13.6 SQL Trace

---

SQL Trace is a utility that comes bundled with the Oracle database server. This utility is used to generate SQL Trace information while the database is being used for a specific session. The output generated in the form of trace files from the process contains the execution plans used by the SQL queries. This information generated in the trace files is used to help improve the query performance.

SQL Trace is generated at the system level by enabling an init<SID>.ora parameter SQL_TRACE = TRUE or at the session level with the following statement:

SQL>ALTER SESSION SET SQL_TRACE = TRUE;

Either method of SQL tracing generates output files for every session containing trace information in the directory specified by the USER_DUMP_DEST parameter.

Setting this parameter before executing SQL queries at the session level will generate trace files for only queries executed by this session. The files are generated in the same user dump destination.

The trace files generated are basic raw files and not easily readable. However, Oracle provides a utility called TKPROF, which can be utilized 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 tkprof <trace filename> <output file name>, explain=<schema name>/ <password>@SID table=<schema name>.<temp table name> <additional parameters>

Note 

A sample out can be found in the next section.

---