The various case of Oracle database 10046 to facilitate trace information collection

Whenever we encounter SQL-related performance, we always need to collect 10046 to view and diagnose the problem.
Since 10046 the true response of the SQL statement executes when the real information, parsing, execution, acquisition time consumption, row source operation the specific situation.
Specific waiting events, each time the specific time consumption and so on. Hopefully, one of the following case will help you.

event:10046 "Enable SQL statement tracing (including binds/waits)" (Doc ID 21154.1)
Interpreting Raw sql_trace output (Doc ID 39817.1)
General sql_trace/10046 TRACE Gathering examples (Doc ID 1274511.1)

==================
SQL performance is commonly used:
All versions
10046 on Session/system
To start tracing:
Alter Session/system (cautiously) Set events ' 10046 Trace name Context forever, Level 12 ';
/* Execute your selects to be traced * *

To stop tracing
Alter Session/system (cautiously) Set events ' 10046 Trace name context off ';

11g or above
1. event++ specifies sql_id at the system level, valid for new sessions and current sessions, invalid for other existing sessions
Sql> alter system set Events ' Sql_trace [Sql:5qcyrymp65fak] level=12 ';

Note: The current event is valid for the current session and the newly created session, and is not valid for other sessions that already exist.
Close Event + +:
Sql> alter system set Events ' Sql_trace [Sql:5qcyrymp65fak] off ';

2. Event + + Specifies the sql_id of a process
sql> oradebug setospid <SPID> <<<<< Specify a detected session SPID <<<<<<<<<< <select spid from v$process, v$session where audsid=userenv (' SESSIONID ') and paddr=addr;
Sql> Oradebug Unlimit
Sql> Oradebug Tracefile_name
Sql> oradebug Event sql_trace [Sql:5qcyrymp65fak] level=12

Close Event + +:
Sql> oradebug Event sql_trace [Sql:5qcyrymp65fak] Off

3. Do not know sql_id manually Execute SQL collection 10046
Sql>connect Username/password
Sql>alter Session Set Timed_statistics = true;
Sql>alter session Set Statistics_level=all;
Sql>alter session Set max_dump_file_size = unlimited;
Sql> Select value from V$diag_info where Name= ' Default Trace File '; <<<< work in above 11g
sql> variable A1 <the type of account_type_id>; <<<<< please perform type
sql> EXEC:A1: = 123123 or ' abded '; <<<<<<< set numeric or string
Sql>alter session Set Events ' 10046 Trace name Context forever, Level 12 ';
Sql>update/*+ restrict_all_ref_cons * * "Lbi_ods". T_o_customer_account "SET
"account_type_id" =: A1
WHERE
"Account_no" = 1234565; <<<<<<<<<<<< Perform SQL recurrence issues
Sql>alter session Set Events ' 10046 Trace name context off ';

==================
Use trigger setting 10046
Use a Logon triggerto start tracing:
Create or Replace Trigger USER_LOGON_TRG
After logon on database
Begin
If USER = ' xxxx ' then
Execute immediate
' Alter session Set Events ' 10046 Trace name Context forever, Level 8 ';
End If;
End
/

/* Login A new session as User ' XXXX ' and execute your selects to be traced * *

To stop Tracing:via LogOff Trigger (needs to is created before off)
Create or Replace Trigger USER_LOGOFF_TRG
Before logoff on database
Begin
If USER = ' xxxx ' then
Execute immediate
' Alter session Set Events ' 10046 trace name ';
End If;
End
/

==================
Mmon's 10046
1. Would you please turn on the trace of auto purge?

Begin
Dbms_monitor.serv_mod_act_trace_enable
(service_name=> ' Sys$background ',
Module_name=> ' Mmon_slave ',
Action_name=> ' auto-purge Slave Action ');
End
/

2. Please wait at least one day, please check out tomorrow when Auto Purge is executed and produces m00x trace file contains 10046

3. Turn off trace for Auto purge
Begin
Dbms_monitor.serv_mod_act_trace_disable
(service_name=> ' Sys$background ',
Module_name=> ' Mmon_slave ',
Action_name=> ' auto-purge Slave Action ');
End
/

==================
Data Pump 10046
1. Enable 10046 trace for DM/DW process

Alter system set Events ' Sql_trace{process:pname=dw | pname=dm} level=12 ';

2. Please reproduce the issue and then add "trace=480300" in data Pump importing command

3. Please upload data pump importing log and the generated DM/DW process trace

To disable the tracing by issuing:

Alter system set Events ' Sql_trace {process:pname = DW | pname = DM} off ';

==================
Other ways to set 10046
1. Dbms_supportto Start Tracing:
exec sys.dbms_support.start_trace;
/* Execute your selects to be traced * *

To stop tracing:
exec sys.dbms_support.stop_trace;
Tracing from Another sessionthe examples below demonstrate how to trace sessions with sid=18 and serial# =226 obtained from V$session.

2. Using "Dbms_system". Set_bool_param_in_session "To start tracing:
EXEC sys.dbms_system. Set_bool_param_in_session (%, Sql_trace, ' the ", TRUE);
/* Execute your selects to be traced * *
To stop tracing:
EXEC sys.dbms_system. Set_bool_param_in_session (a), the Sql_trace, ' the FALSE ';

3. Using "Dbms_system.set_ev" to start tracing:
EXEC Dbms_system.set_ev (18, 226, 10046, 12, ");

       to stop tracing:
       exec Dbms_ System.set_ev (18, 226, 10046, 0, ");
    4. Using "Dbms_system.set_sql_trace_in_session" to start tracing:
       exec Dbms_ System.set_sql_trace_in_session (18,226,true);
      /* Execute your selects to be traced */
     & nbsp To stop tracing:
       exec dbms_system.set_sql_trace_in_session (18,226,false);
    5. Using "Sys.dbms_monitor" to start tracing:
       exec Sys.dbms_monitor.session_ Trace_enable (session_id=>18,serial_num=>226, Waits=>true, binds=>true);
      /* Execute your selects to be traced */

To stop tracing:
EXEC sys.dbms_monitor.session_trace_disable (session_id=>18,serial_num=>226);

6. Using Oradebug (as SYS) to start tracing:
Oradebug setospid xxxx
Oradebug event 10046 Trace name forever, level 12;
/* In the session being traced execute the selects * *

To stop tracing:
Oradebug Event 10046 Trace name context off;