10046 Trace Detailed (1)

10046 Trace Detailed (1)

10046trace helps us to parse the running state of one/More SQL, PL/SQL statements, which include waiting events encountered in the three phases of parse/fetch/execute, physical and logical reading of consumption, CPU time, execution plan, etc., which reveals a The operation of multiple SQL, SQL tuning is a very good tool, but also to help us analyze some of the DDL Maintenance Command Internal working principle, RMAN, DataPump EXPDP/IMPDP and other tools slow problem.

Remember to go to the company interview to mention this same question, look at the SQL execution plan which method, 10046, pl/sql F5 shortcut keys, EXPLAIN plans for ..., Toad can also view, in fact 10053 can also view the SQL execution plan, So what's the difference between 10046 and 10053? 10053 is the most commonly used Oracle Optimizer optimizer tracking trace,10053 can serve as an aid to why we choose an execution plan for the reason of the optimizer, but does not tell us how the execution plan actually works. While 10046 does not explain the work of the optimizer optimizer, it also illustrates the waiting events and CPU resources that are encountered during the SQL parsing parse phase, as well as the metrics for execute execution and the fetch phase, simply 10046 tells us how the SQL plug-in program works, and 10053 tells us why the optimizer chose this execution plan.

Often in order to diagnose SQL tuning class problems, we need to record the waiting that these statements generate during execution and the information for bind variables (bound variables). This information can be obtained by 10046 trace at level 12. The following examples illustrate how to set 10046 events in various scenarios.

Location of trace files
Starting with 11GR1, Oracle introduced a new diagnostic structure to control the path of the trace file and core file with parameter diagnostic_dest. You can obtain the location of the diagnostic_dest by using the following command:

Sql> show Parameterdiagnostic_dest;

11gr1 before, the fruit is the user process, 10046trace files will be generated under User_dump_dest, if the background process, trace files will be generated under Background_dump_dest, the following command can display USER_DUMP_ Dest

Sql> Show Parameteruser_dump_dest

Note: By setting tracefile_identifier, this setting can help us find the generated trace file more easily.

Second, Open trace at the session level
This approach applies to SQL statements that can be run after a new session is created.

Alter session settracefile_identifier= ' 10046 ';
Altersession Set timed_statistics = true;
Altersession set statistics_level=all;--instance level setting this option requires attention and consumes a large amount of CPU
Alter session Set max_dump_file_size = unlimited;
Altersession Set Events ' 10046 Trace name context forever,level12 '; --the commonly used level is 12
Execute SQL that needs to be trace

Select* from dual;
Exit
If you do not exit the current session, you can turn off trace with the following command:

Alter session set the events ' 10046 trace namecontext off ';

Note that some important trace information may be lost if the session is not completely closed and the trace is stopped.
Note: Here we set "Statistics_level" to all, because it is possible that this parameter is not the default value "typical" (such as basic) at the system level. To gather information about performance-related issues, we need to open a level of statistics. We recommend setting this parameter to all at the session level to facilitate the collection of more information, although this is not necessary.

Iii. Tracking a process that has already begun
If you need to track an already existing session, you can connect to it with Oradebug and initiate 10046trace.
First, find the session that needs to be tracked in some way.
For example, in Sql*plus, locate the process ID (SPID) of the OS for the target session:
SELECTP.PIDP.SPID, S.sid
Fromv$process p,v$session S
WHERES.PADDR = p.addr
and S.sid =&session_id
/SPIDis the operating system's process identifier (OSPID)
PIDOracle's process identifier (ORA PID) assumes that the ospid to be tracked is 9834, log on to Sql*plus as SYSDBA, and execute the following command: Oradebug setospid 9834
Oradebug Unlimit
Oradebug Event 10046 Tracename context forever,level12
Note: You can also connect to a session by using the ' setorapid ' command oradebug. In the following example, use thePID(Oracle process Identifier) (instead of a spid), the Oradebug command is changed to:  Connect/as SYSDBA
Oradebug setorapid9834
Oradebug Unlimit
Oradebug Event 10046 Tracename context Forever,level 12 after completion of the trace process, turn off the Oradebug trace: Oradebug event 10046 Trace name Contex T offIv. tracking of instance layers
Note: Setting up the trace at the instance level requires great care because overall performance is affected by the fact that all sessions are tracked, and this setting tracks every session created after this parameter setting "later". A session that already exists is not tracked, and the system layer's 10046 trace applies when we know that the problem session will appear but cannot be identified in advance, in which case you can open the system layer for a short period of time and close it immediately after the problem is reproduced. It then looks for the required information from the generated trace to open the system-level trace under the command:

