Here’s a quick start guide to taking and interpreting 10046 trace, with bind variables.
1. Generate the trace
SQL> ALTER SESSION SET tracefile_identifier='MYTRACE'; SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; SQL> --alternately, SQL> --ALTER SESSION SET sql_trace=true; SQL> var bpno NUMBER SQL> EXEC :bpno := 92000; SQL> DELETE FROM batch_process WHERE process_id = :bpno; SQL> ALTER SESSION SET EVENTS '10046 trace name context off'; SQL> --alternately, SQL> --alter session set sql_trace= false;
2. Find the trace
Go to diagnostic dest on the db server (the default is $ORACLE_BASE/diag/rdbms/
cd to trace directory, look for *MYTRACE*.trc
eg
[oracle@C102 trace]$ ls *MYTRACE* C102_ora_19385_MYTRACE.trc C102_ora_19385_MYTRACE.trm
The .trc file (in this case C102_ora_19385_MYTRACE.trc) will have the 10046 output in it.
3. Interpret the trace
There are 2 ways to interpret it:
1) open the file in SQL developer, which automatically analyses it for you
2) use tkprof on it
[oracle@C102 trace]$ tkprof C102_ora_19385_MYTRACE.trc C102_ora_19385_MYTRACE.out explain=scott/passwd
... SQL ID: 3pfvf0sq4vhx2 Plan Hash: 3613227910 delete from batch_process where process_id = :bpno call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 5 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 5 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 109 (SCOTT) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 DELETE BATCH_PROCESS (cr=3 pr=0 pw=0 time=32 us) 0 0 0 INDEX RANGE SCAN BATCH_PROCESS_IND1 (cr=3 pr=0 pw=0 time=10 us cost=2 size=39 card=1)(object id 92766) Rows Execution Plan ------- --------------------------------------------------- 0 DELETE STATEMENT MODE: ALL_ROWS 0 DELETE OF 'BATCH_PROCESS' 0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'BATCH_PROCESS_IND1' (INDEX) ******************************************************************************** ...