Oracle events描述

 

 

1、10046事件：

10046 event是oracle用于系统性能分析时的一个最重要的事件。当激活这个事件后，将通知oracle kernel追踪会话的相关即时信息，并写入到相应trace文件中。这些有用的信息主要包括sql是如何进行解析，绑定变量的使用情况，会话中发生的等待事件等10046event 可分成不同的级别（level），分别追踪记录不同程度的有用信息。对于这些不同的级别，应当注意的是向下兼容的，即高一级的trace信息包含低于此级的所有信息。

10046 event的启用和关闭：
前提条件：（先确保要event的会话环境符合条件）
1、 必须确保timed_statistics为TRUE，这个参数可以在会话级上进行修改。
2、 为了确保trace输出能够完整进行，还要调整此会话对trace文件大小的限制，一般将此限制
取消，即将max_dump_file_size设置为UNLIMITED，或者设置为一个很大的阈值。

在满足了上述条件后，就可以启用10046event对会话进行后台跟踪了。

这里还有几种方式来启用10046event：
一种是在当前会话启用event，可以利用alter session + 事件名称 + level，
如：sql>alter session set events ‘10046 trace name context forever, level 12’;

另外一种是启用别的会话进行event跟踪，可以利用oracle提供的dbms_system来完成。
如：sql>exec dbms_system.set_ev(sid,serial#,10046,12,’’);
注意：
sql>exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
相当于打开了sql_trace。

event的关闭：
可以在通过下面的语句来关闭当前会话的event：
sql>alter session set event ‘10046 trace name context off’;
也可以利用dbms_system包来关闭某个会话的event：
sql>execute dbms_system.set_ev(sid,serial#,0,’’);

这里应当值得一提的是，TRACE将消耗相当的系统资源，因此我们在使用TRACE的时候应当慎重。对于正式的系统，应当只在必要的时候进行TRACE操作，并且应当及时关闭。

当利用事件trace完当前或某个session后，接下来我们的工作就是找到oracle生成的trace了。
Oracle的初始化文件中user_dump_dest参数的设置将决定trace文件的生成位置。

从trace文件中查找和发现有用的信息，然后寻找必要的性能调整点并进行相应的调整：

大部分情况下，通过10046事件trace到文件里的信息包含了此会话中存在的性能问题，可以根据trace到的等待事件、SQL语句执行情况以及绑定变量的使用情况来进行分析和查找。

oracle提供了一个工具tkprof来对trace文件进行格式的翻译，以便trace文件中记录的信息能够
被我们容易掌握和获取。

10046event，是Oracle提供的内部事件，是对SQL_TRACE的增强 的追踪级别大致有：
level 1：启用标准的SQL_TRACE功能,等价于sql_trace，跟踪sql语句，包括解析、执行、提取、提交和回滚等。
level 4：Level 1 加上绑定值(bind values)，包括变量的详细信息
level 8：Level 1 + 等待事件跟踪，包括等待事件
level 12：Level 1 + Level 4 + Level 8,包括绑定变量与等待事件

2、alter system|session set events ‘[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : …….’

 

alter system set events相关知识：

格式：alter system|session set events ‘[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : …….’

通过:符号，可以连续设置多个事件，也可以通过连续使用alter session set events来设置多个事件。

格式说明：eventnumber指触发dump的事件号，事件号可以是Oracle错误号（出现相应错误时跟踪指定的事件）或oralce内部事件号，内部事件号在10000到10999之间，不能与immediate关键字同用。

immediate关键字表示命令发出后，立即将指定的结构dump到跟踪文件中，这个关键字只用在alter session语句中，并且不能与eventnumber、forever关键字同用。

trace name是关键字。

eventname指事件名称（见后面），即要进行dump的实际结构名。若eventname为context，则指根据内部事件号进行跟踪。

forever关键字表示事件在实例或会话的周期内保持有效状态，不能与immediate同用。

level为事件级别关键字。但在dump错误栈(errorstack)时不存在级别。

levelnumber表示事件级别号，一般从1到10，1表示只dump结构头部信息，10表示dump结构的所有信息。

1、buffers事件：dump SGA缓冲区中的db buffer结构

alter session set events ‘immediate trace name buffers level 1′; –表示dump缓冲区的头部。

2、blockdump事件：dump数据文件、索引文件、回滚段文件结构

alter session set events ‘immediate trace name blockdump level 66666′; –表示dump块地址为6666的数据块。

在Oracle 8以后该命令已改为：

alter system dump datafile 11 block 9; –表示dump数据文件号为11中的第9个数据块。

3、controlf事件：dump控制文件结构

alter session set events ‘immediate trace name controlf level 10′; –表示dump控制文件的所有内容。

4、locks事件：dump LCK进程的锁信息

alter session set events ‘immediate trace name locks level 5′;

5、redohdr事件：dump redo日志的头部信息

alter session set events ‘immediate trace name redohdr level 1′; –表示dump redo日志头部的控制文件项。

alter session set events ‘immediate trace name redohdr level 2′; –表示dump redo日志的通用文件头。

alter session set events ‘immediate trace name redohdr level 10′; –表示dump redo日志的完整文件头。

注意：redo日志的内容dump可以采用下面的语句:

alter system dump logfile ‘logfilename’;

6、loghist事件：dump控制文件中的日志历史项

alter session set events ‘immediate trace name loghist level 1′; –表示只dump最早和最迟的日志历史项。

levelnumber大于等于2时，表示2的levelnumber次方个日志历史项。

alter session set events ‘immediate trace name loghist level 4′; –表示dump 16个日志历史项。

7、file_hdrs事件：dump所有数据文件的头部信息

alter session set events ‘immediate trace name file_hdrs level 1′; –表示dump所有数据文件头部的控制文件项。

alter session set events ‘immediate trace name file_hdrs level 2′; –表示dump所有数据文件的通用文件头。

alter session set events ‘immediate trace name file_hdrs level 10′; –表示dump所有数据文件的完整文件头。

8、errorstack事件：dump错误栈信息，通常Oracle发生错误时前台进程将得到一条错误信息，但某些情况下得不到错误信息，可以采用这种方式得到Oracle错误。

alter session set events ‘604 trace name errorstack forever’; –表示当出现604错误时，dump错误栈和进程栈。

9、systemstate事件：dump所有系统状态和进程状态

alter session set events ‘immediate trace name systemstate level 10′; –表示dump所有系统状态和进程状态。

10、coalesec事件：dump指定表空间中的自由区间

levelnumber以十六进制表示时，两个高位字节表示自由区间数目，两个低位字节表示表空间号，如0×00050000表示dump系统表空间中的5个自由区间，转换成十进制就是327680，即：

alter session set events ‘immediate trace name coalesec level 327680′;

11、processsate事件：dump进程状态

alter session set events ‘immediate trace name processsate level 10′;

12、library_cache事件：dump library cache信息

alter session set events ‘immediate trace name library_cache level 10′;

13、heapdump事件：dump PGA、SGA、UGA中的信息

alter session set events ‘immediate trace name heapdump level 1′;

14、row_cache事件：dump数据字典缓冲区中的信息

alter session set events ‘immediate trace name row_cache level 1′;

1).Global Area

ALTER SESSION SET EVENTS 'immediate trace name global_area level n';

1 包含PGA
2 包含SGA
4 包含UGA
8 包含indrect memory

2).Library Cache

ALTER SESSION SET EVENTS 'immediate trace name library_cache level n';

1 library cache统计信息
2 包含hash table histogram
3 包含object handle
4 包含object结构（Heap 0）

3).Row Cache

ALTER SESSION SET EVENTS 'immediate trace name row_cache level n';

1 row cache统计信息
2 包含hash table histogram
8 包含object结构

4).Buffers

