_optimizer_null_aware_antijoin引发的SQL性能问题
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
前几天某客户联系我说之前我们进行存储迁移的系统，有个SQL跑的极慢，根本跑不出来结果。通过VPN登录看了下，SQL确认跑的很慢。开始我很难理解，我们仅仅是进行了存储迁移，数据库基本上没动，为什么会有SQL性能问题呢？ 我们先来看看有问题的SQL：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
SYS@rptdb1> set autot traceonly exp SYS@rptdb1> select a.*,b.rate TAX_RATE,round(a.charge*b.rate/(1+b.rate),0) tax,a.charge-round((a.charge*b.rate/(1+b.rate)),0) charge_flh,1 flag,b.tax_rule_id 2 from statrpt.rpt_offer_rate b,statrpt.tmp_item_aggr_ex_691 a 3 where a.acct_item_type_id = b.acct_item_type_id 4 and a.offer_cd =b.offer_ID 5 union all 6 SYS@rptdb1> select a.*,b.rate TAX_RATE,round(a.charge*b.rate/(1+b.rate),0) tax,a.charge-ROUND((a.charge*b.rate/(1+b.rate)),0) charge_flh,2,b.tax_rule_id 2 from statrpt.rpt_product_rate b,statrpt.tmp_item_aggr_ex_691 a 3 where a.acct_item_type_id = b.acct_item_type_id 4 and a.product_id=b.product_id 5 and (a.acct_item_type_id,a.offer_cd) not in(select acct_item_type_id,offer_id from statrpt.rpt_offer_rate) 6 union all 7 SYS@rptdb1> select a.*,b.rate TAX_RATE,round(a.charge*b.rate/(1+b.rate),0) tax,a.charge-round((a.charge*b.rate/(1+b.rate)),0) charge_flh,3,b.tax_rule_id 2 from statrpt.rpt_zm_rate b,statrpt.tmp_item_aggr_ex_691 a 3 where a.acct_item_type_id = b.acct_item_type_id 4 and (a.acct_item_type_id,a.offer_cd)not in(select acct_item_type_id,offer_id from statrpt.rpt_offer_rate ) 5 and (a.acct_item_type_id,a.product_id) not in(select acct_item_type_id,product_id from statrpt.rpt_product_rate ) 6 union all 7 select a.*,b.rate TAX_RATE,round(a.charge*b.rate/(1+b.rate),0) tax,a.charge-round((a.charge*b.rate/(1+b.rate)),0) charge_flh,4,b.tax_rule_id 8 from statrpt.tmp_zm_only_rate b,statrpt.tmp_item_aggr_ex_691 a 9 where a.acct_item_type_id = b.acct_item_type_id 10 and (a.acct_item_type_id,a.offer_cd)not in(select acct_item_type_id,offer_id from statrpt.rpt_offer_rate ) 11 and (a.acct_item_type_id,a.product_id) not in(select acct_item_type_id,product_id from statrpt.rpt_product_rate ) 12 and (a.acct_item_type_id) not in(select acct_item_type_id from statrpt.rpt_zm_rate ) 13 / Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1624413711 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6983K| 765M| 563M (51)|999:59:59 | | | | | 1 | UNION-ALL | | | | | | | | | |* 2 | FILTER | | | | | | | | | | 3 | PX COORDINATOR | | | | | | | | | | 4 | PX SEND QC (RANDOM) | :TQ60001 | 3494K| 383M| 1050 (1)| 00:00:13 | Q6,01 | P->S | QC (RAND) | |* 5 | HASH JOIN | | 3494K| 383M| 1050 (1)| 00:00:13 | Q6,01 | PCWP | | | 6 | PX RECEIVE | | 1034 | 14476 | 3 (0)| 00:00:01 | Q6,01 | PCWP | | | 7 | PX SEND BROADCAST | :TQ60000 | 1034 | 14476 | 3 (0)| 00:00:01 | Q6,00 | P->P | BROADCAST | | 8 | PX BLOCK ITERATOR | | 1034 | 14476 | 3 (0)| 00:00:01 | Q6,00 | PCWC | | | 9 | TABLE ACCESS FULL| RPT_ZM_RATE | 1034 | 14476 | 3 (0)| 00:00:01 | Q6,00 | PCWP | | | 10 | PX BLOCK ITERATOR | | 3494K| 336M| 1046 (1)| 00:00:13 | Q6,01 | PCWC | | | 11 | TABLE ACCESS FULL | TMP_ITEM_AGGR_EX_691 | 3494K| 336M| 1046 (1)| 00:00:13 | Q6,01 | PCWP | | | 12 | PX COORDINATOR | | | | | | | | | | 13 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 9 | 8 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 14 | PX BLOCK ITERATOR | | 1 | 9 | 8 (0)| 00:00:01 | Q1,00 | PCWC | | |* 15 | TABLE ACCESS FULL | RPT_OFFER_RATE | 1 | 9 | 8 (0)| 00:00:01 | Q1,00 | PCWP | | | 16 | PX COORDINATOR | | | | | | | | | | 17 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 9 | 96 (2)| 00:00:02 | Q2,00 | P->S | QC (RAND) | | 18 | PX BLOCK ITERATOR | | 1 | 9 | 96 (2)| 00:00:02 | Q2,00 | PCWC | | |* 19 | TABLE ACCESS FULL | RPT_PRODUCT_RATE | 1 | 9 | 96 (2)| 00:00:02 | Q2,00 | PCWP | | |* 20 | FILTER | | | | | | | | | | 21 | PX COORDINATOR | | | | | | | | | | 22 | PX SEND QC (RANDOM) | :TQ70001 | 3494K| 383M| 1050 (1)| 00:00:13 | Q7,01 | P->S | QC (RAND) | |* 23 | HASH JOIN | | 3494K| 383M| 1050 (1)| 00:00:13 | Q7,01 | PCWP | | | 24 | PX RECEIVE | | 6053 | 84742 | 3 (0)| 00:00:01 | Q7,01 | PCWP | | | 25 | PX SEND BROADCAST | :TQ70000 | 6053 | 84742 | 3 (0)| 00:00:01 | Q7,00 | P->P | BROADCAST | | 26 | PX BLOCK ITERATOR | | 6053 | 84742 | 3 (0)| 00:00:01 | Q7,00 | PCWC | | | 27 | TABLE ACCESS FULL| TMP_ZM_ONLY_RATE | 6053 | 84742 | 3 (0)| 00:00:01 | Q7,00 | PCWP | | | 28 | PX BLOCK ITERATOR | | 3494K| 336M| 1046 (1)| 00:00:13 | Q7,01 | PCWC | | | 29 | TABLE ACCESS FULL | TMP_ITEM_AGGR_EX_691 | 3494K| 336M| 1046 (1)| 00:00:13 | Q7,01 | PCWP | | | 30 | PX COORDINATOR | | | | | | | | | | 31 | PX SEND QC (RANDOM) | :TQ30000 | 1 | 9 | 8 (0)| 00:00:01 | Q3,00 | P->S | QC (RAND) | | 32 | PX BLOCK ITERATOR | | 1 | 9 | 8 (0)| 00:00:01 | Q3,00 | PCWC | | |* 33 | TABLE ACCESS FULL | RPT_OFFER_RATE | 1 | 9 | 8 (0)| 00:00:01 | Q3,00 | PCWP | | | 34 | PX COORDINATOR | | | | | | | | | | 35 | PX SEND QC (RANDOM) | :TQ40000 | 1 | 9 | 96 (2)| 00:00:02 | Q4,00 | P->S | QC (RAND) | | 36 | PX BLOCK ITERATOR | | 1 | 9 | 96 (2)| 00:00:02 | Q4,00 | PCWC | | |* 37 | TABLE ACCESS FULL | RPT_PRODUCT_RATE | 1 | 9 | 96 (2)| 00:00:02 | Q4,00 | PCWP | | | 38 | PX COORDINATOR | | | | | | | | | | 39 | PX SEND QC (RANDOM) | :TQ50000 | 1 | 5 | 3 (0)| 00:00:01 | Q5,00 | P->S | QC (RAND) | | 40 | PX BLOCK ITERATOR | | 1 | 5 | 3 (0)| 00:00:01 | Q5,00 | PCWC | | |* 41 | TABLE ACCESS FULL | RPT_ZM_RATE | 1 | 5 | 3 (0)| 00:00:01 | Q5,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS (SELECT 0 FROM "STATRPT"."RPT_OFFER_RATE" "RPT_OFFER_RATE" WHERE LNNVL("ACCT_ITEM_TYPE_ID"<>:B1) AND LNNVL("OFFER_ID"<>:B2)) AND NOT EXISTS (SELECT 0 FROM "STATRPT"."RPT_PRODUCT_RATE" "RPT_PRODUCT_RATE" WHERE LNNVL("ACCT_ITEM_TYPE_ID"<>:B3) AND LNNVL("PRODUCT_ID"<>:B4))) 5 - access("A"."ACCT_ITEM_TYPE_ID"="B"."ACCT_ITEM_TYPE_ID") 15 - filter(LNNVL("ACCT_ITEM_TYPE_ID"<>:B1) AND LNNVL("OFFER_ID"<>:B2)) 19 - filter(LNNVL("ACCT_ITEM_TYPE_ID"<>:B1) AND LNNVL("PRODUCT_ID"<>:B2)) 20 - filter( NOT EXISTS (SELECT 0 FROM "STATRPT"."RPT_OFFER_RATE" "RPT_OFFER_RATE" WHERE LNNVL("ACCT_ITEM_TYPE_ID"<>:B1) AND LNNVL("OFFER_ID"<>:B2)) AND NOT EXISTS (SELECT 0 FROM "STATRPT"."RPT_PRODUCT_RATE" "RPT_PRODUCT_RATE" WHERE LNNVL("ACCT_ITEM_TYPE_ID"<>:B3) AND LNNVL("PRODUCT_ID"<>:B4)) AND NOT EXISTS (SELECT 0 FROM "STATRPT"."RPT_ZM_RATE" "RPT_ZM_RATE" WHERE LNNVL("ACCT_ITEM_TYPE_ID"<>:B5))) 23 - access("A"."ACCT_ITEM_TYPE_ID"="B"."ACCT_ITEM_TYPE_ID") 33 - filter(LNNVL("ACCT_ITEM_TYPE_ID"<>:B1) AND LNNVL("OFFER_ID"<>:B2)) 37 - filter(LNNVL("ACCT_ITEM_TYPE_ID"<>:B1) AND LNNVL("PRODUCT_ID"<>:B2)) 41 - filter(LNNVL("ACCT_ITEM_TYPE_ID"<>:B1)) |
大家看该SQL的执行计划就知道，COST巨大无比，很显然这个SQL基本上是跑不动的。本人SQL优化比较弱，因此直接从原库进行对比，因此在原库跑了下SQL：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
Plan hash value: 2514835211 --------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 3493 (100)| | | | | | 1 | UNION-ALL | | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10004 | 557 | 71853 | | 1745 (3)| 00:00:21 | Q1,04 | P->S | QC (RAND) | |* 4 | HASH JOIN BUFFERED | | 557 | 71853 | | 1745 (3)| 00:00:21 | Q1,04 | PCWP | | | 5 | PX RECEIVE | | 557 | 64055 | | 1742 (3)| 00:00:21 | Q1,04 | PCWP | | | 6 | PX SEND HASH | :TQ10002 | 557 | 64055 | | 1742 (3)| 00:00:21 | Q1,02 | P->P | HASH | | 7 | MERGE JOIN ANTI NA | | 557 | 64055 | | 1742 (3)| 00:00:21 | Q1,02 | PCWP | | | 8 | SORT JOIN | | 55738 | 5769K| 12M| 1733 (3)| 00:00:21 | Q1,02 | PCWP | | | 9 | MERGE JOIN ANTI NA | | 55738 | 5769K| | 1732 (3)| 00:00:21 | Q1,02 | PCWP | | | 10 | SORT JOIN | | 5573K| 515M| 1643M| 1631 (2)| 00:00:20 | Q1,02 | PCWP | | | 11 | PX BLOCK ITERATOR | | 5573K| 515M| | 1614 (1)| 00:00:20 | Q1,02 | PCWC | | |* 12 | TABLE ACCESS FULL | TMP_ITEM_AGGR_EX_691 | 5573K| 515M| | 1614 (1)| 00:00:20 | Q1,02 | PCWP | | |* 13 | SORT UNIQUE | | 421K| 3704K| 16M| 101 (5)| 00:00:02 | Q1,02 | PCWP | | | 14 | PX RECEIVE | | 421K| 3704K| | 96 (0)| 00:00:02 | Q1,02 | PCWP | | | 15 | PX SEND BROADCAST | :TQ10000 | 421K| 3704K| | 96 (0)| 00:00:02 | Q1,00 | P->P | BROADCAST | | 16 | PX BLOCK ITERATOR | | 421K| 3704K| | 96 (0)| 00:00:02 | Q1,00 | PCWC | | |* 17 | TABLE ACCESS FULL | RPT_PRODUCT_RATE | 421K| 3704K| | 96 (0)| 00:00:02 | Q1,00 | PCWP | | |* 18 | SORT UNIQUE | | 22695 | 199K| | 9 (12)| 00:00:01 | Q1,02 | PCWP | | | 19 | PX RECEIVE | | 22695 | 199K| | 8 (0)| 00:00:01 | Q1,02 | PCWP | | | 20 | PX SEND BROADCAST | :TQ10001 | 22695 | 199K| | 8 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST | | 21 | PX BLOCK ITERATOR | | 22695 | 199K| | 8 (0)| 00:00:01 | Q1,01 | PCWC | | |* 22 | TABLE ACCESS FULL | RPT_OFFER_RATE | 22695 | 199K| | 8 (0)| 00:00:01 | Q1,01 | PCWP | | | 23 | PX RECEIVE | | 1059 | 14826 | | 3 (0)| 00:00:01 | Q1,04 | PCWP | | | 24 | PX SEND HASH | :TQ10003 | 1059 | 14826 | | 3 (0)| 00:00:01 | Q1,03 | P->P | HASH | | 25 | PX BLOCK ITERATOR | | 1059 | 14826 | | 3 (0)| 00:00:01 | Q1,03 | PCWC | | |* 26 | TABLE ACCESS FULL | RPT_ZM_RATE | 1059 | 14826 | | 3 (0)| 00:00:01 | Q1,03 | PCWP | | | 27 | PX COORDINATOR | | | | | | | | | | | 28 | PX SEND QC (RANDOM) | :TQ20004 | 6 | 804 | | 1748 (3)| 00:00:21 | Q2,04 | P->S | QC (RAND) | |* 29 | HASH JOIN | | 6 | 804 | | 1748 (3)| 00:00:21 | Q2,04 | PCWP | | | 30 | PX RECEIVE | | 6 | 720 | | 1745 (3)| 00:00:21 | Q2,04 | PCWP | | | 31 | PX SEND BROADCAST | :TQ20003 | 6 | 720 | | 1745 (3)| 00:00:21 | Q2,03 | P->P | BROADCAST | | 32 | MERGE JOIN ANTI NA | | 6 | 720 | | 1745 (3)| 00:00:21 | Q2,03 | PCWP | | | 33 | SORT JOIN | | 557 | 61827 | | 1736 (3)| 00:00:21 | Q2,03 | PCWP | | |* 34 | HASH JOIN RIGHT ANTI NA| | 557 | 61827 | | 1735 (3)| 00:00:21 | Q2,03 | PCWP | | | 35 | PX RECEIVE | | 1059 | 5295 | | 3 (0)| 00:00:01 | Q2,03 | PCWP | | | 36 | PX SEND BROADCAST | :TQ20000 | 1059 | 5295 | | 3 (0)| 00:00:01 | Q2,00 | P->P | BROADCAST | | 37 | PX BLOCK ITERATOR | | 1059 | 5295 | | 3 (0)| 00:00:01 | Q2,00 | PCWC | | |* 38 | TABLE ACCESS FULL | RPT_ZM_RATE | 1059 | 5295 | | 3 (0)| 00:00:01 | Q2,00 | PCWP | | | 39 | MERGE JOIN ANTI NA | | 55738 | 5769K| | 1732 (3)| 00:00:21 | Q2,03 | PCWP | | | 40 | SORT JOIN | | 5573K| 515M| 1643M| 1631 (2)| 00:00:20 | Q2,03 | PCWP | | | 41 | PX BLOCK ITERATOR | | 5573K| 515M| | 1614 (1)| 00:00:20 | Q2,03 | PCWC | | |* 42 | TABLE ACCESS FULL | TMP_ITEM_AGGR_EX_691 | 5573K| 515M| | 1614 (1)| 00:00:20 | Q2,03 | PCWP | | |* 43 | SORT UNIQUE | | 421K| 3704K| 16M| 101 (5)| 00:00:02 | Q2,03 | PCWP | | | 44 | PX RECEIVE | | 421K| 3704K| | 96 (0)| 00:00:02 | Q2,03 | PCWP | | | 45 | PX SEND BROADCAST | :TQ20001 | 421K| 3704K| | 96 (0)| 00:00:02 | Q2,01 | P->P | BROADCAST | | 46 | PX BLOCK ITERATOR | | 421K| 3704K| | 96 (0)| 00:00:02 | Q2,01 | PCWC | | |* 47 | TABLE ACCESS FULL| RPT_PRODUCT_RATE | 421K| 3704K| | 96 (0)| 00:00:02 | Q2,01 | PCWP | | |* 48 | SORT UNIQUE | | 22695 | 199K| | 9 (12)| 00:00:01 | Q2,03 | PCWP | | | 49 | PX RECEIVE | | 22695 | 199K| | 8 (0)| 00:00:01 | Q2,03 | PCWP | | | 50 | PX SEND BROADCAST | :TQ20002 | 22695 | 199K| | 8 (0)| 00:00:01 | Q2,02 | P->P | BROADCAST | | 51 | PX BLOCK ITERATOR | | 22695 | 199K| | 8 (0)| 00:00:01 | Q2,02 | PCWC | | |* 52 | TABLE ACCESS FULL | RPT_OFFER_RATE | 22695 | 199K| | 8 (0)| 00:00:01 | Q2,02 | PCWP | | | 53 | PX BLOCK ITERATOR | | 6083 | 85162 | | 3 (0)| 00:00:01 | Q2,04 | PCWC | | |* 54 | TABLE ACCESS FULL | TMP_ZM_ONLY_RATE | 6083 | 85162 | | 3 (0)| 00:00:01 | Q2,04 | PCWP | | --------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."ACCT_ITEM_TYPE_ID"="B"."ACCT_ITEM_TYPE_ID") 12 - access(:Z>=:Z AND :Z<=:Z) 13 - access(INTERNAL_FUNCTION("A"."ACCT_ITEM_TYPE_ID")=INTERNAL_FUNCTION("ACCT_ITEM_TYPE_ID") AND INTERNAL_FUNCTION("A"."PRODUCT_ID")=INTERNAL_FUNCTION("PRODUCT_ID")) filter((INTERNAL_FUNCTION("A"."PRODUCT_ID")=INTERNAL_FUNCTION("PRODUCT_ID") AND INTERNAL_FUNCTION("A"."ACCT_ITEM_TYPE_ID")=INTERNAL_FUNCTION("ACCT_ITEM_TYPE_ID"))) 17 - access(:Z>=:Z AND :Z<=:Z) 18 - access(INTERNAL_FUNCTION("A"."ACCT_ITEM_TYPE_ID")=INTERNAL_FUNCTION("ACCT_ITEM_TYPE_ID") AND INTERNAL_FUNCTION("A"."OFFER_CD")=INTERNAL_FUNCTION("OFFER_ID")) filter((INTERNAL_FUNCTION("A"."OFFER_CD")=INTERNAL_FUNCTION("OFFER_ID") AND INTERNAL_FUNCTION("A"."ACCT_ITEM_TYPE_ID")=INTERNAL_FUNCTION("ACCT_ITEM_TYPE_ID"))) 22 - access(:Z>=:Z AND :Z<=:Z) 26 - access(:Z>=:Z AND :Z<=:Z) 29 - access("A"."ACCT_ITEM_TYPE_ID"="B"."ACCT_ITEM_TYPE_ID") 34 - access("A"."ACCT_ITEM_TYPE_ID"="ACCT_ITEM_TYPE_ID") 38 - access(:Z>=:Z AND :Z<=:Z) 42 - access(:Z>=:Z AND :Z<=:Z) 43 - access(INTERNAL_FUNCTION("A"."ACCT_ITEM_TYPE_ID")=INTERNAL_FUNCTION("ACCT_ITEM_TYPE_ID") AND INTERNAL_FUNCTION("A"."PRODUCT_ID")=INTERNAL_FUNCTION("PRODUCT_ID")) filter((INTERNAL_FUNCTION("A"."PRODUCT_ID")=INTERNAL_FUNCTION("PRODUCT_ID") AND INTERNAL_FUNCTION("A"."ACCT_ITEM_TYPE_ID")=INTERNAL_FUNCTION("ACCT_ITEM_TYPE_ID"))) 47 - access(:Z>=:Z AND :Z<=:Z) 48 - access(INTERNAL_FUNCTION("A"."ACCT_ITEM_TYPE_ID")=INTERNAL_FUNCTION("ACCT_ITEM_TYPE_ID") AND INTERNAL_FUNCTION("A"."OFFER_CD")=INTERNAL_FUNCTION("OFFER_ID")) filter((INTERNAL_FUNCTION("A"."OFFER_CD")=INTERNAL_FUNCTION("OFFER_ID") AND INTERNAL_FUNCTION("A"."ACCT_ITEM_TYPE_ID")=INTERNAL_FUNCTION("ACCT_ITEM_TYPE_ID"))) 52 - access(:Z>=:Z AND :Z<=:Z) 54 - access(:Z>=:Z AND :Z<=:Z) |
很明显，原库的执行计划要好的，通过对比执行计划，我们发现：性能较差的SQL的执行计划中，not in 被改写成了not exits，进行了一些filter操作。而性能较高的SQL的执行计划，则是选择了ANTI Join。
问题是原来为什么ok ？存储迁移之后就有问题了呢 ？第一感觉可能是调整了优化器参数，检查发现果然是：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SYS@rptdb1> show parameter optimizer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _optimizer_adaptive_cursor_sharing boolean FALSE _optimizer_extended_cursor_sharing string NONE _optimizer_extended_cursor_sharing_r string NONE el _optimizer_null_aware_antijoin boolean FALSE _optimizer_use_feedback boolean FALSE optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.2.0.2 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE optimizer_use_invisible_indexes boolean FALSE optimizer_use_pending_statistics boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE SYS@rptdb1> SYS@rptdb1> alter session set "_optimizer_null_aware_antijoin"=true; Session altered. |
通过将该参数改回默认值，测试一切正常。 这里我主要是通过SQLT来解决该SQL的性能问题，首先创建一个SQL profile,然后修改SQL profile的查询块信息即可，如下：
|
1 2 3 4 5 |
q'[OPT_PARAM('_optimizer_null_aware_antijoin' 'true')]', q'[OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')]', q'[OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')]', q'[OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')]', q'[OPT_PARAM('_optimizer_use_feedback' 'false')]', |
通过调整之后，SQL性能恢复正常。 虽然这是一个很常见的问题，然而我却是第一次在生产中碰见，下面进行一个简单的测试。
说明：测试脚本来自google。
—For 10.2.0.5
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
www.killdb.com> create table t1 2 as select 3 cast(rownum as int) a, 4 cast(rownum+10 as int) b, 5 cast(dbms_random.string('i',10) as varchar2(10)) c 6 from dual connect by level<=10000; Table created. www.killdb.com> create table t2 2 as select 3 cast(rownum as int) a, 4 cast(rownum+10 as int) b, 5 cast(dbms_random.string('i',10) as varchar2(10)) c 6 from dual connect by level<=9980; Table created. www.killdb.com> www.killdb.com> set autot traceonly exp www.killdb.com> analyze table t1 compute statistics; Table analyzed. www.killdb.com> analyze table t2 compute statistics; Table analyzed. www.killdb.com> select /*SQL_1*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 895956251 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9999 | 126K| 60407 (1)| 00:12:05 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T1 | 10000 | 126K| 12 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T2 | 1 | 3 | 12 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE LNNVL("A"<>:B1))) 3 - filter(LNNVL("A"<>:B1)) www.killdb.com> alter table t2 modify a not null ; Table altered. www.killdb.com> select /*SQL_2*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 895956251 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9999 | 126K| 60407 (1)| 00:12:05 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T1 | 10000 | 126K| 12 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T2 | 1 | 3 | 12 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE LNNVL("A"<>:B1))) 3 - filter(LNNVL("A"<>:B1)) www.killdb.com> create index idx_t2_a on t2(a); Index created. www.killdb.com> create index idx_t1_a on t1(a); Index created. www.killdb.com> select /*SQL_3*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 377637984 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9999 | 126K| 35333 (1)| 00:07:04 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | T1 | 10000 | 126K| 12 (0)| 00:00:01 | |* 3 | INDEX FAST FULL SCAN| IDX_T2_A | 1 | 3 | 7 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE LNNVL("A"<>:B1))) 3 - filter(LNNVL("A"<>:B1)) www.killdb.com> |
我们可以看到，仍然没有走办连接，还是走filter了，这里的类似nest loop，很明显效率很低，其原因是需要用T1表的每条记录去和T2 返回的结果集进行匹配。那么有没有办法让SQL走半连接呢 ？ 肯定是可以的，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
www.killdb.com> alter table t1 modify a not null ; Table altered. www.killdb.com> select /*SQL_4*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 1490751970 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 320 | 20 (5)| 00:00:01 | |* 1 | HASH JOIN RIGHT ANTI | | 20 | 320 | 20 (5)| 00:00:01 | | 2 | INDEX FAST FULL SCAN| IDX_T2_A | 9980 | 29940 | 7 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T1 | 10000 | 126K| 12 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"="A") |
我们可以看到，走半连接之后，效率明显要高的多。当然，这里不对t1表进行not null操作也可以进行优化。
—-for 11.2.0.2 test
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
[ora11g@localhost ~]$ sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.2.0 Production on Sat Apr 18 22:59:32 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options www.killdb.com> conn roger/roger Connected. www.killdb.com> SQL> create table t1 2 as select 3 cast(rownum as int) a, 4 cast(rownum+10 as int) b, 5 cast(dbms_random.string('i',10) as varchar2(10)) c 6 from dual connect by level<=10000; Table created. SQL> create table t2 2 as select 3 cast(rownum as int) a, 4 cast(rownum+10 as int) b, 5 cast(dbms_random.string('i',10) as varchar2(10)) c 6 from dual connect by level<=9980; Table created. SQL> analyze table t1 compute statistics ; Table analyzed. SQL> analyze table t2 compute statistics; Table analyzed. SQL> set autot traceonly exp SQL> select /*SQL_1*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 2739594415 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 1600 | 23 (5)| 00:00:01 | |* 1 | HASH JOIN RIGHT ANTI NA| | 100 | 1600 | 23 (5)| 00:00:01 | | 2 | TABLE ACCESS FULL | T2 | 9980 | 29940 | 11 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T1 | 10000 | 126K| 11 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"="A") SQL> alter session set "_optimizer_null_aware_antijoin"=false; Session altered. SQL> select /*SQL_2*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 895956251 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9999 | 126K| 55478 (1)| 00:11:06 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T1 | 10000 | 126K| 11 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T2 | 1 | 3 | 11 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE LNNVL("A"<>:B1))) 3 - filter(LNNVL("A"<>:B1)) SQL> alter table t2 modify a not null; Table altered. SQL> create index idx_t2_a on t2(a); Index created. SQL> create index idx_t1_a on t1(a); Index created. SQL> SQL> alter session set "_optimizer_null_aware_antijoin"=true; Session altered. SQL> select /*SQL_3*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 2568882110 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 1600 | 19 (6)| 00:00:01 | |* 1 | HASH JOIN RIGHT ANTI SNA| | 100 | 1600 | 19 (6)| 00:00:01 | | 2 | INDEX FAST FULL SCAN | IDX_T2_A | 9980 | 29940 | 7 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T1 | 10000 | 126K| 11 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"="A") SQL> alter session set "_optimizer_null_aware_antijoin"=false; Session altered. SQL> select /*SQL_3*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 377637984 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9999 | 126K| 35396 (2)| 00:07:05 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | T1 | 10000 | 126K| 11 (0)| 00:00:01 | |* 3 | INDEX FAST FULL SCAN| IDX_T2_A | 1 | 3 | 7 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE LNNVL("A"<>:B1))) 3 - filter(LNNVL("A"<>:B1)) SQL> alter table t1 modify a not null ; Table altered. SQL> select /*SQL_3*/ c from t1 where a not in (select a from t2) ; Execution Plan ---------------------------------------------------------- Plan hash value: 1490751970 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 1600 | 19 (6)| 00:00:01 | |* 1 | HASH JOIN RIGHT ANTI | | 100 | 1600 | 19 (6)| 00:00:01 | | 2 | INDEX FAST FULL SCAN| IDX_T2_A | 9980 | 29940 | 7 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T1 | 10000 | 126K| 11 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"="A") |
实际上，通过我们测试可以发现，本质上应用SQL出问题，不是我们调整参数的问题，而是应用SQL写法不规范导致。或者说应用表结构设计存在缺陷导致。实际上该SQL，我们不需要调整隐含参数，通过对表的column 添加非空约束即可。
Leave a Reply
You must be logged in to post a comment.