Use the 10046 event to view the Oracle execution plan

Use the 10046 event to view the Oracle execution plan

You can use the 10046 event to view the execution plan in the Oracle database and get the logical read, physical read, and time consumed by each execution step in the SQL Execution Plan. This fine-grained details display is particularly important when we diagnose the performance problems of complex SQL statements, and this is also the explain plan command, which is not provided by the autotrace command. However, we can also use the gather_plan_sstatistics Hint with the dbms_xplan package to get an execution plan with fine-grained details similar to the 10046 event.

We only need three steps to easily obtain the SQL Execution Plan through the 10046 event.

1. First, activate the 10046 event in the current session.
2. Execute the SQL statement in the current session.
3. Disable the 10046 event in the current session.
After the preceding steps are completed, oracle writes the execution plan and detailed resource consumption of the target SQL statement to the trace file corresponding to this session.
Oracle will generate this trace file in the directory set by the user_dump_dest parameter.
We have two methods to activate the 10046 event in the current session.
1. Execute alter session set events '10046 trace naem context forever, level 12' in the current session'
2. Run oradebug event 10046 trace name context forever, level 12' in the current session'
The number after the keyword "level" in the preceding command indicates the level value of the Set 10046 time. This value can be modified.
The commonly used value is 12, indicating that in addition to the execution plan and resource consumption details of the target SQL statement, the generated trace file also contains
The value of the bound variable used by the SQL statement and the waiting event experienced by the session, except for the level value, other parts are fixed syntax,
It cannot be modified.
I recommend 2nd methods, because you can run the oradebug tracefile_name command after activating the 10046 event to obtain the current
The specific path name of the trace file corresponding to the session.
We can disable the 10046 event in the current session in two ways:
1. Execute alter session set events '10046 trace name context off' in the current session'
2. Execute oradebug event 10046 trace naem context off in the current session

Let's use an example to illustrate

1. Activate the 10046 event first. We use the oradebug command to activate the event.
SQL> oradebug setmypid
SQL> oradebug event 10046 trace name context forver, level 12
2. After the 10046 event is activated in the current session, we execute an SQL statement.
SQL> SELECT * FROM hr. test;
3. Run the oradebug tracefile_name command to view the path and name of the trace file corresponding to the current session.
SQL> oradebug tracefile_name
/App/oracle/diag/rdbms/pxboracle/trace/pxboracle_ora_18565.trc
4. Close the 10046 event in the current session:
SQL> oradebug event 10046 trace name context off

The following content is the content in the trace file.

==================================
Parsing in cursor #47006443926880 len = 27 dep = 0 uid = 0 oct = 3 lid = 0 tim = 2879240721898379 hv = 2413634929 ad = '9e709620 'sqlid = '4wn49u27xu9bj'
Select * from hr. test
END OF STMT
PARSE #47006443926880: c = 378943, e = 2562085, p = 13, cr = 721, cu = 0, mis = 1, r = 0, dep = 0, og = 1, plh = 121040406, tim = 2879240721898378
EXEC #47006443926880: c = 0, e = 81, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, plh = 121040406, tim = 2879240721898787
WAIT #47006443926880: nam = 'SQL * Net message to client' ela = 3 driver id = 1650815232 # bytes = 1 p3 = 0 obj #=-1 tim = 2879240721898965
WAIT #47006443926880: nam = 'disk file operations I/o' ela = 175 FileOperation = 2 fileno = 5 filetype = 2 obj # = 18841 tim = 2879240721899603
WAIT #47006443926880: nam = 'db file sequential read 'ela = 15398 file # = 5 block # = 138826 blocks = 1 obj # = 18841 tim = 2879240721915091
WAIT #47006443926880: nam = 'db file sequential read 'ela = 10745 file # = 5 block # = 138827 blocks = 1 obj # = 18841 tim = 2879240721935221
FETCH #47006443926880: c = 1000, e = 36278, p = 2, cr = 2, cu = 0, mis = 0, r = 1, dep = 0, og = 1, plh = 121040406, tim = 2879240721935413
WAIT #47006443926880: nam = 'SQL * Net message from client' ela = 6148 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 18841 tim = 2879240721941743
WAIT #47006443926880: nam = 'SQL * Net message to client' ela = 1 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 18841 tim = 2879240721941899
FETCH #47006443926880: c = 999, e = 178, p = 0, cr = 1, cu = 0, mis = 0, r = 15, dep = 0, og = 1, plh = 121040406, tim = 2879240721942058
WAIT #47006443926880: nam = 'SQL * Net message from client' ela = 407527 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 18841 tim = 2879240722349676
WAIT #47006443926880: nam = 'SQL * Net message to client' ela = 1 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 18841 tim = 2879240722349821
FETCH #47006443926880: c = 1000, e = 201, p = 0, cr = 1, cu = 0, mis = 0, r = 7, dep = 0, og = 1, plh = 121040406, tim = 2879240722350005
STAT #47006443926880 id = 1 cnt = 23 pid = 0 pos = 1 obj = 18841 op = 'table access full lbbnf (cr = 4 pr = 2 pw = 0 time = 36197 us cost = 3 size = 3036 card = 23)'
WAIT #47006443926880: nam = 'SQL * Net message from client' ela = 21195539 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 18841 tim = 2879240743545997
CLOSE #47006443926880: c = 0, e = 14, dep = 0, type = 0, tim = 2879240743546441

* ** 2061-03-28 21:05:43. 546
Processing Oradebug command 'tracefile _ name'

* ** 2061-03-28 21:05:43. 546
Oradebug command 'tracefile _ name' console output:
/App/oracle/diag/rdbms/pxboracle/trace/pxboracle_ora_18565.trc
WAIT #0: nam = 'SQL * Net message to client' ela = 2 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 18841 tim = 2879240743547147
WAIT #0: nam = 'SQL * Net message from client' ela = 18525364 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 18841 tim = 2879240762072687

* ** 21:06:02. 072
Processing Oradebug command 'event 10046 trace name context off'

* ** 21:06:02. 073
Oradebug command 'event 10046 trace name context off' console output: <none>