See several common methods of Oracle execution plan-Series 3

Cont.: http://blog.csdn.net/bisal/article/details/39225373

4.10046 Events

The execution plan for the target SQL can also be viewed through the 10046 event. Events like 10046, which are not available in Oracle's official documentation, are typically used for debugging purposes, so they can often be used to find more detailed information about the problem.

The difference between the 10046 event and the previous explain plan, the Dbms_Xplan package, and the autotrace switch is that the TRC file generated by the 10046 event explicitly shows the logical reads consumed by each step in the target SQL actual execution plan, The physical read and spend time, the execution plan cost analysis, and then can see why Oracle chose such an execution plan for SQL, rather than the execution plan, is the actual execution plan, from the 10046 event execution process can be seen:

(a) Activates the 10046 event in the current session .

(b) Execute SQL in this session.

(c) Close The 10046 event for this session.

The actual execution of the SQL, corresponding execution plan can be found in the TRC file. This TRC file records the execution plan and resource consumption of SQL, named format "instance name _ora_ the SPID of the current session. TRC".

(1). Activate 10046 event :

There are two ways of doing this:

(a) Alter session set events ' 10046trace name Context forever, level ";

(b) Oradebug setmypid/oradebug setospid SPID;

Oradebug Event 10046 Trace name Context forever, level 12;

(2). view 10046 generated TRC file name and path methods ：

(a)show parameter user_dump_dest display the path to TRC file storage-> find the TRC file corresponding to the current session (if currently a single user, it is the most recently generated file) .

Experiment:

(b) using the Ordebug (b) above to produce TRC files, you can use Oradebug tracefile_name to obtain the TRC file name and path.

Oradebug has a lot to say, first of all, this is the Sqlplus-specific command, which executes SQL statements in Plsql Developer that will prompt for invalid, such as:

Next it is the command of the SYSDBA role, using non-SYSDBA execution will prompt ora-01031 Insufficient permissions , for example:

After you sign in with SYSDBA, you can view Help for Oradebug:

Although it may seem troublesome to use SYSDBA to log in when Oradebug is used, the biggest benefit is that alter session can only be used for the current session or system level, compared to the first alter session method , alter session or alter system settings, if you set a trace for non-this session, You can use Oradebug now, (it is said that Dbms_system, Dbms_monitor and DBMS _support can also achieve the same requirements, but have not tried).

Before you set up the 10046 event with Oradebug, you need to set up the session to be traced first:

(a) Follow this session, using:oradebug setmypid .

(b) Tracking non-current session, using:oradebug setospid SPID( from V$process).

ways to find the SPID :

(a) select * from V$session A WHERE Audsid = Userenv (' SessionID '); returns sid value.

(b) Select S.username,
S.osuser,
S.sid,
S.PADDR,
S.process,
P.spid os_process_id,
P.pid oracle_process_id
From V$session S, v$process p
where s.paddr = P.addr
and s.username = Upper (' Session user name to be traced ')
and S.sid = (a) The returned SID;

For example:

which

The SPID in the v $ process refers to the operating system's process , which is the PID of the operating system.
PID in v$session , serial# is the PID assigned by Oracle.

at this point, if you need to trace the SQL executed by this session 24061, You can use Oradebug setospid 24061, then oradebug event 10046 Trace Name context forever, level; The 10046 event is opened.

You can then view the trace file name and path through Oradebug tracefile_name, for example:

Look at the contents of the PETEST_ORA_22756.TRC:

In addition to basic information such as machines, instances, processes, and so on, what is actually written:

Received oradebug command ' tracefile_name ' from process Unix process pid:22235, Image:

Indicates that the PID is the 22235 oradebug command that receives the operating system process from UNIX, the parameter tracefile_name.

Obviously 22235 this process is the client that executes Oradebug after Sqlplus login, for example:

(3 ). method of closing 10046 :

(a) Alter session set events ' 10046 Trace name context off ';

(b) Oradebug event10046 trace name context off;

There are two ways to open 10046 events, respectively.

