oracle Event:10046
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[@more@]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.
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[@more@]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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/190276/viewspace-902826/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
