10046 Events and Tkprof commands

Using the 10046 event is another way to view the execution plan of the target SQL in the Oralce database. The difference between this approach and the use of the explain Plan command, the Dbms_xplan package and the autotrace switch is that the resulting execution plan explicitly shows the logical reads, physical reads, and time spent by each of the execution steps in the target SQL actual execution plan. This fine-grained detail is particularly useful in diagnosing the performance problems of complex SQL, and is not available in the other three methods (in fact, with GATHER_PLAN_STATISTISC hint mates Dbms_ Xplan package can also achieve similar 10046 events with this fine-grained detail display effect)

It is easy to get the execution plan of the target SQL with the 10046 event, just follow the following three steps in turn:

Activates the 10046 event in the current session first

Then execute the target SQL in this session

Finally close the 10046 event in this session

When the above steps are performed, Oracle will write the execution plan and the detail resource consumption of the target SQL to the trace file for this session, and view the trace file to know the execution plan and resource consumption details of the target SQL. Oracle generates a trace file under the target represented by the parameter user_dump_dest, named "Instance name _ora_ the SPID.TRC of the current session."

You can usually activate the 10046 event in the current session using the following two ways:

In the current seesion, perform the alter session set events ' 10046 Trace name context forever,level 12 '

In the current session, execute Oradebug event 10046 Trace name Context Forever,level 12.

It is important to note that the original trace file produced by the 10046 event is what we are accustomed to call a bare trace file, and that the Oracle record in the bare trace file does not look so intuitive at first glance, and is not so easy to understand. In order for the above-mentioned bare trace files to be presented in a more intuitive and easy-to-understand manner, Oracle provides the TKPROF command, a command-oralce, that can be used to translate the trace file. As you can see from the following example, the translation is more intuitive and easier to understand.

We still use the target SQL statement "Select Empno,ename,dname from emp a,dept b where a.deptno=b.deptno;" For example, explain the use of the 10046 event and the Thprof command.

Oradebug Setmypid indicates that you are ready to use the Oradebug command for the current session:

Sql> Oradebug Setmypid;

Statement processed.

Here we use the second method described earlier to activate the 10046 event in the current session:

Sql> oradebug Event 10046 Trace name context Forever,level 12;

Statement processed.

After activating the 10046 event in the current session, execute the target sql:

Sql> Select Empno,ename,dname from scott.emp a,scott.dept b where a.deptno=b.deptno;

EMPNO ename dname

---------- ---------- --------------

7782 CLARK ACCOUNTING

7839 KING ACCOUNTING

7934 MILLER ACCOUNTING

7566 JONES

7902 FORD

7876 ADAMS

Rows selected.

With the Oradebug tracefile_name command, you can see at a glance the path and name of the trace file that corresponds to the current session activation 10046 event:

Sql> Oradebug Tracefile_name;

/u01/app/oracle/diag/rdbms/mecbs/mecbs1/trace/mecbs1_ora_10292.trc

The original trace file does not look too intuitive at first glance, nor is it too easy to understand. Now we use the TKPROF command to translate:

[Email protected] ~]$ TKPROF/U01/APP/ORACLE/DIAG/RDBMS/MECBS/MECBS1/TRACE/MECBS1_ORA_10292.TRC

Output =/home/oracle/mecbs_10046.trc

[email protected] ~]$ cat MECBS_10046.TRC

Tkprof:release 11.2.0.4.0-development on Sun Dec 14 16:00:53 2014

Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.

Trace File:/u01/app/oracle/diag/rdbms/mecbs/mecbs1/trace/mecbs1_ora_10292.trc

Sort Options:default

********************************************************************************

Count = number of times OCI procedure was executed

CPU = CPU time in seconds executing

elapsed = elapsed time in seconds executing

Disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

Current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

SQL ID:0RQWS2B4FVRR6 Plan hash:844388907

Select Empno,ename,dname

From

Scott.emp a,scott.dept b where a.deptno=b.deptno

Call count CPU Elapsed disk query current rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse 1 0.02 0.07 0 260 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 10 0 14

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Total 4 0.02 0.07 0 270 0 14

Misses in library cache during parse:1

Optimizer mode:all_rows

Parsing user Id:sys

Number of plan statistics captured:1

Rows (1st) rows (avg) rows (max) Row Source operation

---------- ---------- ----------  ---------------------------------------------------

MERGE JOIN (cr=10 pr=0 pw=0 time=313 US cost=6 size=364 card=14)

4 4 4 TABLE ACCESS by INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=256 US cost=2 size=52 card=4)

4 4 4 INDEX full SCAN pk_dept (cr=2 pr=0 pw=0 time=143 US cost=1 size=0 card=4) (Object ID 87107)

+ SORT JOIN (cr=6 pr=0 pw=0 time=184 US cost=4 size=182 card=14)

TABLE ACCESS full EMP (cr=6 pr=0 pw=0 time=125 US cost=3 size=182 card=14)

10046 Events and Tkprof commands