ORACLE TRACE 10046

ALTER SESSION SET SQL_TRACE = TRUE ;(exec sys.dbms_support.start_trace ;)

Alter session set events '?10046 trace name context forever, level 12';

--sql

Alter session set events '10046 trace name context off';

ALTER SESSION SET SQL_TRACE = FALSE;(exec sys.dbms_support.stop_trace ;)

oradebug setmypid xxxx

--sql

oradebug event 10046 trace name context forever, level 12;

oradebug event 10046 trace name context off ;

--select a.SID,a.SERIAL# from v$session a where a.SID=&sid

方式一

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', TRUE);

exec dbms_system.set_ev(18, 226, 10046, 12, '');

--sql

exec dbms_system.set_ev(18, 226, 10046, 0, '');

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', FALSE);

exec dbms_system.set_sql_trace_in_session(18,226,TRUE);

exec sys.dbms_monitor.session_trace_enable(session_id=>18,serial_num=>226, waits=>true, binds=>true);

--sql

exec sys.dbms_monitor.session_trace_disable(session_id=>18,serial_num=>226);

exec dbms_system.set_sql_trace_in_session(18,226,FALSE);

--select b.SPID from v$session a,v$process b where a.PADDR=b.ADDR and a.SID=&SID;

--獲取os程式id

oradebug setospid xxxx

oradebug event 10046 trace name context forever, level 12;

oradebug tracefile_name

oradebug event 10046 trace name context off ;

  These are bit values so can be ORed together to get different mixes

        1  - Enable standard SQL_TRACE functionality (Default)

        4  - As Level 1 PLUS trace bind values           [ bind=true ]

        8  - As Level 1 PLUS trace waits                 [ wait=true ]

             This is especially useful for spotting latch wait etc.

             but can also be used to spot full table scans and index scans.

  As of 11g these additional bit levels are available:

        16 - Generate STAT line dumps for each execution [ plan_stat=all_executions ]

        32 - Never dump execution statistics             [ plan_stat=never ]

  As of 11.2.0.2 this additional bit level is available:

        64 - Adaptive dump of STAT lines.                [ plan_stat=adaptive ]

             This dumps the STAT information if a SQL took more than about 1 minute thereby

             giving information for the more expensive SQLs and for different executions of such

             SQLs.

  eg: A common event level is 12 which includes standard SQL_TRACE output, binds, waits and

        default STAT line tracing.

Notes:

 STAT dumping has been amended in 11g so that they are not aggregated across all executions but are

 dumped after execution.  This  has been done to address cases where the cursor is not closed and the

 STAT information is therefore not dumped. Now we guarantee to capture the STAT information

 following the execution. See the above bit levels to have finer control over STAT lines.