Oracle等待事件详解

0    717    1

Tags:

👉 本文共约81230个字,系统预计阅读时间或需306分钟。

User I/O类型

等待事件的源起

谈到等待事件,必然会提到一种流行的诊断方法论OWI,即Oracle Wait Interface.

等待事件的概念大概是从Oracle 7.0.12中引入的,刚引入的时候大约有100多个等待事件,在Oracle 8.0中这个数目增大到了大约150个,在Oracle 8i中大约有220个事件,在Oracle 9i中大约有400多个等待事件,在Oracle 10gR2中,大约有800多个等待事件,在11gR2中约有1000多个等待事件。随着等待事件的逐步完善,也能够反映出对于问题的诊断粒度越来越细化。

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

10.2.0.5版本:

select count(1) from v$event_name;

Oracle等待事件详解

11g:

select count(1) from v$event_name;

Oracle等待事件详解

分类

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#;

Oracle等待事件详解

11g:

Oracle等待事件详解

常见的空闲事件有:

• 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

This appendix contains the following topics:

■ Classes of Wait Events

■ Descriptions of Common Wait Event Parameters

■ Descriptions of Wait Events

Information about wait events is displayed in three dynamic performance views:

■ V$SESSION_WAIT displays the events for which sessions have just completed waiting or are currently waiting.

■ V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view.

■ V$SESSION_EVENT is similar to V$SYSTEM_EVENT, but displays all waits for each

session.

Many of these wait events are tied to the internal implementation of Oracle and

therefore are subject to change or deletion without notice. Application developers

should be aware of this and write their codeto tolerate missing or extra wait events.

The following SQL statement displays an alphabetical list of all Oracle wait events and the wait class to which they belong:

SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;

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')

Descriptions of Common Wait Event Parameters

Oracle® Database Reference 11g Release 2 (11.2) E40402-09

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).

le

The relative index number into V$GC_ELEMENT.

mode

The mode is 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:

Table C-1 Lock Mode Values

Mode ValueDescription
1Null mode
2Sub-Share
3Sub-Exclusive
4Share
5Share/Sub-Exclusive
6Exclusive

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_CACHE view.

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.

重要等待事件

一些常见的重要的等待事件:

1)数据文件I/O相关的等待事件:

  • db file sequential read
  • db file scattered read
  • db file parallel read
  • direct path read
  • direct path write

2)控制文件I/O相关的等待事件:

  • control file parallel write
  • control file sequential read
  • control file single write

3)重做日志文件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)

4)高速缓存区I/O相关的等待事件:

  • db file parallel write
  • db file single write
  • write complete waits
  • free buffer waits

User I/O类型

SELECT * FROM V$EVENT_NAME A WHERE A.WAIT_CLASS = 'User I/O';

Oracle等待事件详解

11g User I/O大约有84个。

db file sequential read(数据文件顺序读)

db file sequential read这个等待事件在实际生产库非常常见,是个与User I/O相关的等待事件,通常显示与单个数据块相关的读取操作,在大多数情况下,读取一个索引块或者通过索引读取一个数据块时,都会记录这个等待。当Oracle 需要每次I/O只读取单个数据块这样的操作时,会产生这个等待事件。最常见的情况有索引的访问(除IFFS外的方式),回滚操作,以ROWID的方式访问表中的数据,重建控制文件,对文件头做DUMP等。如果db file scattered read事件是伴随Multi Block I/O发生的等待事件,那db file sequential read事件就是伴随Single Block I/O发生的等待事件。每次发生Single Block I/O时,就会发生一次db file sequential read事件的等待。Single Block I/O可以发生在从文件读取一个块的所有工作上,一般在索引扫描、通过ROWID的表扫描、读取控制文件和文件头时发生。

在V$SESSION_WAIT这个视图里面,这个等待事件有三个参数P1、P2、P3,其中P1代表Oracle要读取的文件的绝对文件号即File#,P2代表Oracle从这个文件中开始读取的起始数据块的BLOCK号即Block#,P3代表Oracle从这个文件开始读取的BLOCK号后读取的BLOCK数量即Blocks,通常这个值为1,表明是单个BLOCK被读取,如果这个值大于1,则是读取了多个BLOCK,这种多BLOCK读取常常出现在早期的Oracle版本中从临时段中读取数据的时候。

这个等待事件有三个参数:

File#: 要读取的数据块所在数据文件的文件号。

Block#: 要读取的起始数据块号。

Blocks:要读取的数据块数目(这里应该等于1)。

SELECT *

FROM v$event_name

WHERE NAME = 'db file sequential read';

Oracle等待事件详解

