Details Of TRACE and TKPROF Output

SQL TRACE has multiple sections including SQL Statements, Statistics, information and EXPLAIN PLAN.

1. SQL Statements: The first part of a TKPROF statement is the SQL  Statement . This statement will be exactly the same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output.

2. Statistics:  It has all the Statistics for this SQL Statements. It has eight columns 

• call Statistics for each cursor's activity are divided in to 3 areas: Parse,Execute and Fetch. A total is also calculated.

      Parse: statistics from parsing the cursor. This includes information for plan generation etc.

Execute: statistics for the execution phase of a cursor

Fetch: statistics for actually fetching the rows

• Count number of times each individual activity has been performed on this particular CALL.
• CPU time used by this CALL.
• ELAPSED time for this CALL(includes the CPU time).
• DISK this indicates the number of blocks read from disk. Generally it would be preferable for blocks to be read from the buffer cache rather than disk.
• QUERY the total number of data buffers retrieved from memory for this type of call. SELECT statements usually retrieve buffers in this mode. This is the number of consistent gets.
• CURRENT the total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE usually access buffers in this mode, although SELECT statements may use a small number of buffers in this mode also. This is the number of db block gets.
• ROWS the total number of rows processed by this statement. The rows processed for SELECT statements will appear in the row of Fetch statistics. Inserts, updates, and deletes will appear in the Execute row.

3. Information: It contains information about the number of library cache misses from parse and execute calls. If the number of misses is high, there may be a problem with the size of the shared pool. It also contains the username of  the last user to parse this statement.   

4. EXPLAIN PLAN : This most useful section of  the TKPROF. The first column of this section is the number of rows processed by each line of the execution plan. We can identify how slow a statement is. If the total number of rows in the fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN.