Overview : You can generate a log of Oracle statements being executed on the server. This trace file is very useful in diagnosing performance problems. Oracle's utility, tkprof, can be used to sort commands. You can also request the execution plan of the statements.
Procedure
1. Enable timed statistics
Modify the initxxx.ora file for your database instance to include timed_statistics with the following parameter. You must shutdown and restart the database for this parameter to take effect.
timed_statistics=true
2. Trace all sessions
To trace all sessions, set SQL_TRACE in the initxxx.ora file to TRUE. If you are trying to diagnose a specific error then open the .trc file with a text editor and search for err='Oracle error number without the leading zeros'
3. Trace a specific session
MAXIMO 4.x
Start the MAXIMO application you wish to trace.
Determine the process id of the workstation you want to trace.
select sid, serial# from v$session where username='maximoschema' and machine='hostname';
Start the trace:
exec dbms_system.set_sql_trace_in_session (sid, serial#, TRUE);
note: the procedure can be run as sys. You may need to grant execute to system on dbms_system.
Run the MAXIMO process you wish to trace.
Stop the trace:
exec dbms_system.set_sql_trace_in_session (sid, serial#, FALSE);
MAXIMO 5.x
You cannot trace a specific session in MAXIMO 5.x.
4. Generate the results using tkprofFormat the results of the trace file with ORACLE's TKPROF utility. You will want to sort either by total CPU time SORT=(PRSCPU,EXECPU,FCHCPU) or by total elapsed time SORT=(PRSELA,EXEELA,FCHELA)
By turning on tracing within MAXIMO and running TKPROF, you will be able to see exactly which SQL statements generated by MAXIMO are the long-running statements. You can run the TKPROF utility using the EXPLAIN PLAN option which will show you the path that the optimizer uses for each particular SQL statement. The goal is to discover why particular SQL statements are taking so long to run.Example:tkprof ora_213999.trc ora_213999.out sys=no sort=execpu explain=maximo/maximo@tns:maxdbPerformance issues to consider (1) optimization mode (cost vs rule) (2) missing indexes (3) database table and index fragmentation (4) other processes running on the database server (5) server resource limitations reached: memory, file throughput, CPU speed. Note: Increasing the size of Oracle trace filesYou may need to increase the size of the trace file.To determine the current setting:SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME = 'max_dump_file_size';To increase it:ALTER SYSTEM SET MAX_DUMP_FILE_SIZE = nnM SCOPE = MEMORY;where size can be nnK nnM or UNLIMITEDand SCOPE can be:MEMORY - For dynamic parameters. Change is immediate but does not persist across shutdown.SPFILE - For static or dynamic parms. Change goes into affect after a shutdown/restart. Static parms can only use this one.BOTH - For dynamic parms. Put into affect immediately and make it permanent.