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

Rows Execution Plan
-------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
300 HASH (GROUP BY)
44846573 HASH JOIN
4484793 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 'ORDERS_I2'
(INDEX (UNIQUE))
44846573 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 'IORDL' (INDEX
(UNIQUE))

Interpreting the trace output generated using tkprof :
Call: Statistics for each cursor's activity are divided in to three areas: •

• Parse: Statistics from parsing the cursor. This includes information for plan generation, etc.

• Execute: Statistics for the exaction phase of a cursor.

• Fetch: Statistics for actually fetching the rows.

• Count: Number of times you have performed a particular activity on this particular cursor.

• CPU: CPU time used by this cursor.

• Elapsed: Elapsed time for this cursor.

• Disk: This indicates the number of blocks read from disk. Generally you want to see blocks

being read from the buffer cache rather than disk.

• Query: This column is incremented if a buffer is read in consistent mode. A consistent mode

buffer is one that has been generated to give a consistent read snapshot for a long running
transaction. The buffer actually contains this status in its header.

• Current: This column is incremented if a buffer found in the buffer cache is new enough

for the current transaction and is in current mode (and it is not a CR buffer). This applies to
buffers that have been read in to the cache as well as buffers that already exist in the cache in
current mode.

• Rows: Rows retrieved by this step.

Such detailed information is not found in a regular explain plan created from the AUTOTRACE feature.

Level 4
Level 1 tracing of event 10046 provides the basic SQL queries, sometimes to recreate the issue or to re-execute the
query. These basic raw queries and their explain plans are not sufficient, especially if the query is using bind variables.
While using bind variables is an excellent practice and should be in the best practices guide of the organization, for
debugging and query tuning purposes, it would be helpful to determine the values used during the query execution
that showed slower query execution. To determine the bind variables and the corresponding bind values, event 10046
at level 4 captures the information.
Similar to level 1, level 4 can be enabled at the system or session level using the following statements:

ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';