PARTITION RANGE SUBQUERY 导致SQL执行效率降低的处理案例

  在一个风和日丽阳光明媚的早上，刚到公司屁股还没有坐下，就接到报告：XX系统一条SQL执行效率很低，赶紧上去经过一番犀利操作。SQL信息如下：

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dkusf44y9g1yv, child number 0
-------------------------------------
SELECT A.NO_ID,A.BILL_ID1 FROM MM_USER_INFO A, MM_ATOBSTESTSTATE_INFO B
WHERE A.NO_ID=B.NO_ID AND B.DONETIME < ADD_MONTHS(SYSDATE,-6) AND
B.RUN_IDNO='W' AND A.KKK_SERVICE_ID='1111'

Plan hash value: 3801554394

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |       |       |       | 64561 (100)|          |       |       |
|*  1 |  HASH JOIN                          |                        |   255K|    11M|  9744K| 64561   (2)| 00:12:55 |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| MM_USER_INFO           |   255K|  6741K|       | 26342   (1)| 00:05:17 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | IDX_MMUSER_SERVICEID   |   255K|       |       |   649   (1)| 00:00:08 |       |       |
|   4 |   PARTITION RANGE ALL               |                        |   475K|  8821K|       | 37038   (2)| 00:07:25 |     1 |    17 |
|*  5 |    TABLE ACCESS FULL                | MM_ATOBSTESTSTATE_INFO |   475K|  8821K|       | 37038   (2)| 00:07:25 |     1 |    17 |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."NO_ID"="B"."NO_ID")
   3 - access("A"."KKK_SERVICE_ID"='1111')
   5 - filter(("B"."RUN_IDNO"='W' AND "B"."DONETIME" 26 rows selected.

  从执行计划看出，这条语句走HASH JOIN,主要慢在ID=2和ID=5。我们要知道，这里的执行计划是指标都是估算的，估算的东西就是可能不准确，特别是执行计划有问题的时候，那么需要分析问题的根源，是可以通过Predicate Information里的条件去计算真实的Rows，然后分析是不是统计信息不准，或用gather_plan_statistics或alter session set statistics_level=all去看A-Rows,A-Time等信息，从而准确判断慢在哪一步然后进一步分析。那么查看真实计划看看：

Plan hash value: 3801554394

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |      1 |        |    842 |00:01:18.55 |     670K|    570K|       |       |          |
|*  1 |  HASH JOIN                          |                        |      1 |    255K|    842 |00:01:18.55 |     670K|    570K|   130M|    13M|  126M (0)|
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| UR_USER_INFO           |      1 |    255K|   2113K|00:00:41.52 |     299K|    200K|       |       |          |
|*  3 |    INDEX RANGE SCAN                 | IDX_USERINFO_SERV_ID   |      1 |    255K|   2113K|00:00:07.14 |   12844 |  12843 |       |       |          |
|   4 |   PARTITION RANGE ALL               |                        |      1 |    475K|    842 |00:00:34.24 |     370K|    370K|       |       |          |
|*  5 |    TABLE ACCESS FULL                | UR_CRMTOBOSSSTATE_INFO |     17 |    475K|    842 |00:00:34.23 |     370K|    370K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID_NO"="B"."ID_NO")
   3 - access("A"."MASTER_SERV_ID"='2063')
   5 - filter(("B"."RUN_CODE"='W' AND "B"."OP_TIME"

从执行计划看出，这条语句走HASH JOIN，主要慢在ID=2和ID=5，如果要优化，由于第五步返回结果较少，可以建立RUN_IDNO,DONETIME的分区索引。索引创建如下：

CREATE INDEX "OPERTIADM"."IDX1_MM_ATOBSTESTSTATE_INFO" ON "OPERTIADM"."MM_ATOBSTESTSTATE_INFO" ("RUN_IDNO","DONETIME")
 LOCAL TABLESPACE "TBS_IDX_TKO" parallel 16;
 alter index "OPERTIADM"."IDX1_MM_ATOBSTESTSTATE_INFO" noparallel;

 
执行计划如下，竟然没有改变：

Execution Plan
----------------------------------------------------------
Plan hash value: 3801554394

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |   255K|    11M|       | 64561   (2)| 00:12:55 |       |       |
|*  1 |  HASH JOIN                          |                        |   255K|    11M|  9744K| 64561   (2)| 00:12:55 |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| MM_USER_INFO           |   255K|  6741K|       | 26342   (1)| 00:05:17 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | IDX_MMUSER_SERVICEID   |   255K|       |       |   649   (1)| 00:00:08 |       |       |
|   4 |   PARTITION RANGE ALL               |                        |   475K|  8821K|       | 37038   (2)| 00:07:25 |     1 |    17 |
|*  5 |    TABLE ACCESS FULL                | MM_ATOBSTESTSTATE_INFO |   475K|  8821K|       | 37038   (2)| 00:07:25 |     1 |    17 |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."NO_ID"="B"."NO_ID")
   3 - access("A"."KKK_SERVICE_ID"='1111')
   5 - filter("B"."RUN_IDNO"='W' AND "B"."DONETIME"

 
  执行计划未变，主要是ID=5的cardinality估算不准确。

SQL> select count(*) from MM_ATOBSTESTSTATE_INFO
  2  where DONETIME < ADD_MONTHS(SYSDATE, -6);

  COUNT(*)
----------
  14539090

SQL> select/*+parallel(16)*/ count(*) from MM_ATOBSTESTSTATE_INFO
  2  where RUN_IDNO = 'W';

  COUNT(*)
----------
     59675

 

既然cardinality不准确，那么就收集统计信息，收集完统计信息执行计划如下：

Plan hash value: 1403561594

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|*  1 |  TABLE ACCESS BY LOCAL INDEX ROWID   | MM_USER_INFO                |      1 |      1 |    842 |00:00:10.81 |     302K|      5 |
|   2 |   NESTED LOOPS                       |                             |      1 |   1349K|   1685 |00:00:10.81 |     301K|      5 |
|   3 |    PARTITION RANGE SUBQUERY          |                             |      1 |   1349K|    842 |00:00:10.80 |     300K|      5 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| MM_ATOBSTESTSTATE_INFO      |      6 |   1349K|    842 |00:00:00.01 |     842 |      0 |
|*  5 |      INDEX RANGE SCAN                | IDX1_MM_ATOBSTESTSTATE_INFO |      6 |   1349K|    842 |00:00:00.01 |      77 |      0 |
|   6 |    PARTITION RANGE ITERATOR          |                             |    842 |      1 |    842 |00:00:00.01 |    1741 |      0 |
|*  7 |     INDEX UNIQUE SCAN                | PK_MM_USER_INFO             |    842 |      1 |    842 |00:00:00.01 |    1741 |      0 |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"."KKK_SERVICE_ID"='1111')
   5 - access("B"."RUN_IDNO"='W' AND "B"."DONETIME"    7 - access("A"."NO_ID"="B"."NO_ID")

 

  现在走NESTED LOOPS了，但是还是需要10s多，效率没有啥提升，通过执行计划可以看出主要慢在 PARTITION RANGE SUBQUERY上，那么只能关闭这个功能对应的参数，当然，最好的是使用SQL PROFILE绑定到如下语句：

SELECT/*+leading(b) use_nl(a) index(b IDX1_MM_ATOBSTESTSTATE_INFO)
opt_param('_subquery_pruning_enabled' 'false')*/ A.NO_ID, A.BILL_ID1
FROM MM_USER_INFO A, MM_ATOBSTESTSTATE_INFO B  WHERE A.NO_ID = B.NO_ID
  AND B.DONETIME < ADD_MONTHS(SYSDATE, -6)    AND B.RUN_IDNO = 'W'
AND A.KKK_SERVICE_ID = '1111'

  绑定后的执行计划如下：

Plan hash value: 4290111086

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |      0 |        |      0 |00:00:00.01 |       0 |
|*  1 |  TABLE ACCESS BY LOCAL INDEX ROWID   | MM_USER_INFO                |      1 |      1 |    842 |00:00:00.02 |    3436 |
|   2 |   NESTED LOOPS                       |                             |      1 |   1349K|   1685 |00:00:00.02 |    2594 |
|   3 |    PARTITION RANGE ALL               |                             |      1 |   1349K|    842 |00:00:00.01 |     853 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| MM_ATOBSTESTSTATE_INFO      |     17 |   1349K|    842 |00:00:00.01 |     853 |
|*  5 |      INDEX RANGE SCAN                | IDX1_MM_ATOBSTESTSTATE_INFO |     17 |   1349K|    842 |00:00:00.01 |      88 |
|   6 |    PARTITION RANGE ITERATOR          |                             |    842 |      1 |    842 |00:00:00.01 |    1741 |
|*  7 |     INDEX UNIQUE SCAN                | PK_MM_USER_INFO             |    842 |      1 |    842 |00:00:00.01 |    1741 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"."KKK_SERVICE_ID"='1111')
   5 - access("B"."RUN_IDNO"='W' AND "B"."DONETIME"    7 - access("A"."NO_ID"="B"."NO_ID")

  非常完美，执行效率由原来的10s多降低为0.01s,效率提升上千倍。通过分析真实的执行计划可以快速找到问题的ROOT CAUSE，从而解决之。这里通过分析得知需要建立索引，但是建立索引后执行计划未变，发现是cardinality估算不准，那么收集统计信息，收集完毕后，走索引和NL，但是却出现了PARTITION RANGE SUBQUERY影响效率，那么只能在语句级先关闭这个参数，通过SQL PROFILE绑定从而达到解决问题的目的。

附：关于partition的内容可以参考VLDB and Partitioning Guide 里面的Advanced Partition Pruning Techniques有关于 PARTITION RANGE SUBQUERY的内容。