【等待事件】等待事件概述（1）--等待事件的源起和分类

1 等待事件的源起

等待事件的概念大概是从ORACLE 7.0.12中引入的，大致有100个等待事件。在ORACLE 8.0中这个数目增大到了大约150个，在ORACLE 8I中大约有220个事件，在ORACLE 9IR2中大约有400个等待事件，在ORACLE 10GR2中，大约有874个等待事件，在11GR2中约有1000多个等待事件。

虽然不同版本和组件安装可能会有不同数目的等待事件，但是这些等待事件都可以通过查V$EVENT_NAME视图获得：

10.2.0.5版本：

select count(1) from v$event_name;

11g：

select count(1) from v$event_name;

2 分类

ORACLE的等待事件，主要可以分为两类，即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件。

1). 空闲等待事件指ORACLE正等待某种工作，在诊断和优化数据库的时候，不用过多注意这部分事件。

2). 非空闲等待事件专门针对ORACLE的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是在调整数据库的时候需要关注与研究的。

下面来看一下ORACLE中等待事件的主要分类及各类等待事件的个数：

SELECT a.INST_ID, A.EVENT, COUNT(1)

FROM gv$session a

where a.username is not null

and a.STATUS = 'ACTIVE'

AND A.WAIT_CLASS<>'Idle'

GROUP BY a.INST_ID,A.EVENT;

SELECT wait_class#,

wait_class_id,

wait_class,

COUNT(*) AS "count"

FROM v$event_name

GROUP BY wait_class#,

wait_class_id,

wait_class

ORDER BY wait_class#;

11g：

常见的空闲事件有:

• dispatcher timer

• lock element cleanup

• Null event

• parallel query dequeue wait

• parallel query idle wait - Slaves

• pipe get

• PL/SQL lock timer

• pmon timer- pmon

• rdbms ipc message

• slave wait

• smon timer

• SQL*Net break/reset to client

• SQL*Net message from client

• SQL*Net message to client

• SQL*Net more data to client

• virtual circuit status

• client message

一些常见的非空闲等待事件有:

• db file scattered read

• db file sequential read

• buffer busy waits

• free buffer waits

• enqueue

• latch free

• log file parallel write

• log file sync

2.1 Classes of Wait Events

Every wait event belongs to a class of wait event. The following list describes each of

the wait classes.

Administrative

Waits resulting from DBA commands that cause users to wait (for example, an index

rebuild)

Application

Waits resulting from user application code (for example, lock waits caused by row

level locking or explicit lock commands)

Cluster

Waits related to Real Application Clusters resources (for example, global cache

resources such as 'gc cr block busy')

Commit

This wait class only comprises one wait event - wait for redo log write confirmation

after a commit (that is, 'log file sync')

Concurrency

Waits for internal database resources (for example, latches)

Configuration

Waits caused by inadequate configuration of database or instance resources (for

example, undersized log file sizes, shared pool size)

Idle

Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net

message from client')

Network

Waits related to network messaging (for example, 'SQL*Net moredata to dblink')

Other

Waits which should not typically occur on a system (for example, 'wait for EMON to

spawn')

Queue

Contains events that signify delays in obtaining additional data in a pipelined

environment. The time spent on these wait events indicates inefficiency or other

problems in the pipeline. It affects features such as Oracle Streams, parallel queries, or

DBMS_PIPEPL/SQL packages.

Scheduler

Resource Manager related waits (for example, 'resmgr: become active')

System I/O

Waits for background process I/O (for example, DBWR wait for 'db file parallel write')

User I/O

Waits for user I/O (for example 'db file sequential read')

2.2 Descriptions of Common Wait Event Parameters

This section provides descriptions of some of the more common wait event parameters.

block#

This is the block number of the block for which Oracle needs to wait. The block

number is relative to the start of the file. Tofind the object to which this block belongs,

issue the following SQL statement:

select segment_name, segment_type, owner, tablespace_name

from dba_extents

where file_id = file#

and block#

between block_id and block_id + blocks - 1;

blocks

The number of blocks that is being either read from or written to the file. The block

size is dependent on the file type:

■ Database files have a block size of DB_BLOCK_SIZE

■ Logfiles and control files have a block size that is equivalent to the physical block

size of the platform

break?

If the value for this parameter equals 0, a reset was sent to the client. A nonzero value

indicates that a break was sent to the client.

class

The class of the block describes how the contents of the block are used. For example,

class 1 represents data block, and class 4 represents segment header.

dba

The initials "dba" represents the data block address, which consists of a file number

and a block number.

driver id

The address of the disconnect function of the driver that is currently being used.

file#

The following query returns the name of the database file:

select *

from v$datafile

where file# = file#;

id1

The first identifier (id1) of the enqueue or global lock takes its value from P2 or

P2RAW. The meaning of the identifier depends on the name (P1).

id2

The second identifier (id2) of the enqueue or global lock takes its value from P3 or

P3RAW. The meaning of the identifier depends on the name (P1).

The relative index number into V$GC_ELEMENT.

mode

Themodeis usually stored in the low order bytes of P1 or P1RAW and indicates the

mode of the enqueue or global lock request.This parameter has one of the following

values:

Use the following SQL statement to retrieve the name of the lock and the mode of the

lock request:

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1, 16711680)/65535) "Lock",

bitand(p1, 65535) "Mode"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

name and type

The name or "type" of the enqueue or globallock can be determined by looking at the

two high order bytes of P1 or P1RAW. The name is always two characters. Use the

following SQL statement to retrieve the lock name.

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1,16711680)/65535) "Lock"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

namespace

The name of the object namespace as it is displayed in V$DB_OBJECT_CACHEview.

requests

The number of I/Os that are "requested." This differs from the number of blocks in

that one request could potentially contain multiple blocks.

session#

The number of the inactive session. Use the following SQL statement to find more

information about the session:

select *

from v$session

where sid = session#;

waited

This is the total amount of time the sessionhas waited for this session to terminate.

Table C–1 Lock Mode Values

Mode Value Description

1 Null mode

2 Sub-Share

3 Sub-Exclusive

4Share 5Share/Sub-Exclusive 6Exclusive

3 重要等待事件

数据文件I/O相关的等待事件：

Ø db file sequential read

Ø db file scattered read

Ø db file parallel read

Ø direct path read

Ø direct path write

Ø direct path read (lob)

Ø direct path write (lob)

控制文件I/O相关的等待事件：

Ø control file parallel write

Ø control file sequential read

Ø control file single write

重做日志文件I/O相关的等待事件：

Ø log file parallel write

Ø log file sync

Ø log file sequential read

Ø log file single write

Ø switch logfile command

Ø log file switch completion

Ø log file switch (clearing log file)

Ø log file switch (checkpoint incomplete)

Ø log switch/archive

Ø log file switch (archiving needed)

高速缓存区I/O相关的等待事件：

Ø db file parallel write

Ø db file single write

Ø write complete waits

Ø free buffer waits

1 等待事件的源起

2 分类

2.1 Classes of Wait Events

2.2 Descriptions of Common Wait Event Parameters

3 重要等待事件

更多相关文章

随机推荐