Wednesday, November 25, 2009

Oracle 10132 trace

Almost all of the DBA's use the 10046 trace and many use 10053 trace, one of traces not often used is the 10132 trace.This is generally used to generate the execution plan for the hard parses.Which can be enabled at the system level to get/ baseline the plans for the hard parsed statements after the event is set.We can also use it for session level as a shorter version of 10053 trace.

And most importantly the trace file is well formatted and easily readable even without using any utility like tkprof.


The event 10132 can be enabled and disabled in the following ways:

Enable and disable the event for the current session.

ALTER SESSION SET events '10132 trace name context forever'
ALTER SESSION SET events '10132 trace name context off'

Enable and disable the event for the whole database.

Note: this setting does not take effect immediately but only for sessions created after the modification.

ALTER SYSTEM SET events '10132 trace name context forever'
ALTER SYSTEM SET events '10132 trace name context off'


Sample output:

*** SERVICE NAME:(SYS$USERS) 2009-11-25 10:23:15.898
*** SESSION ID:(2160.19196) 2009-11-25 10:23:15.898


Current SQL statement for this session:
SELECT so_status_name
FROM job_queue jq,
so_job_queue sjq
WHERE jq.module = :sModule
AND jq.arg_3 = :sUser
AND jq.arg_4 = :sFile
AND jq.arg_5 = :sDate
AND jq.jobid = sjq.so_jobid
Plan Table
--------
-------------------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | | | 6 | | | | | | |
| NESTED LOOPS | | 1 | 48 | 6 | | | | | | |
| TABLE ACCESS FULL | JOB_QUEUE | 1 | 33 | 5 | | | | | | |
| TABLE ACCESS BY INDEX ROWID | SO_JOB_QUEUE | 1 | 15 | 1 | | | | | | |
| INDEX UNIQUE SCAN | SO_JOB_QUEUE_IDX | 1 | | | | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------