这两天bi迁移后,有个进程一直很慢,仔细检查了迁移前后的表结构和执行计划都没有发现问题。分别对新旧存储过程进行跟踪,发现了很奇怪的现象,两个insert语句的执行计划一样,但io差别相当大。
fast:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 75.08 231.51 68712 77406 91973 2145639
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 75.08 231.52 68712 77406 91973 2145639
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 144 (BI_RW) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
0 MERGE JOIN (OUTER)
0 MERGE JOIN (OUTER)
0 MERGE JOIN (OUTER)
0 MERGE JOIN (OUTER)
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_MIDINFO2_D'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_ZH_NOW_D'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_TC_VOICE_D'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_TC_SCORE_D'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_TC_DATA_D'
slow:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3025.39 3054.78 68375 302289986 363629235 2145625
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3025.39 3054.78 68375 302289986 363629235 2145625
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (BI_RW) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
0 MERGE JOIN (OUTER)
0 MERGE JOIN (OUTER)
0 MERGE JOIN (OUTER)
0 MERGE JOIN (OUTER)
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_MIDINFO2_D'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_ZH_NOW_D'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_TC_VOICE_D'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_TC_SCORE_D'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FW_DC_TC_DATA_D'
几张表在新旧系统的数据量以及高水位都差不多,io竟然差这么大。在insert之前,该存储过程对要insert的表做了一个很大的delete操作,而且没有commit,从trace文件上看,delete的执行效率倒是差不多。进行了多个地方的比较,发现除了新系统是采用了ASSM管理方式,老系统是手动的,没有其它明显的不同。将存储过程的语句分别单独执行,效率也都一样,可delete和insert连续执行就出现问题。于是在delete后面先做了一次commit,结果速度一下上来了,看来这不是一个正常的优化问题了。咨询了oracle,果不其然又是个bug(最近怎么老遇到bug-_-)
到metalink查询bug4475314,问题说明和现象跟我这里遇到的一样,这个问题在9.2.0.8,10.2.0.3和11g得以解决,或者用对应的小patch也可以,当然delete之后先提交在insert也可以避免这个问题,不过要注意事务一致性的问题。
阅读(3306) | 评论(0) | 转发(0) |