db file sequential read等待事件

 今天是2014-01-08，继续完成等待事件系列。

什么是：db file sequential read：？

简单说，就是oracle要读取单块数据，其他会话存在等待，有三个参数p1，是要读的文件，p2是block#,开始读取的数据块号，p3是blocks，一般p3为单块，但是如果是多块那么一般发生在从temporary segment中读的。

该类等待事件的出现主要是由于执行对索引，回滚（undo）段，和表（当借助rowid来访问），控制文件和数据文件头的单块读操作SQL语句（用户和递归）引起的，该事件的产生要么表的连接顺序有问题，要么索引使用不当导致。

如何解决呢：

1、优化sql，主要是看执行计划，能不走全表的就不走全表（但是有时候全表可能比索引更快），能走index scan的就不走table access byｉｎｄｅｘ　ｒｏｗｉｄ．

２、增加ｆｉｌｅ的ｉ／０速率，一般对于硬件存储设备而言，数据文件所在的磁盘采用哪种ｒａｉｄ也是很显著

３、增加buffer cache大小，使其数据在内存中得到，但是到了10G、11g 启用了asmm和amm那么buffer cache是会动的，或者考虑将相关表存放到keep 池中，如果是11g可以考虑result cache。

4、可以将数据放到非标准块中（大块存多行数据），避免i/0

5、采用索引组织表，减少i/o

6、采用并行查询执行（占用一定内存和cpu，需要依据主机负载判断）

7、可以尝试采用分区表等。

案例分析：

1、获得sql信息如下：

SELECTcount(1)

 FROMWF_DEAL_COMMON_MAIN_T  M,

      WFC_ROOT_SUB_RELATION_T R,

      RT_WORKITEMINST         W

 WHERE M.F_PROCINST_ID = R.ROOT_ID

  AND R.SUB_ID =W.PROC_INSTANCE_ID

  AND W.current_statein (1,2)

  AND W.USER_ID = :1

  AND W.OVERDUED = :2

 

查看执行计划如下：

>SELECT count(1)

16:02:35   2   FROM WF_DEAL_COMMON_MAIN_T   M,

16:02:35   3        WFC_ROOT_SUB_RELATION_T R,

16:02:35   4        RT_WORKITEMINST         W

16:02:35   5  WHERE M.F_PROCINST_ID = R.ROOT_ID

16:02:35   6    AND R.SUB_ID = W.PROC_INSTANCE_ID

16:02:35   7    AND W.current_state in (1, 2)

16:02:36   8    AND W.USER_ID = 999

16:02:36   9    AND W.OVERDUED = 1

 

16:02:36  10  ;

Predicate Information (identified by operation id):

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

 

   4 -filter(("W"."CURRENT_STATE"=1 OR"W"."CURRENT_STATE"=2) AND"W"."OVERDUED"=1 AND

             TO_NUMBER("W"."USER_ID")=999)

   6 -access("R"."SUB_ID"="W"."PROC_INSTANCE_ID")

      filter("R"."SUB_ID"="W"."PROC_INSTANCE_ID")

   8 -access("M"."F_PROCINST_ID"="R"."ROOT_ID")

 

Note

-----

   - dynamic samplingused for this statement

 

index full scan，table access full。注意此时有隐式转换。

如果把变量换成字符串。执行计划如下：

>r

  1  SELECT count(1)

  2   FROM WF_DEAL_COMMON_MAIN_T   M,

  3           WFC_ROOT_SUB_RELATION_T R,

  4          RT_WORKITEMINST         W

  5     WHERE M.F_PROCINST_ID = R.ROOT_ID

  6      AND R.SUB_ID = W.PROC_INSTANCE_ID

  7      AND W.current_state in (1, 2)

  8      AND W.USER_ID ='999'

  9*     AND W.OVERDUED = 2

 

Predicate Information (identified by operation id):

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

 

   4 -filter(("W"."CURRENT_STATE"=1 OR"W"."CURRENT_STATE"=2) AND"W"."OVERDUED"=2)

   5 -access("W"."USER_ID"='640001312')

   7 -access("R"."SUB_ID"="W"."PROC_INSTANCE_ID")

   8 -access("M"."F_PROCINST_ID"="R"."ROOT_ID")

 

Note

-----

   - dynamicsampling used for this statement

 

总结：

从如上内容，可以分析得出：因为w.user_id为varchar 类型，当w.user_id赋值为数字则会产生index full scan和table access full，这主要是由于隐式转换导致，解决该办法一种是建立函数索引，另一种是在传送该值变量的时候进行转换。建议采用后者。