Alter system set Events ' 10046 Trace name Context forever,level12 '

Close the 10046 trace in all sessions with the following command:

Alter system set Events ' 10046 Trace name context off ';
V. To trace the instance layer through initialization parameter settings
After you set the following parameters and restart the instance, all sessions on the instance will turn on tracing.

event= "10046 Trace name Context forever,level12"

If you want to close, you can turn off tracing by removing this parameter and restarting the instance, or by using the following Altersystem command.

Alter system set Events ' 10046 Trace name context off ';

Vi. setting up tracking via Logontrigger
Sometimes when you need to track a particular user's actions, you can use the logon trigger to turn on tracing

CREATE OR REPLACE TRIGGER sys.set_trace
After LOGON on DATABASE
When (USER like ' &username ')
DECLARE
Lcommand varchar (200);
BEGIN
EXECUTE IMMEDIATE ' altersession set tracefile_identifier= ' From_trigger ';
EXECUTE IMMEDIATE ' altersession set Statistics_level=all ';
EXECUTE IMMEDIATE ' altersession set max_dump_file_size=unlimited ';
EXECUTE IMMEDIATE ' altersession set events ' 10046 Trace name context forever, level12 ';
Endset_trace;
/

To be aware, in order to be able to open the tracking session, the user executing trigger needs to be explicitly granted ' alter session ' permission

Grantalter Session to User_xj

Vii. Collect trace with SQLT
The Sqltxplain Xecute method generates a diagnostic file that contains 10046trace, as the Xecute name shows, SQLT executes the parsed SQL statement and then generates a set of diagnostic files (including 10046trace). See:
note:215187.1 sqlt (sqltxplain)-Toolthat helps to diagnose SQL statements performing poorly (Doc ID215187.1)

The trace file generated in this way is included in the SQLT output package in the format:

sqlt_s12345_10046_execute.trc -- 12345 of which is the SQLT report ID.
Eight, use Dbms_monitor to carry on the tracking
The Dbms_monitor package provides several ways to turn on tracing. For more information, please see:
note:293661.1 tracing enhancements in 10g Usingdbms_monitor
If you are running a Pl/sql stored procedure or package, you can use Pl/sqlprofiler to determine specific information about the time consumed during the Pl/sql run. The following documentation describes how Pl/sqlprofiler is used:
note:243755.1 Implementing and Using the Pl/sql Profiler

Nine, other specific scenarios to open the tracking method
note:21154.1event:10046 "Enable SQL statement tracing (including binds/waits)"
note:1274511.1 General sql_trace/10046 TRACE Gathering examples
note:160124.1 to Set SQL trace on with 10046 Event Trace whichprovides the Bind Variables
note:371678.1 Capture 10046 traces Upon User Login (without using Atrigger)
note:1102801.1 how to get 10046 Trace for Parallel Query
note:242374.1 tracing PX session with a 10046 event or sql_trace
note:258418.1 getting 10046 Trace for Export and Import

10, trace file resolution

note:199081.1 Sql_trace (10046), Tkprof and Explain plan-overviewreference
note:39817.1 Interpreting Raw Sql_trace and Dbms_support. Start_traceoutput
note:224270.1 Trace Analyzer trcanlzr-interpreting Raw SQL traceswith binds and/or Waits

Generated by EVENT 10046
Note:41634.1-tkprof Basic Overview
Note:32951.1-tkprof Interpretation (9i and below)
Note:760786.1-tkprof Interpretation (9i and above)
Note:214106.1-using tkprof to compare actual and predicted rowcounts
note:199083.1 * Master note:sql Query Performance Overview
note:398838.1 * Faq:sql Query performance-frequently askedquestions

Previous: Dbms_sqltune detailed explanation (2)---sql_profile post: 10046 Trace detailed (2)--tkprof

Reference:
Baidu
Interpreting+raw+sql_trace++and+dbms_
Interpreting Raw Sql_trace and Dbms_