ALTER SESSION SET EVENTS 'immediate trace name buffers level n';

1 buffer header
2 level 1 + block header
3 level 2 + block contents
4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
8 level 4 + users/waiters
9 level 5 + users/waiters
10 level 6 + users/waiters

5).Buffer

ALTER SESSION SET EVENTS 'immediate trace name buffer level n';

n为某个指定block的rdba，该命令可以转储某个block在buffer中的所有版本。

6).Heap

ALTER SESSION SET EVENTS 'immediate trace name heapdump level level';

1 PGA摘要
2 SGA摘要
4 UGA摘要
8 Current call(CGA)摘要
16 User call(CGA)摘要
32 Large call(LGA)摘要
1025 PGA内容
2050 SGA内容
4100 UGA内容
8200 Current call内容
16400 User call内容
32800 Large call内容

7).Sub Heap

Oracle 9.0.1版本之前

ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level n';

若n为subheap的地址，转储的是subheap的摘要信息
若n为subheap的地址+1，转储的则是subheap的内容

Oracle 9.2.0版本之后

ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level n, addr m';

其中m为subheap的地址

n为1转储subheap的摘要，n为2转储subheap的内容

8).Process State

ALTER SESSION SET EVENTS 'immediate trace name processstate level n';

9).System State

ALTER SESSION SET EVENTS 'immediate trace name systemstate level n';

