问题现象;生产环境报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关闭了并行后,业务恢复正常。
转载于:https://blog.51cto.com/5073392/1583937