oracle 并行用索引,分区索引并行导致的性能问题

问题现象;生产环境报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_IDcku6z254k95z5, child number 0

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

select coalesce(sum(u.money),0) from t1 uleft

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 

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关闭了并行后，业务恢复正常。