10).Error State

ALTER SESSION SET EVENTS 'immediate trace name errorstack level n';

0 Error stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context area

11).Hang Analysis

ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level n';

12).Work Area

ALTER SESSION SET EVENTS 'immediate trace name workareatab_dump level n';

1 SGA信息
2 Workarea Table摘要信息
3 Workarea Table详细信息

13).Latches

ALTER SESSION SET EVENTS 'immediate trace name latches level n';

1 latch信息
2 统计信息

14).Events

ALTER SESSION SET EVENTS 'immediate trace name events level n';

1 session
2 process
3 system

15).Locks

ALTER SESSION SET EVENTS 'immediate trace name locks level n';

16).Shared Server Process

ALTER SESSION SET EVENTS 'immediate trace name shared_server_state level n';

n取值为1~14

17).Background Messages

ALTER SESSION SET EVENTS 'immediate trace name bg_messages level n';

n为pid+1

二.File Dumps

1).Block

Oracle 7之前

ALTER SESSION SET EVENTS 'immediate trace name blockdump level n';

n为block的rdba

Oracle8以后

ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;

ALTER SYSTEM DUMP DATAFILE file#
BLOCK MIN minimum_block#
BLOCK MAX maximum_block#;

2).Tree Dump

ALTER SESSION SET EVENTS 'immediate trace name treedump level n';

n为object_id

3).Undo Segment Header

ALTER SYSTEM DUMP UNDO_HEADER 'segment_name';

4).Undo for a Transaction

ALTER SYSTEM DUMP UNDO BLOCK 'segment_name' XID xidusn xidslot xidsqn;

5).File Header

ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level n';

1 控制文件中的文件头信息
2 level 1 + 文件头信息
3 level 2 + 数据文件头信息
10 level 3

6).Control file

ALTER SESSION SET EVENTS 'immediate trace name controlf level n';

1 文件头信息
2 level 1 + 数据库信息 + 检查点信息
3 level 2 + 可重用节信息
10 level 3

7).Redo log Header

ALTER SESSION SET EVENTS 'immediate trace name redohdr level n';

1 控制文件中的redo log信息
2 level 1 + 文件头信息
3 level 2 + 日志文件头信息
10 level 3

8).Redo log

ALTER SYSTEM DUMP LOGFILE 'FileName';

ALTER SYSTEM DUMP LOGFILE 'FileName'
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber . BlockNumber
DBA MAX FileNumber . BlockNumber
RBA MIN LogFileSequenceNumber . BlockNumber
RBA MAX LogFileSequenceNumber . BlockNumber;

其中time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi)* 60 + ss;

9).Loghist

ALTER SESSION SET EVENTS 'immediate trace name loghist level n';

1dump控制文件中最早和最迟的日志历史项
>1 dump 2^n个日志历史项

 

 

 

 

 

 

3、Oracle常用dump命令

一.Memory Dumps

1).Global Area
ALTER SESSION SET EVENTS ‘immediate trace name global_area level n’;
1 包含PGA
2 包含SGA
4 包含UGA
8 包含indrect memory

2).Library Cache
ALTER SESSION SET EVENTS ‘immediate trace name library_cache level n’;
1 library cache统计信息
2 包含hash table histogram
3 包含object handle
4 包含object结构（Heap 0）

3).Row Cache
ALTER SESSION SET EVENTS ‘immediate trace name row_cache level n’;
1 row cache统计信息
2 包含hash table histogram
8 包含object结构

4).Buffers
ALTER SESSION SET EVENTS ‘immediate trace name buffers level n’;
1 buffer header
2 level 1 + block header
3 level 2 + block contents
4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
8 level 4 + users/waiters
9 level 5 + users/waiters
10 level 6 + users/waiters

5).Buffer
ALTER SESSION SET EVENTS ‘immediate trace name buffer level n’;
n为某个指定block的rdba，该命令可以转储某个block在buffer中的所有版本。

6).Heap
ALTER SESSION SET EVENTS ‘immediate trace name heapdump level level’;
1 PGA摘要
2 SGA摘要
4 UGA摘要
8 Current call(CGA)摘要
16 User call(CGA)摘要
32 Large call(LGA)摘要
1025 PGA内容
2050 SGA内容
4100 UGA内容
8200 Current call内容
16400 User call内容
32800 Large call内容

