[Oracle] 常见的等待事件

Oracle 11g，在大表的全表扫描算法上有新的变化，根据表的大

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;

 

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;

 

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;

 

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