SQL Tuning Basic Overview 03-execution plan with Sql_trace and 10046 event tracking

1. Using Sql_trace to track execution plans

1.1 Current session Tracking:

Alter Set = true;//start Sql_trace Alter Set  jytrace;//Set the identifier of the trace file alterset= false; End Sql_trace

1.2 Other session tracking: (based on SID of other session, serial#, most commonly used)

exec dbms_system.set_sql_trace_in_session (sid,serial#,true);//Start exec dbms_system.set_sql_trace_in_session (sid,serial#,false); End

1.3 Instance Level: (rarely used, will cause additional overhead for the system)

Alter Set = // Start Alter Set = // End

The instance level can also be added Sql_trace = True in the Pfile/spfile parameter file

2. Use 10046 internal event tracking for more detailed information

2.1 About 10046 events of four levels, refer to the total blog, the difference is as follows:

The 10046 event is an internal event provided by Oracle and is an enhancement to the Sql_trace. 10046 event can be set to the following four levels:1 -enable standard sql_trace functionality, equivalent to Sql_trace4 -  Level 1 +Bound value (bindValues)8 -  Level 1 +waiting for event tracking A -  Level 1 +  Level 4 +  Level 8similar sql_trace,10046 events can be set at the global settings or at the session level. 

2.2 Current session Tracking:

Alter Set ' 10046 Trace name Context forever, level ';

Alter Set ' 10046 Trace name context off ';

2.3 Other session traces (most commonly used):

exec dbms_monitor.session_trace_enable (sid,serial#,waits=true,binds= true); Begin

exec dbms_monitor.session_trace_disable (sid,serial#);  End

2.4 Instance level (rarely used):

Pfile/spfile parameter file Added event = "10046 Trace name context forever,level 12"

3. Read the generated trace file

3.1 Locate the trace file (below this SQL source Dave blog)

Oracle 10g version: The default path for generated trace files is $oracle_base/admin/sid/udump.

Oracle 11g version: The default path for generated trace files is $oracle_base/diag/rdbms/jy/jy/trace.

SELECTD.value|| '/'         || LOWER(RTRIM(I.instance, CHR (0)))         || '_ora_'         ||P.spid|| '. TRC'             as"Trace_file_name" from(SELECTP.spid fromV$mystat m, v$session S, v$process pWHEREm.statistic#= 1  andS.sid=M.sid andP.addr=s.paddr) p, (SELECTt.instance fromV$thread T, V$parameter vWHEREV.name= 'Thread'                    and(V.value= 0 ORt.thread#=To_number (v.value))) I, (SELECTVALUE fromV$parameterWHERENAME= 'user_dump_dest') D;

3.2 Using the Tkprof command to beautify the generated trace file

Here is what I often use, for more detailed instructions on the tkprof command, you can see the tkprof command directly enter.

Tkprof xxx.trc xxx.txt Explain=system/Oracle  sys=n

Reference URL: http://www.eygle.com/archives/2004/10/use_sql_trace_to_diagnose_database.html

http://blog.csdn.net/tianlesoftware/article/details/5857023

Reference book: "Let Oracle run Faster"

SQL Tuning Basic Overview 03-execution plan with Sql_trace and 10046 event tracking