Shulou(Shulou.com)06/01 Report--

Using the 10046 event is another way to view the execution plan of the target sql in the oralce database. This approach differs from using the explain plan command, dbms_ xplan package, and autotrace switch in that the resulting execution plan clearly shows the logical read, physical read, and time spent on each execution step in the actual execution plan of the target sql. This fine-grained detail display is particularly useful when diagnosing complex sql performance problems, and it cannot be provided by the other three methods (in fact, using gather_plan_statistisc hint with dbms_xplan packages can also achieve a fine-grained detail display effect similar to 10046 events)

It is easy to get the execution plan of the target sql with the 10046 event, simply by performing the following three steps in turn:

First activate the 10046 event in the current session

Then execute the target sql in this session

Finally, close the 10046 event in this session

After performing the above steps, oracle will write the execution plan and detailed resource consumption of the target sql into the trace file corresponding to the session. By viewing this trace file, you can 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 in the format "instance name _ ora_ spid.trc of the current session".

There are usually two ways to activate the 10046 event in the current session:

Execute alter session set events' 10046 trace name context forever,level 12' in the current seesion

Execute oradebug event 10046 trace name context forever,level 12 in the current session.

It should be noted that the original trace file generated by the 10046 event is commonly called a naked trace file, and the contents of the oracle record in the naked trace file do not look so intuitive at first glance, nor are they easy to read. In order to enable the above naked trace files to be displayed in a more intuitive and easy-to-understand way, oracle provides the tkprof command, which comes with imperative oralce, which can be used to translate the above trace files. As can be seen from the subsequent examples, the translated version is more intuitive and easier to understand.

Let's use the target sql statement "select empno,ename,dname from emp a where. Deptnob.deptno;" as an example to illustrate the use of the 10046 event and the thprof command.

Oradebug setmypid indicates that you are ready to use the oradebug command on 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 the 10046 event is activated 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 RESEARCH

7902 FORD RESEARCH

7876 ADAMS RESEARCH

14 rows selected.

With the oradebug tracefile_name command, you can clearly see the path and name of the trace file corresponding 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 very intuitive at first glance, nor is it too easy to read and understand. Now let's use the tkprof command to translate:

[oracle@node1 ~] $tkprof / u01/app/oracle/diag/rdbms/mecbs/MECBS1/trace/MECBS1_ora_10292.trc

Output = / home/oracle/mecbs_10046.trc

Oracle@node1 ~] $cat mecbs_10046.trc

TKPROF: Release 11.2.0.4.0-Development on Sun Dec 14 16:00:53 2014

Copyright (c) 1982, 2011, 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 00 00

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

14 14 14 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)

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

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.