7).Sub Heap
Oracle 9.0.1版本之前
ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n’;
若n为subheap的地址，转储的是subheap的摘要信息
若n为subheap的地址+1，转储的则是subheap的内容
Oracle 9.2.0版本之后
ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n, addr m’;
其中m为subheap的地址
n为1转储subheap的摘要，n为2转储subheap的内容

8).Process State
ALTER SESSION SET EVENTS ‘immediate trace name processstate level n’;

9).System State
ALTER SESSION SET EVENTS ‘immediate trace name systemstate level n’;

10).Error State
ALTER SESSION SET EVENTS ‘immediate trace name errorstack level n’;
0 Error stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context area
11).Hang Analysis
ALTER SESSION SET EVENTS ‘immediate trace name hanganalyze level n’;

12).Work Area
ALTER SESSION SET EVENTS ‘immediate trace name workareatab_dump level n’;
1 SGA信息
2 Workarea Table摘要信息
3 Workarea Table详细信息

13).Latches
ALTER SESSION SET EVENTS ‘immediate trace name latches level n’;
1 latch信息
2 统计信息

14).Events
ALTER SESSION SET EVENTS ‘immediate trace name events level n’;
1 session
2 process
3 system

15).Locks
ALTER SESSION SET EVENTS ‘immediate trace name locks level n’;

16).Shared Server Process
ALTER SESSION SET EVENTS ‘immediate trace name shared_server_state level n’;
n取值为1~14

17).Background Messages
ALTER SESSION SET EVENTS ‘immediate trace name bg_messages level n’;
n为pid+1

二.File Dumps

1).Block
Oracle 7之前
ALTER SESSION SET EVENTS ‘immediate trace name blockdump level n’;
n为block的rdba
Oracle8以后
ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;
ALTER SYSTEM DUMP DATAFILE file#
BLOCK MIN minimum_block#
BLOCK MAX maximum_block#;

2).Tree Dump
ALTER SESSION SET EVENTS ‘immediate trace name treedump level n’;
n为object_id

3).Undo Segment Header
ALTER SYSTEM DUMP UNDO_HEADER ’segment_name’;

4).Undo for a Transaction
ALTER SYSTEM DUMP UNDO BLOCK ’segment_name’ XID xidusn xidslot xidsqn;

5).File Header
ALTER SESSION SET EVENTS ‘immediate trace name file_hdrs level n’;
1 控制文件中的文件头信息
2 level 1 + 文件头信息
3 level 2 + 数据文件头信息
10 level 3

6).Control file
ALTER SESSION SET EVENTS ‘immediate trace name controlf level n’;
1 文件头信息
2 level 1 + 数据库信息 + 检查点信息
3 level 2 + 可重用节信息
10 level 3

7).Redo log Header
ALTER SESSION SET EVENTS ‘immediate trace name redohdr level n’;
1 控制文件中的redo log信息
2 level 1 + 文件头信息
3 level 2 + 日志文件头信息
10 level 3

8).Redo log
ALTER SYSTEM DUMP LOGFILE ‘FileName’;
ALTER SYSTEM DUMP LOGFILE ‘FileName’
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber . BlockNumber
DBA MAX FileNumber . BlockNumber
RBA MIN LogFileSequenceNumber . BlockNumber
RBA MAX LogFileSequenceNumber . BlockNumber;
其中time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;

9).Loghist
ALTER SESSION SET EVENTS ‘immediate trace name loghist level n’;
1 dump控制文件中最早和最迟的日志历史项
1 dump 2^n个日志历史项

 

 

 

 

 

 

 

 

 

 

 

 

 

4、Metalink上的Event事项

 