db file sequential read等待使性能出现问题,这些性能问题大多数发生在低效的索引扫描、行迁移、行链接引发附加的I/O过程中。

1、应用程序层

低效的sql语句或低效的索引扫描经常被使用时,因不必要的物理I/O增加,可能增加db file sequential read等待。使用选择性较差的索引是发生db file sequential read等待的主要原因。

2、oracle内存层

如果高速缓冲区过小,就会反复发生物理I/O,因此可能增加db file sequential read等待,这时同时发生free buffer waits等待的概率较高。如果大量发生free buffer waits等待,应该考虑扩展高速缓存区的大小。始终要考虑利用多重缓冲池,有效使用高速缓存区。利用多重缓冲池减少db file sequential read等到的原理,与减少db file scattered read等待的原理相同。

3、OS/裸设备层

如果sql优化或高速缓存区优化、重建表也不能解决问题,就应该怀疑I/O系统本身的性能。将db file sequential read事件的等待次数和等待时间比较后,如果平均等待时间长,缓慢的I/O系统成为原因的可能性高。之前也讨论过,I/O系统上的性能问题在多钟情况下均会发生,因此需要充分调查各种因素。

利用v$filestat视图,可分别获得各数据文件关于Multi Block I/O和Single Block I/O的活动信息。

SELECT F.FILE#,

F.NAME,

S.PHYRDS,

S.PHYBLKRD,

S.READTIM, --所有的读取工作信息

S.SINGLEBLKRDS,

S.SINGLEBLKRDTIM, --SINGLE BLOCK I/O

(S.PHYBLKRD - S.SINGLEBLKRDS) AS MULTIBLKRD, --MULTI BLOCK I/O次数

(S.READTIM - S.SINGLEBLKRDTIM) AS MULTIBLKRDTIM, --MULTI BLOCK I/O时间

ROUND(S.SINGLEBLKRDTIM /

DECODE(S.SINGLEBLKRDS, 0, 1, S.SINGLEBLKRDS),

3) AS SINGLEBLK_AVGTIM, --SINGLE BLOCK I/O 平均等待时间(CS)

ROUND((S.READTIM - S.SINGLEBLKRDTIM) /

NULLIF((S.PHYBLKRD - S.SINGLEBLKRDS), 0),

3) AS MULTIBLK_AVGTIM --MULTI BLOCK I/O 平均等待时间(CS)

FROM V$FILESTAT S, V$DATAFILE F

WHERE S.FILE# = F.FILE#;

如果特点文件上平均执行时间表现的过高,则应该通过提高该文件所在的I/O系统的性能,以此改善性能。没有关于Multi Block I/O的最合理的平均等待时间值,但一般应该维持10微妙左右的平均等待时间。

在Oracle 10g中,这个等待事件被归入User I/O一类:

这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著,可能表示在多表连接中,表的连接顺序存在问题,可能没有正确的使用驱动表;或者可能索引的使用存在问题,不加选择地进行索引,并非索引总是最好的选择。

还有一种特殊的情况是,全表扫描过程还会产生单块读的情况有,读UNDO块。可以参考最后的老熊文章的例子http://blog.itpub.net/26736162/viewspace-2123513/。对于这种情况的解决办法是加索引,或等大事务执行完成后再执行SQL。

这里的sequential也并非指的是Oracle 按顺序的方式来访问数据,和db file scattered read一样,它指的是读取的数据块在内存中是以连续的方式存放的。

在大多数的情况下读取一个索引数据的BLOCK或者通过索引读取数据的一个BLOCK的时候都会去要读取相应的数据文件头的BLOCK。在早期的版本中会从磁盘中的排序段读取多个BLOCK到高速缓存区的连续的缓存中。

在大多数情况下,通过索引可以更为快速地获取记录,所以对于一个编码规范、调整良好的数据库,这个等待事件很大通常是正常的。有时候这个等待过高和存储分布不连续、连续数据块中部分被缓存有关,特别对于DML频繁的数据表,数据以及存储空间的不连续可能导致过量的单块读,定期的数据整理和空间回收有时候是必须的。但是在很多情况下,使用索引并不是最佳的选择,比如读取较大表中大量的数据,全表扫描可能会明显快于索引扫描,所以在开发中就应该注意,对于这样的查询应该避免使用索引扫描。

Oracle等待事件详解

如果这个等待事件在整个等待时间中占主要的部分,可以采用以下的几种方法来调整数据库。

方法一:从AWR的报告中的"SQL ordered by Reads"部分或者从V$SQL视图中找出读取物理磁盘I/O最多的几个SQL语句,优化这些SQL语句以减少对I/O的读取需求。

