ITPub博客

首页 > 数据库 > Oracle > Oracle内置事件的说明

Oracle内置事件的说明

原创 Oracle 作者:aluocp 时间:2007-08-21 14:33:55 0 删除 编辑

Event 10013 - Monitor Transaction Recovery

This event can be used to trace transaction recovery during startup

For example

ALTER SESSION SET EVENTS

'10013 trace name context forever, level 1';

Event 10015 - Dump Undo Segment Headers

This event can be used to dump undo segment headers before and after transaction recovery

For example

ALTER SESSION SET EVENTS

'10015 trace name context forever, level 1';

Event 10032 - Dump Sort Statistics

This event can be used to dump sort statistics. Level 10 is the most detailed

For example

ALTER SESSION SET EVENTS

'10032 trace name context forever, level 10';

[@more@]

Event 10033 - Dump Sort Intermediate Run Statistics

This event can be used to dump sort intermediate run statistics. Level 10 is the most detailed

For example

ALTER SESSION SET EVENTS

'10033 trace name context forever, level 10';

Event 10045 - Trace Free List Management Operations

This event can be used to trace free list management operations

For example

ALTER SESSION SET EVENTS

'10045 trace name context forever, level 1';

Event 10046 - Enable SQL Statement Trace

This event can be used to dump SQL statements executed by a session with execution plans and statistics. Bind variable and wait statistics can optionally be included. Level 12 is the most detailed.

For example

ALTER SESSION SET EVENTS

'10046 trace name context forever, level 12';

Levels are

Level

Action

1

Print SQL statements, execution plans and execution statistics

4

As level 1 plus bind variables

8

As level 1 plus wait statistics

12

As level 1 plus bind variables and wait statistics

Event 10053 - Dump Optimizer Decisions

This event can be used to dump the decisions made by the optimizer when parsing a statement. Level 1 is the most detailed

For example

ALTER SESSION SET EVENTS

'10053 trace name context forever, level 1';

Levels are

Level

Action

1

Print statistics and computations

2

Print computations only

Event 10060 - Dump Predicates

This event can be used to force the optimizer to dump predicates to a table It is available in Oracle 7.1.3 and above, and was still working in Oracle 9.2.

This event requires the following table to be created in the schema of the user parsing the statement

CREATE TABLE kkoipt_table

(

c1 INTEGER,

c2 VARCHAR2(80)

);

To enable this event use

ALTER SESSION SET EVENTS

'10060 trace name context forever, level 1';

This example uses the following object

CREATE TABLE t1 (c01 NUMBER, c02 NUMBER);

With event 10060 set to level 1, when the following statement is executed for the first time

SELECT c01 FROM t1 WHERE c02 = 0;

It is parsed and the results written to kkoipt_table

The results can be selected using the statement

SELECT c1,c2 FROM kkoipt_table ORDER BY c1;

C1

C2

1

Table:

2

T1

3

frofand

4

"T1"."C02"=0

The following table summarises the various operations that can be reported by this event

Operation

Description

fptconst

Folding constants

fptrnum

Remove ROWNUM predicates

fptwhr

Remove all WHERE predicates except remaining ROWNUM predicates

frofkks (rowid lookup)

ROWID lookup

frofkks[i] (and-equal lookup)

start key

frofkke[i] (and-equal lookup)

end key

froiand

index-only predicate

frofkksm[i] (sort-merge)

sort-merge key

frosand (sort-merge)

sort-merge predicates

frojand (sort-merge)

join predicates

frofkks[i] (index start key)

index start key

frofkke[i] (index stop key)

index end key

frofand (hash part)

table predicate (hash)

froiand (index only filter)

index only predicate

frofand

table predicate

froutand

outer join predicates

Event 10065 - Restrict Library Cache Dump Output for State Object Dumps

The amount of library cache dump output for state object dumps can be limited using event 10065

ALTER SESSION SET EVENTS '10065 trace name context forever, level level';

where level is one of the following

Level

Description

1

Address of library object only

2

As level 1 plus library object lock details

3

As level 2 plus library object handle and library object

Level 3 is the default

Event 10079 - Dump SQL*Net Statistics

This event can be used to SQL*Net statistics. Level 2 is the most detailed

For example

ALTER SESSION SET EVENTS

'10079 trace name context forever, level 2';

Event 10081 - Trace High Water Mark Changes

This event can be used to trace high water mark changes

For example

ALTER SESSION SET EVENTS

'10081 trace name context forever, level 1';

Event 10104 - Dump Hash Join Statistics

This event can be used to hash join statistics. Level 10 is the most detailed

For example

ALTER SESSION SET EVENTS

'10104 trace name context forever, level 10';

Event 10128 - Dump Partition Pruning Information

This event can be used to partition pruning information

For example

ALTER SESSION SET EVENTS

'10128 trace name context forever, level level';

Levels are

Level

Action

0x0001

Dump pruning descriptor for each partitioned object

0x0002

Dump partition iterators

0x0004

Dump optimizer decisions about partition-wise joins

0x0008

