tkprof is one of the most useful utilities available to Admins for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.
Why, When tkprof?
Use tkprof and session tracing when the database or a particular session is having performance problems. tkprof will generally be used infrequently, when researching a very particular performance issue. A user may complain that the response time for a session is abysmal compared to the prior week. Session tracing and tkprof can be used to see exactly what is happening on the database, enabling the DBA to take corrective action.
The utility can also be used to view SQL that is being executed for an application. In some situations, this will be the only mechanism a DBA will have to view SQL. These situations include the execution of encrypted PL/SQL code on the database or submission of SQL statements from third party applications.
Identifying the Siebel Server Task
To help diagnose problems that occur with Siebel server tasks it may be necessary to get an RDBMS trace. To perform such a trace on a server task on Oracle RDBMS, carry out the following:
1.First of all identify the process id of the Siebel server task. This can be done from:
Server Administration > Server Tasks (in version 5.x, 6.x)
Server Administration > Tasks (in version 7.x)
Administration – Server Management > Tasks (in version 8.0).
There is a PID column that identifies the process id for the server task. Make a note of the PID identified.
2.Log on to the Siebel database with SQL*Plus as a user with permissions to query the V$ tables for example DBA or SYSTEM and run the following SQL statement:
SELECT SERIAL#, SID
FROM V$SESSION
WHERE PROCESS LIKE 'PID:%'
Where PID is the PID identified in step 1. Make a note of the SID and SERIAL# pairs identified as there may be more than one Oracle session per process.
3.Then start SQL tracing for each SID and SERIAL# pair identified in step 2:
exec sys.dbms_system.set_sql_trace_in_session (SID, SERIAL#, true);
Tracing for this session is now on and Oracle writes to the user dump destination specified by the USER_DUMP_DEST initialization parameter. To turn the trace off:
exec sys.dbms_system.set_sql_trace_in_session (SID, SERIAL#, false);
4.Finally format the trace file with the TKPROF utility. TKPROF accepts as input a trace file produced by the SQL trace facility, and it produces a formatted output file. Run TKPROF on each individual trace file. For example:
TKPROF ora53269.trc ora53269.prf SORT = (EXECPU, FCHCPU)
The SORT value causes TKPROF to sort the SQL statements in order of the sum of the CPU time spent executing and the CPU time spent fetching rows before writing them to the output file.