[Oracle] 常见的等待事件

长平狐 发布于 2013/06/03 15:43
阅读 153
收藏 0

db file scattered read

对于一些频繁访问的表,如果没有建立索引或没有建立合适的索引,Oracle只能对其进行全表扫描,就会导致大量该等待事件。
全表扫描时,读取的数据在磁盘上一般是连续的,但是读到内存时却是不连续的,因此该事件命名为离散读(scattered read),注意不要被它的名字所迷惑。
一次多块读取的数量受参数DB_FILE_MULTIBLOCK_READ_COUNT的影响。
在实际诊断过程中,可以通过v$session_wait视图发现session的等待,再结合其他视图找到存在问题的SQL,当这个等待事件比较显著时,用户也可以结合v$session_longops动态性能视图来进行诊断。

1)模拟通过全表扫描产生db file scattered read:
Oracle 11g,在大表的全表扫描算法上有新的变化,根据表的大小、高速缓存的大小等信息,决定是否绕过SGA直接从磁盘读取数据。而10g则是全部通过高速缓存读取数据。Oracle 11g认为大表全表时使用直接路径读,可能比10g中的数据文件散列读(db file scattered reads)速度更快,效率高。
我们可以通过一个隐藏参数 "_serial_direct_read"来控制这种行为
SQL> alter system set "_serial_direct_read" = false;

系统已更改。


下面用10046事件模拟:
SQL> alter session set tracefile_identifier='jujay';

会话已更改。

SQL> alter session set events '10046 trace name context forever ,level 12' ;

会话已更改。

SQL>  Select /*+full(t)*/ * from t where object_id=-1; --通过Hint强制让优化器走全表扫描

SQL> alter session set events '10046 trace name context off';

会话已更改。

SQL>  select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Default Trace File
c:\app\xianzhu\diag\rdbms\orcl\orcl\trace\orcl_ora_3744_jujay.trc

下面是10046生成的trace文件的内容:
…….
WAIT #47878792599648: nam='db file scattered read' ela= 34 file#=8 block#=131 blocks=5 obj#=23315 tim=1356470039698744
WAIT #47878792599648: nam='db file scattered read' ela= 27 file#=8 block#=200 blocks=8 obj#=23315 tim=1356470039699325
WAIT #47878792599648: nam='db file scattered read' ela= 20 file#=8 block#=217 blocks=7 obj#=23315 tim=1356470039699788
WAIT #47878792599648: nam='db file scattered read' ela= 22 file#=8 block#=224 blocks=8 obj#=23315 tim=1356470039700179
WAIT #47878792599648: nam='db file scattered read' ela= 19 file#=8 block#=241 blocks=7 obj#=23315 tim=1356470039700589
…….

2)通过索引快速扫描产生db file scattered read
首先,创建索引:
SQL> create index i_t on t(object_id);

索引已创建。
SQL> alter session set tracefile_identifier='jujay';

会话已更改。

SQL> alter session set events '10046 trace name context forever ,level 12' ;

会话已更改。

SQL>  Select count(*) from t; --索引快速全扫描

SQL> alter session set events '10046 trace name context off';

会话已更改。

SQL>  select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Default Trace File
c:\app\xianzhu\diag\rdbms\orcl\orcl\trace\orcl_ora_3744_jujay.trc

下面是10046生成的trace文件的内容:
…….
WAIT #47878792599648: nam='db file scattered read' ela= 34 file#=8 block#=131 blocks=5 obj#=23315 tim=1356470039698744
WAIT #47878792599648: nam='db file scattered read' ela= 27 file#=8 block#=200 blocks=8 obj#=23315 tim=1356470039699325
WAIT #47878792599648: nam='db file scattered read' ela= 20 file#=8 block#=217 blocks=7 obj#=23315 tim=1356470039699788
WAIT #47878792599648: nam='db file scattered read' ela= 22 file#=8 block#=224 blocks=8 obj#=23315 tim=1356470039700179
WAIT #47878792599648: nam='db file scattered read' ela= 19 file#=8 block#=241 blocks=7 obj#=23315 tim=1356470039700589
…….

db file sequential read

