Performance problems caused by parallel partitioning Indexes

Problem symptom; Production Environment Reports ORA-17144 = Statement handle not executed

Then I capture the SQL statement and run it again. The execution plan is as follows:

----------------------------------------------------------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 |-------------------------------------------------------------------------------------------------------------------------------

This execution plan was very normal and returned results within Ms. So I caught the AWR when the problem occurred and found that the SQL statement ran for 2 minutes, so I guess the execution plan is different from the execution plan currently running. Then SQL _id captures the execution plan that is currently running as follows:

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 uleftjoin t2 r on u.orderform_flow_no = r.serialnumber     WHEREu.create_time >= to_date(:1,‘yyyy-mm-dd hh24:mi:ss‘)   andu.create_time < to_date(:2,‘yyyy-mm-dd hh24:mi:ss‘)  andr.service_id = ‘unicomhb‘    and r.status = 2Plan 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 |     |    |    | | |    |      |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sure enough, the above is all parallel scanning, here do not need to tangle the parallel why will lead to ora-17144 errors, and then I immediately thought of using SQL profile will be the execution plan fixed, but absolutely not reasonable, as to why we need to find the problem in parallel,

So I queried the degree of parallelism between the table and the table index, and found that parallel is enabled on the partition index.

Alter index index_name noparallel: After the parallel function is disabled, the service returns to normal.

This article is from the "Focus on Oracle performance tuning" blog!

Performance problems caused by parallel partitioning Indexes