[Oracle] 常见的等待事件

文章由LinuxBoy分享于2019-03-30 05:03:12热评（576）

[Oracle] 常见的等待事件

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文件的内容：

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语句导致，如没有使用绑定变量等。

推荐文章：

[Oracle] 常见的等待事件