EVENT: 10046 "enable SQL statement tracing (including binds/waits)"

EVENT: 10046 "enable SQL statement tracing (including binds/waits)" [ID 21154.1]
		Modified 11-NOV-2007 Type REFERENCE Status PUBLISHED

Event:10046
Text:   enable SQL statement timing
-------------------------------------------------------------------------------
For details of interpreting 10046 output see: Note:39817.1

Explanation:
        This is a special EVENT code. 
        It can be used to signal Oracle to perform SQL_TRACE type actions.

        The 10046 trace is the equivalent of setting SQL_TRACE=TRUE.
        The advantage of using the event is that extra details may be
        output to the trace file depending on the level specified with the
        event.

For Customers:
        This event has been wrapped up for use by customers in the
        DBMS_SUPPORT package as described in Note:62294.1

For Oracle Employees:
        The notes below describe the levels for this event.
        The methods of setting trace here are not for general customer
        use.
        
10046 EVENT levels:
        1  - Enable standard SQL_TRACE functionality (Default)
        4  - As Level 1 PLUS trace bind values
        8  - As Level 1 PLUS trace waits
             This is especially useful for spotting latch wait etc. 
             but can also be used to spot full table scans and index scans.
        12 - As Level 1 PLUS both trace bind values and waits

Example Syntax:

   Session level:

	alter session set events '10046 trace name context forever';
	alter session set events '10046 trace name context forever, level 8';
	alter session set events '10046 trace name context off';

   Init.ora:

	event="10046 trace name context forever,level 4"
        
        WARNING: This will trace ALL database sessions
        

    From oradebug (7.3+):

        oradebug event 10046 trace name context forever, level 4

    From oradbx (pre 7.3):

	event 10046 trace name context forever, level 4


Articles:
     Interpreting RAW 10046 output                            Note:39817.1
     How to enable 10046 for another session using ORADEBUG   Note:1058210.6


EXAMPLES:
~~~~~~~~~
Wait Information:
        nam is what is being waited for
        ela is the elapsed time for the operation
            In Oracle7 / 8 this is in units of hundredths of a second
            In Oracle9i onwards this is in microseconds
        p1 is the file number
        p2 is the block number 
        p3 is the number of blocks read by the operation

Waiting for a Full Table Scan: 
        WAIT #1: nam="db file scattered read" ela= 5 p1=4 p2=1435 p3=25
        WAIT #1: nam="db file scattered read" ela= 7 p1=4 p2=1461 p3=32
        WAIT #1: nam="db file scattered read" ela= 7 p1=4 p2=1493 p3=32

        The first wait indicates a mutliblock read was issued for 25 blocks
        starting at file 4 block 1435. This took 5/100th of a second.
        The next 2 lines also show multiblock reads of 32 blocks (which is
        probably the value of  )

Waiting for an Index Scan:
        WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1224 p3=1
        WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1788 p3=1

        This is the sort of output you would see on an index scan or an
        individual block access to table block.

[@more@]