首页 > 代码库 > 分区索引并行导致的性能问题
分区索引并行导致的性能问题
问题现象;生产环境报ORA-17144=statement handle not executed
然后我把sql抓出来手工运行一遍执行计划如下：
---------------------------------------------------------- Plan hash value: 644608605 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 75 | 120K (1)| 00:24:10 | | | | 1 | SORT AGGREGATE | | 1 | 75 | | | | | | 2 | NESTED LOOPS | | 58896 | 4313K| 120K (1)| 00:24:10 | | | | 3 | NESTED LOOPS | | 58896 | 4313K| 120K (1)| 00:24:10 | | | | 4 | PARTITION RANGE SINGLE | | 58896 | 2300K| 2984 (1)| 00:00:36 | 12 | 12 | | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| t1 | 58896 | 2300K| 2984 (1)| 00:00:36 | 12 | 12 | |* 6 | INDEX RANGE SCAN | idx_1 | 58896 | | 2984 (1)| 00:00:36 | 12 | 12 | |* 7 | INDEX UNIQUE SCAN | t2_UNIQUE1 | 1 | | 1 (0)| 00:00:01 | | | |* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID | t2 | 1 | 35 | 2 (0)| 00:00:01 | ROWID | ROWID | -------------------------------------------------------------------------------------------------------------------------------
这个执行计划非常正常，400ms返回结果，于是我抓了出问题时的awr，发现这个sql跑了2分钟，于是我猜测执行计划和当前运行的执行计划不一致，然后sql_id 抓取了当时运行的执行计划如下
SQL> set pages 200 lines 200; SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => ‘1hqcmrpa790c3‘, cursor_child_no => 0, 4 format => ‘ALL ALLSTATS LAST NOTE ADVANCED -projection‘)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cku6z254k95z5, child number 0 ------------------------------------- select coalesce(sum(u.money),0) from t1 u left join t2 r on u.orderform_flow_no = r.serialnumber WHERE u.create_time >= to_date(:1,‘yyyy-mm-dd hh24:mi:ss‘) and u.create_time < to_date(:2,‘yyyy-mm-dd hh24:mi:ss‘) and r.service_id = ‘unicomhb‘ and r.status = 2 Plan hash value: 28991375 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | OMem | 1Mem | Used-Mem | Used-Tmp| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 100K(100)| | | | | | | | | | | | 1 | SORT AGGREGATE | | 1 | 75 | | | | | | | | | | | | |* 2 | PX COORDINATOR | | | | | | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 75 | | | | | Q1,02 | P->S | QC (RAND) | | | | | | 4 | SORT AGGREGATE | | 1 | 75 | | | | | Q1,02 | PCWP | | | | | | |* 5 | FILTER | | | | | | | | Q1,02 | PCWC | | | | | | |* 6 | HASH JOIN | | 87509 | 6409K| 100K (1)| 00:20:12 | | | Q1,02 | PCWP | | 1740K| 1638K| 2076K (0)| | | 7 | PX RECEIVE | | 87509 | 3418K| 127 (0)| 00:00:02 | | | Q1,02 | PCWP | | | | | | | 8 | PX SEND HASH | :TQ10001 | 87509 | 3418K| 127 (0)| 00:00:02 | | | Q1,01 | P->P | HASH | | | | | | 9 | PX PARTITION RANGE ITERATOR | | 87509 | 3418K| 127 (0)| 00:00:02 | KEY | KEY | Q1,01 | PCWC | | | | | | | 10 | TABLE ACCESS BY LOCAL INDEX ROWID| t1 | 87509 | 3418K| 127 (0)| 00:00:02 | KEY | KEY | Q1,01 | PCWP | | | | | | |* 11 | INDEX RANGE SCAN | idx_1 | 87509 | | 127 (0)| 00:00:02 | KEY | KEY | Q1,01 | PCWP | | | | | | | 12 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | | 126M| 3809K| 97M (0)| 113K| | 13 | PX RECEIVE | | 9157K| 305M| 100K (1)| 00:20:10 | | | Q1,02 | PCWP | | | | | | | 14 | PX SEND HASH | :TQ10000 | 9157K| 305M| 100K (1)| 00:20:10 | | | | S->P | HASH | | | | | | 15 | PARTITION RANGE ALL | | 9157K| 305M| 100K (1)| 00:20:10 | 1 | 19 | | | | | | | | |* 16 | TABLE ACCESS FULL | t2 | 9157K| 305M| 100K (1)| 00:20:10 | 1 | 19 | | | | | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
果不其然，上面全是并行扫描，这里也不用纠结这个并行为什么会导致ora-17144错误，然后我立马想到用sql profile 将执行计划固定住，但是绝对不太合理，至于为什么并行还要找到问题说在，
于是我查询了表和该表索引的并行度，发现分区index上开启了并行，问题找到了，通过
alter index index_name noparallel关闭了并行后，业务恢复正常。
本文出自 “专注于Oracle性能调优” 博客，谢绝转载！
分区索引并行导致的性能问题
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。