|
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" |
从执行计划看出，这条语句走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 |
|
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" |
|
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" |
非常完美，执行效率由原来的10s多降低为0.01s,效率提升上千倍。通过分析真实的执行计划可以快速找到问题的ROOT CAUSE，从而解决之。这里通过分析得知需要建立索引，但是建立索引后执行计划未变，发现是cardinality估算不准，那么收集统计信息，收集完毕后，走索引和NL，但是却出现了PARTITION RANGE SUBQUERY影响效率，那么只能在语句级先关闭这个参数，通过SQL PROFILE绑定从而达到解决问题的目的。
附：关于partition的内容可以参考VLDB and Partitioning Guide 里面的Advanced Partition Pruning Techniques有关于 PARTITION RANGE SUBQUERY的内容。