目录
等待事件主要可以分为两类,即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件。
空闲等待事件,是指Oracle正等待某种工作,比如用sqlplus登录之后,但没有进一步发出任何命令,此时该session就处于SQL*Net message from/to client等待事件状态,等待用户发出命令,任何的在诊断和优化数据库的时候,一般不用过多注意这部分事件。
非空闲等待事件,专门针对Oracle的活动,指数据库任务或应用运行过程中发生的等待,这些等待事件是调整数据库的时候应该关注与研究的。
等待的定位方式
Sql 级别-- 10046 event;
如果想知道一条sql在执行过程中在那些方面产生了等待,可以做一个10046事件的trace;
会话级别--v$session_wait
如果某个会话非常慢,可以通过该视图进行查看;
系统级别--AWR报告和v$system_event
v$system_event是一个累计的结果,不太很好,AWR报告更直观。
等待事件分类
可以通过下面的方法,观察系统包含的等待事件数量及大致分类(以下语句在11g环境运行)
select a.wait_class#,a.wait_class_id,a.wait_class,count(1)
from v$event_name a
group by a.wait_class#,a.wait_class_id,a.wait_class
order by a.wait_class#;
等待事件类型 | 描述 |
administraive | 管理类,此类等待事件是由于DBA的管理命令引起的,这些命令要求用户处于等待状态(比如,重建索引)。 |
Application | 应用程序类,此类等待事件是由于用户应用程序的代码引起的(比如,锁等待) 。 |
Cluster | 群集类,此类等待事件和真正应用群集RAC的资源有关(比如,gc cr block busy等待事件) 。 |
Commit | 提交确认类,此类等待事件只包含一种等待事件——在执行了一个commit命令后,等待一个重做日志写确认(也就是log file sync) 。 |
Concurrency | 并发类,此类等待事件是由内部数据库资源引起的(比如闩锁) 。 |
Configuration | 配置类,此类等待事件是由数据库或实例的不当配置造成的(比如,重做日志文件尺寸太小,共享池的大小等) 。 |
Idle | 空闲类,此类等待事件意味着会话不活跃,等待工作(比如,sql * net messages from client) 。 |
Network | 网络类,和网络环境相关的一些等待事件(比如sql* net more data to dblink) 。 |
Other | 其它类,此类等待事件通常比较少见(比如wait for EMON to spawn) |
Scheduler | 调度类,此类等待事件和资源管理相关(比如resmgr: become active') 。 |
System I/O | 系统I/O类,此类等待事件通过是由后台进程的I/O操作引起的(比如DBWR等待-db file paralle write) 。 |
User I/O | 用户I/O类,此类等待事件通常是由用户I/O操作引起的(比如db file sequential read) 。 |
Queue | 队列 |
观察等待事件的视图
系统级
v$event_name 系统支持的等待事件,可以查看等待事件所属类别、参数的含义等信息。
v$system_wait_class 等待事件类别的统计信息(系统级)。通过这一视图,可从全局角度了解系统那类操作等待较多。
v$system_event等待事件的统计信息(系统级)。展开来说,是提供了自实例启动后各个等待事件的概括。常用于获取系统等待信息的历史影象。而通过两个snapshot获取等待项增量,则可以确定这段时间内系统的等待项。
主要字段 | 字段类型 | 注释 |
event | varchar2(64) | |
total_waits | number | 自数据库启动到现在,此等待事件总等待次数。 |
total_timeouts | number | 此等待事件总等待超时次数。 |
time_waited | number | 此等待事件的总等待时间(单位:百分之一秒)。这个数据表示从数据库启动以来这个等待事件在所有会话(包括已经结束和正保持连接状态的会话)总的等待事件之和。 |
average_wait | number | 此等待事件的平均等待用时(单位:百分之一秒)。 |
time_waited_micro | 等待事件的总时间(以微秒为单位) |
select t.wait_class,t.event,t.total_waits
,round(t.time_waited_micro/1000) as total_ms
,round((t.time_waited_micro*100)/sum(t.time_waited_micro)over(),4) as pct_time
,round((t.time_waited_micro/t.total_waits)/1000,2) as avg_ms
from v$system_event t
where t.wait_class <> 'Idle'
order by time_waited_micro desc;
会话级
v$session_event
和v$system_event相类似,记录的是会话在其生命周期中各个等待事件的累计值。跟前者相比,增加了session_id信息。这些信息也会被同时累积到v$system_event中。需要注意的是,当一个会话重新建立时,统计信息将被设置为0。
v$session_wait
查询当前会话的等待,当等待不再存在时,会话先前出现的那些等待的历史也将消失,从而使得事后诊断非常困难。V$SESSION_EVENT提供了累积的但不是非常详细的数据。可以通过历史视图v$session_wait_history获得历史信息。
其中字 P1text、p1、p2text、p2、p3text、p3 不同的 event 表示的含义不一样;
常用字段
字段名 | 含义 |
EVENT | 会话当前等待的事件,或者最后一次等待事件 |
WAIT_TIME | 会话等待事件的时间(单位:百分之一秒)。 值>0: 最后一次等待时间(单位:10ms),当前未在等待状态。 值=0: session正在等待当前的事件。 值=-1: 最后一次等待时间小于1个统计单位,当前未在等待状态。 值=-2: 时间统计状态未置为可用,当前未在等待状态。 |
STATE | waiting:SESSION正等待这个事件。 waited unknown time:由于设置了timed_statistics值为false,导致不能得到时间信息。表示发生了等待,但时间很短。 wait short time:表示发生了等待,但由于时间非常短不超过一个时间单位,所以没有记录。 waited knnow time:如果session等待然后得到了所需资源,那么将从waiting进入本状态。 |
WAIT_TIME/SECOND_IN_WAIT | Wait_time和Second_in_wait字段值与state相关。 1)state=waiting wait_time无用,second_in_wait值是实际的等待时间(单位:秒)。 2)state=wait unknow time wait_time和second_in_wait都无用。 3)state=wait short time wait_time和second_in_wait都无用。 4)state=waiting known time wait_time值就是实际等待时间(单位:秒),second_in_wait值无用。 |
v$session_wait_history
记录会话最近n次等待事件,即v$session_wait的历史记录;默认是记录10次,可进行修改。
v$event_histogram
这个视图记录了等待事件的柱状图分布,从而可以对一个等待事件具体分布有进一步了解。在v$session_event或v$system_event视图记录的是累积信息以及关于等待的平均值,无法得知个别等待消耗的时间。
下面将会话等待事件与各视图之间的关系,总结整理如下:
1.一个会话一次只发生一个等待事件。如果看到了其他的等待事件,那仅仅表示在下一个时间片上发生了等待。在某个时刻只存在一个等待。
2.v$session_wait中的wait_time和second_in_wait字段以秒为单位,而v$session_event中的time_waited和average_wait字段是以百分之一秒为单位。
3.v$session_wait的等待事件结束后,v$session_event的统计信息将会发生改变。
4.v$session_wait的统计信息意义不大,因为信息是实时变化的。
- 当v$session_wait里面的等待事件结束时,v$session_wait中的seconds_in_wait字段值被复制到v$session_event中time_waited字段,而v$session_event视图的average_time字段同时也被修改。
常见等待事件
空闲等待事件--ilde wait events
进程无事可做,等待分配任务
可能还出现其他问题,导致该事件处于等待状态
发散读-- db file scattered read
这是一个用户操作引起的等待事件,当用户发出每次I/O需要读取多个数据块这样的SQL操作时,读取到SGA中,会产生这个等待事件,最常见的两种情况全表扫描和索引快速扫描。这个名称中的scattered(发散)可能会导致很多人认为它是以scattered的方式来读取数据块的,其实恰恰相反,当发生这种等待事件时,SQL的操作都是顺序地读取数据块的,比如FTS或IFFS方式。其实这里scattered指的是读取的数据块在内存中的存放方式。它们被读取到内存中后,是以分散的方式存放在内存中,而不是连续的。
每次读取的数据块数量由参数db_file_multiblock_read_count确定;具体的还有根据实际情况来看。
10046结果参数含义:
file# 代表oracle要读取的文件的绝对文件号。
block# 从这个文件中开始读取的起始数据块块号。
Blocks 读取的block数量。
优化方向:
1.看其是否是正常的情况,正常则不需要解决;比如 物理磁盘读到内存 本身就会产生一定的等待。
2.的确等待时间很长,看是否索引方面有问题
3.如果这个并不是索引引起的,而且比较慢,考虑开启一些并行
顺序的读-- db file sequential read
这个等待事件在实际生产库也很常见,执行对索引,回滚(undo)段,和表(当借助rowid来访问),控制文件和数据文件头的单块读操作SQL语句(用户和递归)引起的;当Oracle 需要每次I/O只读取单个数据块这样的操作时,会产生这个等待事件。 最常见的情况有索引的访问(除IFFS外的方式),回滚操作,以ROWID的方式访问表中的数据,重建控制文件,对文件头做DUMP等。
这里的sequential也并非指的是Oracle 按顺序的方式来访问数据,和db file scattered read一样,它指的是读取的数据块在内存中是以连续的方式存放的。
这个等待事件有三个参数:
File#: 要读取的数据块锁在数据文件的文件号。
Block#: 要读取的起始数据块号。
Blocks:要读取的数据块数目(这里应该等于1)。
如何解决?
– 无需解决
– SQL语句的效率
– 考虑其它方式的索引
• 符合索引
• 位图索引
• 全文索引
– 全表扫描+并行
– 改善磁盘I/O
Direct Path Read
这个等待事件发生在会话将数据块直接读取到PGA当中而不是SGA中的情况,这些被读取的数据通常是这个会话私有的数据,所以不需要放到SGA作为共享数据,因为这样做没有意义。 这些数据通常是来自与临时段上的数据,比如一个会话中SQL的排序数据,并行执行过程中间产生的数据,以及Hash Join,merge join产生的排序数据,因为这些数据只对当前的会话的SQL操作有意义,所以不需要放到SGA当中。
当发生direct path read等待事件时,意味着磁盘上有大量的临时数据产生,比如排序,并行执行等操作。 或者意味着PGA中空闲空间不足。
-- 排序数据由于内存不足,被写到磁盘上(temp表空间数据文件),然后重新读取时。
-- 并行操作的slave进程的数据读取。
-- 其它的属于某个会话私有数据的读取操作
这个等待事件有三个参数:
Descriptor address: 一个指针,指向当前会话正在等待的一个direct read I/O。
First dba: descriptor address 中最旧的一个I/O数据块地址。
Block cnt: descriptor address上下文中涉及的有效的buffer 数量。
如何解决?
– 无需解决。
– 增大内存排序区(PGA)
– 调整操作的并行度。
– 改善磁盘I/O。
Direct Path writ
这个等待事件和direct path read 正好相反,是会话将一些数据从PGA中直接写入到磁盘文件上,而不经过SGA。
– 排序数据由于内存不足,被写到磁盘上(temp表空间数据文件)。
- 数据的直接加载(使用append方式加载数据)并行DML操作。
– 并行操作的slave进程向磁盘上写数据。。
– 其它的属于某个会话私有数据的读取操作。
这个等待事件有三个参数:
Descriptor address: 一个指针,指向当前会话正在等待的一个direct I/O.
First dba: descriptor address 中最旧的一个I/O数据块地址。
Block cnt: descriptor address 上下文中涉及的有效地 buffer 数量。
Log File Sync
这是一个用户会话行为导致的等待事件,当一个会话发出一个commit命令时,LGWR进程会将这个事务产生的redo log从log buffer里面写到磁盘上,以确保用户提交的信息被安全地记录到数据库中。
会话发出的commit指令后,需要等待LGWR将这个事务产生的redo 成功写入到磁盘之后,才可以继续进行后续的操作,这个等待事件就叫作log file sync。
当系统中出现大量的log file sync等待事件时,应该检查数据库中是否有用户在做频繁的提交操作。
这种等待事件通常发生在OLTP系统上。 OLTP 系统中存在很多小的事务,如果这些事务频繁被提交,可能引起大量的log file sync的等待事件。
这个等待事件包含一个参数:
Buffer#: redo buffer 中需要被写入到磁盘中的buffer。
buffer busy waits
从本质上讲,这个等待事件的产生仅说明了一个会话在等待一个Buffer(数据块),但是导致这个现象的原因却有很多种。常见的两种是:
当一个会话视图修改一个数据块,但这个数据块正在被另一个会话修改时。
当一个会话需要读取一个数据块,但这个数据块正在被另一个会话读取到内存中时。
Oracle 操作的最小单位是块(Block),即使你要修改一条记录,也需要对这条记录所在的这个数据块做操作。 当你对这个数据块做修改时,其他的会话将被阻止对这个数据块上的数据做修改(即使其他用户修改的不是当前用户修改的数据),但是可以以一致性的方式读取这个数据块(from undo)。当前的用户修改完这个数据块后,将会立即释放掉加在这个数据块上的排他锁,这样另一个会话就可以继续修改它。 修改操作是一个非常短暂的时间,这种加锁的机制我们叫Latch。
当一个会话修改一个数据块时,是按照以下步骤来完成的:
以排他的方式获得这个数据块(Latch)
修改这个数据块。
释放Latch。
Buffer busy waits等待事件常见于数据库中存在的热块的时候,当多个用户频繁地读取或者修改同样的数据块时,这个等待事件就会产生。如果等待的时间很长,我们在AWR或者statspack 报告中就可以看到。
Free buffer waits
当一个会话将数据块从磁盘读到内存中时,它需要到内存中找到空闲的内存空间来存放这些数据块,当内存中没有空闲的空间时,就会产生这个等待;除此之外,还有一种情况就是会话在做一致性读时,需要构造数据块在某个时刻的前映像(image),此时需要申请内存来存放这些新构造的数据块,如果内存中无法找到这样的内存块,也会发生这个等待事件。
当数据库中出现比较严重的free buffer waits等待事件时,可能的原因是:
(1) data buffer 太小,导致空闲空间不够
(2) 内存中的脏数据太多,DBWR无法及时将这些脏数据写到磁盘中以释放空间
这个等待事件包含2个参数:
File#: 需要读取的数据块所在的数据文件的文件号。
Block#: 需要读取的数据块块号。
如何解决
提高硬件性能
异步i/o
增加多个dbwr进程
增大SGA