Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 178

SQL Trace is generated at the system level by setting the parameter SQL_TRACE = TRUE or at the session level with
the following statement:
SQL>ALTER SESSION SET SQL_TRACE=TRUE;
Either method can be used; however, setting the parameter SQL_TRACE=TRUE will generate trace for all sessions.
This can cause performance issues and should only be enabled when there are large numbers of statements having
poor performance or when the bad statement cannot be identified. Enabling this parameter at the system level will
also generate a significant amount of trace files in the user dump destination directory specified by the USER_DUMP_DEST
parameter. The DBA should ensure that there is sufficient space in the user dump destination location before enabling
this parameter.
Setting this parameter before executing SQL queries at the session level will generate trace for only queries
executed in the session.
The trace files generated are basic raw files and are not easily readable. Oracle provides a utility called TKPROF ,
which can be used to format the trace files into a more readable form using the following command:
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor