等待事件 db file scattered read

 db file scattered read 等待事件，在生产环境之中，这个等待事件可能更为常见。 这个事件表明用户进程正在读数据到 Buffer Cache 中，等待直到物理 I/O调用返回。 DB File Scattered Read 发出离散读，将存储上连续的数据块离散的读入到多个不连续的内存位置。 Scattered Read 通常是多块读，在 Full Table Scan 或 Fast Full Scan 等访问方式下使用。

Scattered Read 代表 Full Scan，当执行 Full Scan 读取数据到 Buffer Cache 时，通常连续的数据在内存中的存储位置并不连续，所以这个等待被命名为 Scattered Read（ 离散读）。每次多块读读取的数据块数量受初始化参数            DB_FILE_MULTIBLOCK_READ_COUNT 限制。 图简要说明了 Scattered Read 的数据读取方式

从 V$EVENT_NAME 视图可以看到，该等待有 3 个参数，分别代表文件号、 起始数据块号、 数据块的数量：

 

SQL> select * from v$event_name where name='db file scattered read';

EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3

---------- ---------------------- ---------------------- ------------- ----

206 db file scattered read file# block# blocks

数据文件号、起始数据块号加上数据块的数量， 通过这些信息可以知道 Oracle Session 正在等待的对象文件等信息。 该等待可能和全表扫描（ Full Table Scan）或者快速全索引扫描（ Index Fast Full Scan） 的连续读取相关， 根据经验， 通常大量的 db file scattered read 等待可能意味着应用问题或者索引缺失。

在实际环境的诊断过程中， 可以通过 v$session_wait 视图发现 Session 的等待，再结合其他视图找到存在问题的 SQL 等根本原因，从而从根本上解决问题。

当这个等待事件比较显著时， 用户也可以结合 v$session_longops 动态性能视图来进行诊断，该视图中记录了长时间（运行时间超过 6 秒的）运行的事务，可能很多是全表扫描操作（不管怎样，这部分信息都是值得我们注意的），上一个案例就是通过 v$session_longops 快速发现了问题所在。

从 Oracle 9i 开始， Oracle 新增加了一个视图 V$SQL_PLAN 用于记录当前系统 Library Cache 中 SQL 语句的执行计划， 可以通过这个视图找到存在问题的 SQL 语句， 以下是在一个

生产系统中查询得到的结果：

SQL> @getplan

Enter value for waitevent: free buffer waits

old 15: AND b.event = '&waitevent')

new 15: AND b.event = 'free buffer waits')

HASH_VALUE CHILD_NUMBER OPERATION OBJECT COST KBYTES

---------- ------------ ---------------------------------------- -------------------

2838180055 0 INSERT STATEMENT CHOOSE Cost=41733 41733

2838180055 0 TABLE ACCESS FULL I_CM_POWER_TEMP 41733 1356468

进而可以通过 v$sql_text 视图获得这个问题 Session 正在执行的 SQL 语句：

SQL> select sid,event from v$session_wait;

SID EVENT

---------- ----------------------------------------------------------------

1 pmon timer

4 rdbms ipc message

7 rdbms ipc message

5 rdbms ipc message

8 rdbms ipc message

21 free buffer waits

49 free buffer waits

2 db file parallel write

 

3 db file parallel write

6 smon timer

……

16 rows selected.

 

SQL>@ GetSqlBySid

Enter value for sid: 49

old 5: where b.sid='&sid'

new 5: where b.sid='49'

SQL_TEXT

-----------------------------------------------------------------------

insert into i_cm_power_new(PNAME,YYS,SPHM,SJH,SENTTIME,NOTES,PLACE,RMK)

select PNAME,YYS,SPHM,SJH,SENTTIME,NOTES,PLACE,RMK FROM i_cm_power_temp

通过 V$SQL_PLAN 视图， 可以获得大量有用的信息，比如获得全表扫描的对象：

SQL> select distinct object_name,object_owner from v$sql_plan p

2 where p.operation='TABLE ACCESS' and p.options='FULL'

3 and object_owner = 'MKT';

OBJECT_NAME OBJECT_OWNER

--------------------------------------------------- -----------------

HD_TEMP MKT

I_CM_BILL MKT

I_CM_IVR_BUTTON MKT

……

TOOLS_HD MKT

TOOLS_HD_NEW MKT

TOOLS_HD_NEW_BAK MKT

TOOLS_IVRBLIST MKT

TOOLS_USER_CANCEL MKT

29 rows selected

或者获得全索引扫描对象：

SQL> select distinct object_name,object_owner from v$sql_plan p

2 where p.operation='INDEX' and p.options='FULL SCAN' ;

OBJECT_NAME OBJECT_OWNER

------------------------------ -----------------------------------

FK_ITEM_LEVEL_CODE AVATAR

FK_ITEM_SELLCNT_CODE AVATAR

FK_MYZZIM_CRTDATE AVATAR

I_SYSAUTH1 SYS

SYS_C008211 WLLM

进而可以通过 V$SQL_PLAN 和 V$SQLTEXT 联合，获得这些查询的 SQL 语句， 查找全

 

表扫描的 SQL 语句可以参考如下语句：

SELECT sql_text FROM v$sqltext t, v$sql_plan p

WHERE t.hash_value = p.hash_value AND p.operation = 'TABLE ACCESS' AND p.options = 'FULL'

ORDER BY p.hash_value, t.piece;

查找 Fast Full Index 扫描的 SQL 语句可以参考如下语句：

SELECT sql_text FROM v$sqltext t, v$sql_plan p

WHERE t.hash_value = p.hash_value AND p.operation = 'INDEX' AND p.options = 'FULL SCAN'

ORDER BY p.hash_value, t.piece;

这些信息对于发现数据库问题，优化数据库性能具有极强的指导意义。 本例中用到的 SQL

代码 getplan.sql 内容如下： :

SET linesize 120

COL operation format a55

COL cost format 99999

COL kbytes format 999999

COL object format a25

SELECT hash_value, child_number, LPAD (' ', 2 * DEPTH) || operation || ' ' || options

|| DECODE (ID, 0, SUBSTR (optimizer, 1, 6) || ' Cost=' || TO_CHAR (COST) ) operation,

object_name OBJECT, COST, ROUND (BYTES / 1024) kbytes

FROM v$sql_plan WHERE hash_value IN (

SELECT a.sql_hash_value FROM v$session a, v$session_wait b

WHERE a.SID = b.SID AND b.event = '&waitevent')

ORDER BY hash_value, child_number, ID;

在 Oracle 10g 中， Oracle 对等待事件进行了分类， db file scattered read 事件被归入 User I/O

一类：

SQL> select name,PARAMETER1 p1,PARAMETER2 p2,PARAMETER3 p3,

2 WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS

3 from v$event_name where name='db file scattered read';

NAME P1 P2 P3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS

------------------------- -------- -------- -------- ------------- ----------- ----------

db file scattered read file# block# blocks 1740759767 8 User I/O

 

 

下面是一条有用的SQL语句，在数据库层面查看db file等待事件的I/O是否有问题。

############################  IO 响应时间     ############################  

select to_char(b.begin_interval_time,'yyyy-mm-dd:hh24'),a.event_name,a.avg_time

from (

select event_name,snap_id,round(lag_time/lag_count/1000,2) avg_time from (

select event_name,snap_id,total_waits-lag(total_waits,1,total_waits) over(partition by event_name,instance_number order by snap_id) lag_count,

time_waited_micro-lag(time_waited_micro,1,time_waited_micro) over(partition by event_name,instance_number order by snap_id) lag_time

 from dba_hist_system_event where event_name in ('db file scattered read') <==========这个地方要检查db file sequential read 和 db file scattered read

and instance_number=1) where lag_count>0) a,dba_hist_snapshot b

where b.instance_number=1 <=============这个地方要检查实例1和实例2

and a.snap_id=b.snap_id

order by 2,1;