记录一次索引导致的db file sequential read等待事件问题

最新推荐文章于 2021-04-09 08:42:20 发布

ckumtg3452

最新推荐文章于 2021-04-09 08:42:20 发布

阅读量211

点赞数

文章标签：数据库运维

管理会计-ofsaa批处理时，作成本分摊的时候执行时间要很久，查看数据库等待事件为db file sequential read，简单描述一下此等待事件，如下：
Wait Event: db file sequential read
Wait Class: User I/O

Data blocks are being read one at a time. Excessive time on this wait often signals unselective indexes or poor join ordering of tables

Possible solutions are:
Use faster disks
Distribute IO load better
Increase the buffer cache
Improve explain plan efficiency by tuning SQL statements
Adjust PGA AGGREGATE TARGET parameter to reduce waits on sequential reads

通过以上描述可看，问题可能出现在disk io或是内存不够，或者是sql效率问题，而实际上问题根本不是这么回事
通过session监控，正在执行的sql如下：
MERGE INTO ODM_PA_BAL_INT TARGET
USING ( SELECT SUM (IVW_SOURCE.IVW_TARGET_AMOUNT) TARGET_AMOUNT,
IDENTITY_CODE,
ID_NUMBER
FROM (SELECT (Src.SOURCE_AMOUNT * Drv.drv_factor)
IVW_TARGET_AMOUNT,
Drv.IDENTITY_CODE,
Drv.ID_NUMBER
FROM ( SELECT SUM (
DECODE (a.year_s,
2014, a.month_09,
0)
* .5)
SOURCE_AMOUNT,
ORG_UNIT_ID
FROM LEDGER_STAT a
WHERE a.FINANCIAL_ELEM_ID IN
(SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num =
200160
AND (OFSA_IDT_ROLLUP.LEAF_NODE IN
(SELECT REV_HIERARCHY_FILTER.LEAF_NODE
FROM REV_HIERARCHY_FILTER
WHERE REV_HIERARCHY_FILTER.sys_id_num =
200276)))
AND a.ORG_UNIT_ID IN
(SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num =
200165
AND (OFSA_IDT_ROLLUP.NODE_01 IN
(SELECT REV_HIERARCHY_FILTER.LEAF_NODE
FROM REV_HIERARCHY_FILTER
WHERE REV_HIERARCHY_FILTER.sys_id_num =
200250
AND REV_HIERARCHY_FILTER.hierarchy_level =
2)))
AND a.GL_ACCOUNT_ID IN
(SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE (node_01 = 660101)
AND sys_id_num = 200164)
AND a.PRODUCT_ID = 0
AND a.BRANCH_ID IN
(SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num =
200162
AND (OFSA_IDT_ROLLUP.LEAF_NODE IN
(SELECT REV_HIERARCHY_FILTER.LEAF_NODE
FROM REV_HIERARCHY_FILTER
WHERE REV_HIERARCHY_FILTER.sys_id_num =
200241)))
AND a.SOURCE_ORG_ID IN
(SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num =
200163
AND (OFSA_IDT_ROLLUP.LEAF_NODE IN
(SELECT REV_HIERARCHY_FILTER.LEAF_NODE
FROM REV_HIERARCHY_FILTER
WHERE REV_HIERARCHY_FILTER.sys_id_num =
200192)))
AND (a.identity_code) IN
(SELECT DISTINCT
a.parent_identity_code
FROM FSI_M_DATA_IDENTITY_DETAIL a
WHERE a.identity_code IN
(13958)
AND a.as_of_date =
'09/30/2014'
AND a.src_drv_type = 'S')
AND a.year_s IN (2014)
AND a.CONSOLIDATION_CD = 100
GROUP BY ORG_UNIT_ID) Src
JOIN
(SELECT (ColVal / mTotal) drv_factor,
ORG_UNIT_ID,
IDENTITY_CODE,
ID_NUMBER
FROM (SELECT a.IDENTITY_CODE,
a.ID_NUMBER,
(NET_REVNU_RMB) ColVal,
(SUM (NET_REVNU_RMB)
OVER (PARTITION BY ORG_UNIT_ID))
mTotal,
ORG_UNIT_ID
FROM ODM_PA_BAL_INT a
WHERE a.as_of_date = '09/30/2014'
AND (ORG_UNIT_ID) IN
( SELECT ORG_UNIT_ID
FROM LEDGER_STAT a
WHERE a.FINANCIAL_ELEM_ID IN
(SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num =200163
AND (OFSA_IDT_ROLLUP.LEAF_NODE IN
(SELECT REV_HIERARCHY_FILTER.LEAF_NODE
FROM REV_HIERARCHY_FILTER
WHERE REV_HIERARCHY_FILTER.sys_id_num =
200192)))
AND (a.identity_code) IN
(SELECT DISTINCT
a.parent_identity_code
FROM FSI_M_DATA_IDENTITY_DETAIL a
WHERE a.identity_code IN
(13958)
AND a.as_of_date =
to_date('20140930', 'yyyymmdd')
AND a.src_drv_type =
'S')
AND a.year_s IN
(2014)
AND a.CONSOLIDATION_CD =
100
GROUP BY ORG_UNIT_ID))
WHERE mTotal <> 0) Drv
ON (Src.ORG_UNIT_ID = Drv.ORG_UNIT_ID)) IVW_SOURCE
GROUP BY IDENTITY_CODE, ID_NUMBER) SOURCE
ON ( SOURCE.IDENTITY_CODE = TARGET.IDENTITY_CODE
AND SOURCE.ID_NUMBER = TARGET.ID_NUMBER
AND TARGET.AS_OF_DATE = to_date('20140930', 'yyyymmdd'))
WHEN MATCHED
THEN
UPDATE SET
IDENTITY_CODE_CHG = 13958.000000,
TARGET.COST_HQ_BA_ZJ =
NVL (TARGET.COST_HQ_BA_ZJ, 0) + (SOURCE.TARGET_AMOUNT * 1)

执行计划较为复杂，总之走的基本上都是索引，
接下来开始诊断disk io和内存，
disk io通过测试发现，hda1，hda2和dm-0每秒读写都能达到30000，而ofsaa时，io走的很低也就达到50-60，说明io这里并没有瓶颈，

内存监控通过生成的awr报告发现，buffer cache内存命中率基本达到100%，说明内存也不是瓶颈，

到这，问题卡住了，也想不通了为什么，去alert告警日志看下也没什么问题，awr报告的等待事件和dbtime如下

由于本人是个半吊子dba，也不善于做trace，所以问题到这就卡住了。静下心来想了想上面这条sql语句，执行计划基本走的都是索引，db file sequential read即数据文件顺序读基本上是在读索引，而索引是存储在磁盘上，读索引的时候会加载在内存中，索引再去找表中的数据，现在问题既然卡在db file sequential read，那么不如去掉索引，看看是否会有变化，杀掉session，删掉表上的索引，重新跑ofsaa，果然不再出现此等待事件，单个语句之前几个小时现在基本上几分钟处理完。
总结：问题虽然解决，但是有些地方还是没有了解透彻，该表上有很多索引，其中有一个唯一索引，包括19个列（一共45列，其中24列是数据列），个人觉得很可能是出现在该索引上，很可能是读的时候争用严重导致的，还需要更多时间去研究下。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23382569/viewspace-1393321/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23382569/viewspace-1393321/