Dump ROWID range scan pruning information

There are further levels (up to 4096?)

In Oracle 9.0.1 and above, a table must be created before level 2 of this event can be set.

The table definition is as follows

CREATE TABLE kkpap_pruning

(

partition_count NUMBER,

iterator VARCHAR2(32),

partition_level VARCHAR2(32),

order_pt VARCHAR2(12),

call_time VARCHAR2(12),

part# NUMBER,

subp# NUMBER,

abs# NUMBER

);

Event 10200 - Dump Consistent Reads

This event can be used to dump consistent reads

ALTER SESSION SET EVENTS

'10200 trace name context forever, level 1';

Event 10201 - Dump Consistent Read Undo Application

This event can be used to dump consistent read undo application

ALTER SESSION SET EVENTS

'10201 trace name context forever, level 1';

Event 10220 - Dump Changes to Undo Header

This event can be used to dump changes to the undo header (transaction table)

ALTER SESSION SET EVENTS

'10220 trace name context forever, level 1';

Event 10221 - Dump Undo Changes

This event can be used to dump undo changes applied. Level 7 is the most detailed

ALTER SESSION SET EVENTS

'10221 trace name context forever, level 7';

Event 10224 - Dump Index Block Splits / Deletes

This event can be used to dump index block splits and deletes detailed

ALTER SESSION SET EVENTS

'10224 trace name context forever, level 1';

Event 10225 - Dump Changes to Dictionary Managed Extents

This event can be used to dump changes to dictionary-managed extents made in the row cache

ALTER SESSION SET EVENTS

'10225 trace name context forever, level 1';

Event 10241 - Dump Remote SQL Execution

This event can be used to dump remotely executed SQL statements

ALTER SESSION SET EVENTS

'10241 trace name context forever, level 1';

Event 10246 - Trace PMON Process

This event can be used to trace the actions of the PMON background process

This event can only be enabled in the init.ora file using

event = "10246 trace name context forever, level 1"

The ALTER SYSTEM command does not appear to work for this event

There only appears to be one level for this event (levels 5 and 10 appear to generate the same output as level 1)

Event 10248 - Trace Dispatcher Processes

This event can be used to trace dispatcher processes

This event can be enabled in the init.ora file using

event = "10248 trace name context forever, level 10"

In Oracle 9.2 (Windows 2000) the trace is written to a file in the udump directory with a name in the format

ServiceName_dDispatcherNumber_ThreadNumber.trc

e.g.

JD92001_d000_1234.trc

Valid levels are 1 to 10 (Metalink Note)

Event 10249 - Trace Shared Server (MTS) Processes

This event can be used to trace shared server (MTS) processes

This event can be enabled in the init.ora file using

event = "10249 trace name context forever, level 10"

In Oracle 9.2 (Windows 2000) the trace is written to a file in the udump directory with a name in the format

ServiceName_sSharedServerNumber_ThreadNumber.trc

e.g.

JD92001_s000_5678.trc

Valid levels are 1 to 10 (Metalink Note)

Event 10270 - Debug Shared Cursors

This event can be used to enable debugging code in shared cursor management modules

event = "10270 trace name context forever, level 10"

Event 10299 - Debug Prefetching

This event can be used to enable debugging code for table and index block prefetching. It also enables dumping of trace by the CKPT process.

event = "10299 trace name context forever, level 1"

Event 10357 - Debug Direct Path

This event can be used to enable debugging code for direct path

ALTER SESSION SET EVENTS

'10357 trace name context forever, level 1';

Event 10390 - Dump Parallel Execution Slave Statistics

This event can be used to dump parallel slave statistics

ALTER SESSION SET EVENTS

'10390 trace name context forever, level level';

Levels are (from messages)

Level

Action

0x0001

Slave-side execution messages

0x0002

Coordinator-side execution messages

0x0004

Slave context state changes

0x0008

Slave ROWID range bind variables and xty

0x0010

Slave fetched rows as enqueued to TQ

0x0020

Coordinator wait reply handling

0x0040

Coordinator wait message buffering

0x0080

Slave dump timing

0x0100

Coordinator dump timing

0x0200

Slave dump allocation file number

0x0400

Terse format for debug dumps

0x0800

Trace CRI random sampling

0x1000

Trace signals

0x2000

Trace parallel execution granule operations

0x4000

Force compilation by slave 0

Event 10391 - Dump Parallel Execution Granule Allocation

This event can be used to dump parallel granule allocation / assignment statistics

ALTER SESSION SET EVENTS

'10391 trace name context forever, level level';

Levels are (from messages)

Level

Action

0x0001

Dump summary of each object scanned in parallel

0x0002

Full dump of each object except extent map

0x0004

Full dump of each object including extent map

0x0010

Dump summary of each granule generators

0x0020

Full dump of granule generators except granule instances

0x0040

Full dump of granule generators including granule instances

0x0080

Dump system information

0x0100

Dump reference object for the query

0x0200

Gives timing in kxfralo

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7319461/viewspace-964736/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-04

  • 博文量
    59
  • 访问量
    813568