如果有Index Range scans,但是却使用了不该用的索引,就会导致访问更多的BLOCK,这个时候应该强迫使用一个可选择的索引,使访问同样的数据尽可能的少的访问索引块,减少物理I/O的读取;如果索引的碎片比较多,那么每个BLOCK存储的索引数据就比较少,这样需要访问的BLOCK就多,这个时候一般来说最好把索引rebuild,减少索引的碎片;如果被使用的索引存在一个很大的Clustering Factor,那么对于每个索引BLOCK获取相应的记录的时候就要访问更多表的BLOCK,这个时候可以使用特殊的索引列排序来重建表的所有记录,这样可以大大的减少Clustering Factor,例如:一个表有A,B,C,D,E五个列,索引建立在A,C上,这样可以使用如下语句来重建表:

CREATE TABLE TABLE_NAME AS SELECT * FROM old ORDER BY A,C;

此外,还可以通过使用分区索引来减少索引BLOCK和表BLOCK的读取。

方法二:如果不存在有问题的执行计划导致读取过多的物理I/O的特殊SQL语句,那么可能存在以下的情况:

数据文件所在的磁盘存在大量的活动,导致其I/O性能很差。这种情况下可以通过查看AWR报告中的"File I/O Statistics"部分或者V$FILESTAT视图找出热点的磁盘,然后将在这些磁盘上的数据文件移动到那些使用了条带集、RAID等能实现I/O负载均衡的磁盘上去。

使用如下的查询语句可以得到各个数据文件的I/O分布:

SELECT d.name NAME,

f.phyrds,

f.phyblkrd,

f.phywrts,

f.phyblkwrt,

f.readtim,

f.writetim

FROM v$filestat f,

v$datafile d

WHERE f.file# = d.file#

ORDER BY f.phyrds DESC,

f.phywrts DESC;

从Oracle9.2.0开始,我们可以从V$SEGMENT_STATISTICS视图中找出物理读取最多的索引段或者是表段,通过查看这些数据,可以清楚详细的看到这些段是否可以使用重建或者分区的方法来减少所使用的I/O。如果Statpack设置的level为7就会在报告中产生"Segment Statistics"的信息。

SELECT statistic_name,

COUNT(1)

FROM v$segment_statistics T

GROUP BY T.STATISTIC_NAME;

Oracle等待事件详解

从上面的查询可以看到相应的统计名称,使用下面的查询语句就能得到读取物理I/O最多的段:

SELECT object_name,

object_type,

statistic_name,

VALUE

FROM v$segment_statistics

WHERE statistic_name = 'physical reads'

ORDER BY VALUE DESC;

方法三:如果不存在有问题的执行计划导致读取过多的物理I/O的特殊SQL语句,磁盘的I/O也分布的很均匀,这种时候我们可以考虑增大的高速缓存区。对于Oracle8i来说增大初始化参数DB_BLOCK_BUFFERS,让Statpack中的Buffer Cache的命中率达到一个满意值;对于Oracle9i来说则可以使用Buffer Cache Advisory工具来调整Buffer Cache;对于热点的段可以使用多缓冲池,将热点的索引和表放入到KEEP Buffer Pool中去,尽量让其在缓冲中被读取,减少I/O。

例子

老熊的一篇文章:常识之外,全表扫描为何产生大量db file sequential read单块读(常识之外:全表扫描为何产生大量 db file sequential read 单块读?http://blog.itpub.net/26736162/viewspace-2123513/):,介绍了,**全表扫描过程还会产生单块读的情况有,读UNDO块。**

db file scattered read(数据文件离散读)

在V$SESSION_WAIT这个视图里面,这个等待事件有三个参数P1、P2、P3,其中P1代表Oracle要读取的文件的绝对文件号,P2代表Oracle从这个文件中开始读取的BLOCK号,P3代表Oracle从这个文件开始读取的BLOCK号后读取的BLOCK数量。

SELECT *

FROM v$event_name

WHERE NAME IN ('db file sequential read', 'db file scattered read');

Oracle等待事件详解

从V$EVENT_NAME视图可以看到,该等待事件有3个参数:

File#: 要读取的数据块所在数据文件的文件号。

Block#: 要读取的起始数据块号。

Blocks:需要读取的数据块数目。

这样就可以找到那个对象:

SELECT EVENT, P1, P2, P3, ROW_WAIT_OBJ#

FROM GV$SESSION

WHERE EVENT = 'db file scattered read';

SELECT OBJECT_NAME, OBJECT_TYPE

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Oracle等待事件详解后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部