oracle資料庫10046的各種Case，方便trace資訊的收集

每逢與遇到SQL相關效能，我們總是需要收集10046的，來查看和診斷問題。
因為10046真實的反應的SQL語句執行的時候的真實資訊，解析，執行，擷取的時間消耗，row source operation的具體情況。
具體等待事件，每個時間具體的時間消耗等等。希望下面的Case有一種就能協助到您。

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效能常用：
所有版本
    10046 on session/system
    To start tracing:
    Alter session/system(慎用) set events '10046 trace name context forever, level 12';
    /* execute your selects to be traced */

    To stop tracing
    Alter session/system(慎用) set events '10046 trace name context off';

11g以上
    1. event++在system層級指定sql_id,對新起的會話和當前的會話有效， 對其他已經存在的會話無效
         SQL> alter system set events 'sql_trace [sql: 5qcyrymp65fak] level=12';

         注釋：當前事件對當前的session和新建立的session有效，對已經存在的其他session無效。
         關閉 event ++：
         SQL>  alter system set events 'sql_trace [sql: 5qcyrymp65fak] off';

    2. event ++ 指定某個process的sql_id
         SQL> oradebug setospid  <SPID>   <<<<<指定檢測的會話的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

         關閉 event ++：
         SQL>  oradebug event sql_trace [sql: 5qcyrymp65fak] off

    3. 不知道SQL_ID手動執行SQL收集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';   <<<<在11g以上工作
    SQL> variable a1 <the type of ACCOUNT_TYPE_ID>;   <<<<<請執行類型
    SQL> exec :a1 := 123123或'abded';   <<<<<<<請設定數值或字串
    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;                                     <<<<<<<<<<<<執行sql重現問題
    SQL>alter session set events '10046 trace name context off';

==================
使用Trigger設定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 be created before logging 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 context off''';
    end if;
    end;
    /

==================
MMON的10046
    1. 請開啟auto purge的trace？

    begin
      dbms_monitor.serv_mod_act_trace_enable
               (service_name=>'SYS$BACKGROUND',
               module_name=>'MMON_SLAVE',
               action_name=>'Auto-Purge Slave Action');
    end;
    /

    2. 請至少等待一天，請您明天查看時候auto purge被執行，併產生m00x trace檔案包含10046

    3. 關閉auto purge的trace
    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, 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';

==================
其他方式設定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 session 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(18, 226, 'sql_trace', TRUE);
       /* execute your selects to be traced */
       To stop tracing:
       exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', 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 */
       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 context forever, level 12;
       /* In the session being traced execute the selects  */

       To stop tracing:
       oradebug event 10046 trace name context off ;