当访问一个不在SGA的数据块时,就会产生该事件,一般在用索引访问时频繁发生。
在一个正常的OLTP系统中,该事件占的比例比较大是正常的,但是如果该事件的等待事件非常长,则表示当前有大量的索引读取操作,可以考虑是否全表扫描更快速呢?或者磁盘I/O太慢?

首先,创建索引:
SQL> create index i_t on t(object_id);

索引已创建。

接着以索引访问表中数据:
SQL> alter session set tracefile_identifier='jujay';

会话已更改。

SQL> alter session set events '10046 trace name context forever ,level 12' ;

会话已更改。

SQL> select object_name from t where object_id=1000; --以索引访问表中数据

SQL> alter session set events '10046 trace name context off' ;

会话已更改。

SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Default Trace File
c:\app\xianzhu\diag\rdbms\orcl\orcl\trace\orcl_ora_12136_jujay.trc

c:\app\xianzhu\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_12136_jujay.trc orcl_ora_12136_jujay.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期三 12月 26 13:23:29 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

查看trace文件中的内容:
SQL ID: 0bjnptjy6ctk2
Plan Hash: 2928007915
select object_name 
from
 t where object_id=1000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        8      0.03       0.68         34         76          0          64
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.04       0.68         34         76          0          64

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92  

Rows     Row Source Operation
-------  ---------------------------------------------------
     32  TABLE ACCESS BY INDEX ROWID T (cr=38 pr=34 pw=0 time=0 us cost=35 size=960 card=32)
     32   INDEX RANGE SCAN I_T (cr=6 pr=2 pw=0 time=837 us cost=3 size=0 card=32)(object id 75191)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       9        0.00          0.00
   db file sequential read                        34        0.10          0.67
  SQL*Net message from client                     9       19.22         36.71
********************************************************************************
如上所示,df file sequential read事件出现

buffer busy waits  & cache buffer chain

和buffer相关的等待事件是因为热块,根据数据块类型,解决方法不同:
*表数据块:考虑将数据分布在更多的数据块上,减少数据块被多用户同时访问的频率。
*索引数据块:当一个表的主键用sequence生成键值时,索引最右边的叶子节点容易成为热块,可以考虑使用反向索引。


free buffer —— 等待空闲缓冲区

当把数据块从磁盘读入内存时,如果内存已无空闲空间,则会产生该等待事件,可能的原因有:
1)Data buffer太小,需增大Data buffer
2)内存中脏数据太多,需增加DBWR进程数

buffer busy —— 缓冲区忙等待

当用户频繁地读取或修改同样的数据块时,就会出现该等待事件,如果该等待事件很长,表示数据库中存在热块。

log file sync —— 日志文件同步等待

当发出一个commit命令时,LGWR进程会将相应的redo log从log buffer写到磁盘上,此时就会产生该事件。
如果存在大量该事件,则应检查存在用户在频繁的提交,多出现在OLTP系统中。

log buffer space —— 日志缓冲区空间等待

如果log buffer无空闲空间可用,则会出现该等待事件。
一般情况下,该等待事件出现的可能性很少,除非log buffer过小或I/O太差。

log file sequential read —— 日志文件顺序读等待

当对日志文件进行读时(如日志归档),会出现该等待事件。

log file switch —— 日志切换等待

该事件又分为两种类型:
1)checkpoint incomplete
日志切换时,如果下一个日志对应的某些脏数据库还未被写到磁盘上,则必须等待checkpoint完成,减少该等待事件的方法有:增大日志文件大小、增加日志文件组或提高DBWR的性能。
2)archiving needed
如果数据库处于归档模式,当日志切换时,如果下一个日志还为被归档,则必须等到该日志被归档后,才能完成切换。产生这种等待事件的原因一般都是arch进程死掉或太慢。

enqueue —— 锁等待

enqueue可以等同于锁,如果出现长时间的该等待,说明数据库中出现阻塞和锁。

和latch争用相关的等待事件

*library cache
*library cache pin
*library cache pin allocation
*library cache load lock
和library cache相关的等待事件可以断定是共享池出现的问题,基本上是由不良SQL语句导致,如没有使用绑定变量等。


原文链接:http://blog.csdn.net/u010415792/article/details/8992593
加载中
返回顶部
顶部