(4). Again, the difference between the Oradebug and alter session open 10046 event produces the trace file :

(a) When the 10046 event is opened with Alter session, the trace file is not generated if SQL is not executed.

(b) Use Oradebug event 10046 trace name Context forever, Level 12, open 10046 event, the trace file has been generated , in addition to the basic information, mainly a line:

WAIT #0: nam= ' sql*net message to client ' Ela= 2 driver id=1650815232 #bytes =1 p3=0 obj#=-1 tim=1379395297285576

When using Oradebug event 10046 trace name context off; 10046 event is closed, one line is written:

WAIT #0: nam= ' sql*net message from client ' ela= 30946429 driver id=1650815232 #bytes =1 p3=0 obj#=-1 tim=1379395328232564

(5). Trace Trace File :

It says two common ways to open and close 10046, and here's a quick look at what the trace file contains, why 10046 of this event is for debugging, and I'm not proficient in everything right now, probably talking about understanding.

Execute the command first to open the 10046 event, execute the SQL, and close the 10046 event, for example:

To view the resulting trace file:

Two "============" before the content, is the execution of SQL generated information, before and after the content, should be open and close the 10046 event information.

In the red box is the execution plan for this SQL, as seen from the file, there are 5 steps, in parentheses, the related consumption :

Execution Plan First step: MERGE JOIN, logical Read (CR) is 11, physical read (PR) is 0, time is 378 microseconds.

Execution Plan Second step: TABLE ACCESS by INDEX ROWID TEST1, logical Read (CR) is 4, physical read (PR) is 0, time is 139 microseconds.

Execution Plan step three: INDEX full SCAN sys_c0016790, logical read (CR) is 2, physical read (PR) is 0, time is 86 microseconds.

Execution Plan Fourth step: SORT JOIN, logical Read (CR) is 7, physical read (PR) is 0, time is 266 microseconds.

Execution Plan Fifth step: TABLE ACCESS full TEST2, logical read (CR) is 7, physical read (PR) is 0, time is 128 microseconds.

here TRC file is a bare trace file, content can be seen, but not so intuitive, you can use the TKPROF command to translate TRC files. For example:

To view the generated Tkprof file:

From here, you can see more clearly the number of rows returned by each step of the execution plan, as well as the sequential relationship, reading the formula according to @dbsnake's execution plan: "Start from the beginning to the right, until you see the rightmost side of the place; From the top down, for the side-by-side part, the top of the first execution . Under Simple analysis:

(a) INDEX full...

The sys_c0016790 primary key index is used to index a fast full scan, where sys_c0016790 is the TEST1 primary key, or T1id column.

(b) TABLE ACCESS full ...

Full table Scan TEST2 table.

(c) TABLE ACCESS by ...

The corresponding data item is queried based on the ROWID returned by the TEST1 primary key index. Produces a result set of 1.

(d) SORT JOIN

Sort by TEST2 's t2id column. Produces a result set of 2.

(e) MERGEJOIN

Traverse result set 1, that is, the 1th record of the result set 1 is taken out, and the result set 2 in accordance withthe conditions of t1.t 1id=t2.t2id to determine whether there is a matching record, and then take out the 2nd record of the result set 1 continue to judge, until the completion of the result set 1.

here is the "Sort merge connection ", thecorresponding keyword in the execution plan is "merge join" and "sort join", normally, the second step of two tables should be sort join, but here the table TEST1 is the table ACCESS by INDEX ROWID TEST1, I think the reason should be:

(a) the TEST2 table is scanned using the index full scan sys_c0016790, which is a fast, fully scanned, T1ID index block of primary keys .

(b) Indexes are ordered, so the result of index full SCAN sys_c0016790 is also the equivalent of sorting .

(c) since it was already the result of a sort, the corresponding records were found in order according to the ROWID of the ordered index, TABLE ACCESS by INDEX ROWID TEST1, So don't show the sort join again .

Not to be continued ...

? To be Continued ...

See several common methods of Oracle execution plan-Series 3