sql trace(10046) and explain trace (10053)

10046 event:

level 1 ==> sql_trace
level 4 ==> level 1 + binding var
level 8 ==> level 1 + wait events
level 12 ==> level 4 + level 8


alter session set events '10046 trace name context forever, level 12';
--some statement execute
alter session set events '10046 trace name context off';

monitor session:

sql trace trace session:

select sid, serial# from v$session where sid=144;

exec dbms_system.set_sql_trace_in_session(144,1683,true);
--some statement execute

exec dbms_system.set_sql_trace_in_session(144,1683,false);

10046 trace session:
exec dbms_monitor.session_trace_enable(144,1683,waits=>true,binds=>true);
--some statement execute
exec dbms_monitor.session_trace_disable(144,1683);



&&&&&&&&&&&&&&&&&&&&&&

10053 event:

alter session set events '10053 trace name context forever, level 1';


explain plain for select xxxx from xxxx;
or
select xxxx from xxxx;


alter session set events '10053 trace name context off';


udump 里直接阅读即可。




SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

SQL> explain plan for select a.object_name, b.object_type, c.object_id from t1 a, t1 b, t1 c;

Explained.

SQL> alter session set events '10053 trace name context off';

Session altered.


SQL> grant alter session to milo;

Grant succeeded.

                                                                                                                                    SQL>                                                          
SQL> select sid, serial#, sql_trace_waits, sql_trace_binds
  2  from v$session
  3  where osuser='oracle';

       SID    SERIAL# SQL_T SQL_T
---------- ---------- ----- -----
        22          1 FALSE FALSE
        24          1 FALSE FALSE
        29          7 FALSE FALSE
        32          1 FALSE FALSE
        33          1 FALSE FALSE
        35         18 FALSE FALSE
        37         15 FALSE FALSE
        40          1 FALSE FALSE
        41          1 FALSE FALSE
        42          1 FALSE FALSE
        43          1 FALSE FALSE
        44          1 FALSE FALSE
        45          1 FALSE FALSE
        46          1 FALSE FALSE
        47          1 FALSE FALSE
        48          1 FALSE FALSE
        49          1 FALSE FALSE