在一个风和日丽阳光明媚的早上，刚到公司屁股还没有坐下，就接到报告：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 BWHERE A.NO_ID=B.NO_ID AND B.DONETIMEB.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，如果要优化，由于第五步返回结果较少，可以建立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 COUNT(*)---------- 14539090SQL> 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_ID1FROM MM_USER_INFO A, MM_ATOBSTESTSTATE_INFO B WHERE A.NO_ID = B.NO_ID AND B.DONETIMEAND 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 https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/index.html里面的Advanced Partition Pruning Techniques有关于 PARTITION RANGE SUBQUERY的内容。