Bookmark Go to End   Doc ID: Note:75713.1 Subject: Important Customer information about using Numeric Events Type: REFERENCE Status: PUBLISHED   Content Type: TEXT/X-HTML Creation Date: 25-OCT-1999 Last Revision Date: 30-JUL-2004   Important Customer Information about numeric EVENTS This short note gives important information for any customer wanting to either:   A. Add an EVENT="..." line to their initSID.ora startup parameters or   B. Use an ALTER SESSION / SYSTEM SET EVENTS='...'; command   Please read the information here before setting any event. If you are not sure about an EVENT then contact your local Support Centre. The exact syntax to be used between the quotation marks should be given to you either from Oracle Support or from the article which directed you to this Note. What is an EVENT ? An EVENT is a special item used by the Oracle server to do one of the following: Change behaviour Enable collection of trace or debug information Enable additional error checking or similar When can I set an EVENT ? You should only ever set an EVENT if: Directed to do so by Oracle Support Services Directed to do so by an article or bulletin which applies to the Oracle RDBMS version that you are using AND you understand what the EVENT is going to change. It is extremely important that you understand the effects of any event that you set. If you are not sure contact your local Support Centre. Other IMPORTANT considerations 1) Make sure the event is valid for the Oracle release you are running The behaviour of an event can change between Oracle releases. For example: An event to disable a feature in one release may force a corruption in a different release. 2) Make sure you know the LEVEL that an event should be set at. In many cases the LEVEL of an event affects the behaviour of the event. A typical event syntax is:          EVENT="12345 trace name context forever, level 99" In this example the EVENT is 12345 and the LEVEL is 99. 3. If a problem occurs when an event is set then it is worth seeing if the same problem reproduces without the event set. This is because events can enable code specific paths which are not normally used. 4. Any events set in the initSID.ora file should be re-assessed prior to any upgrade. This is important as the EVENT may have a different meaning in the release you wish to upgrade to. Setting an EVENT in INIT.ORA The syntax to set an event in the initSID.ora file should be specified in the document which tells you to set the event. If not then contact Oracle Support. Note that ALL events in the initSID.ora file should be grouped together on successive lines. For example: If you have to set event 10046 and event 10053 then              put the entries on adjacent lines in the initSID.ora thus:                   EVENT="10046 trace name context forever, level 12"                 EVENT="10053 trace name context forever, level 1" It is sensible to always add a comment against any EVENT in the initSID.ora file describing: WHAT the event is for WHY the event has been added WHEN the event was added / by whom / and what the Oracle version was at the time WHERE the information about the event came from. When removing an event from the init.ora file it is sensible to comment it out with details of WHEN / WHERE / WHY it was removed rather than deleting the lines. Disabling an active EVENT Some events are active for the duration of the session or instance up time. These are normally established using the syntax of 'context forever'. For example, when set in the current session, the following event will remain active until the session is terminated:   ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Similarly, when set SYSTEM wide, the following event will remain active until the instance is shutdown:   ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12'; However, the event can be disabled at any time using the following syntax:- For the current session : ALTER SESSION SET EVENTS '10046 trace name context off'; SYSTEM wide: ALTER SYSTEM SET EVENTS '10046 trace name context off'; Hence the forever keyword is replaced by the off keyword to disable the given event. Note that disabling a SYSTEM wide event will only take effect for new connections and will not impact existing connections. .
	Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.

 

 

 

5、Oracle事件参考手册：

Event Reference

10013 - Monitor Transaction Recovery

10015 - Dump Undo Segment Headers

10032 - Dump Sort Statistics

10033 - Dump Sort Intermediate Run Statistics

10045 - Trace Free List Management Operations

10046 - Enable SQL Statement Trace

10053 - Dump Optimizer Decisions

10060 - Dump Predicates

10065 - Restrict Library Cache Output for State Object Dumps

10079 - Dump SQL*Net Statistics

10081 - Dump High Water Mark Changes

10104 - Dump Hash Join Statistics

10128 - Dump Partition Pruning Information

10200 - Dump Consistent Reads

10201 - Dump Consistent Read Undo Application

10220 - Dump Changes to Undo Header

10221 - Dump Undo Changes

10224 - Dump Index Block Splits / Deletes

10225 - Dump Changes to Dictionary Managed Extents

10241 - Dump Remote SQL Execution

10246 - Trace PMON Process

10248 - Trace Dispatcher Processes

10249 - Trace Shared Server (MTS) Processes

10270 - Debug Shared Cursors

10357 - Debug Direct Path

10390 - Dump Parallel Execution Slave Statistics

10391 - Dump Parallel Execution Granule Allocation

10393 - Dump Parallel Execution Statistics

10500 - Trace SMON Process

10608 - Trace Bitmap Index Creation

10704 - Trace Enqueues

10706 - Trace Global Enqueue Manipulation

10708 - Trace RAC Buffer Cache

10710 - Trace Bitmap Index Access

10711 - Trace Bitmap Index Merge Operation

10712 - Trace Bitmap Index OR Operation

10713 - Trace Bitmap Index AND Operation

10714 - Trace Bitmap Index MINUS Operation

10715 - Trace Bitmap Index Conversion to ROWIDs

10716 - Trace Bitmap Index Compress / Decompress

10717 - Trace Bitmap Index Compaction

10719 - Trace Bitmap Index DML

10730 - Trace Fine Grained Access Predicates

10731 - Trace CURSOR Statements

10928 - Trace PL/SQL Execution

10938 - Trace PL/SQL Execution Statistics