本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
前几天某客户联系我说之前我们进行存储迁移的系统，有个SQL跑的极慢，根本跑不出来结果。通过VPN登录看了下，SQL确认跑的很慢。开始我很难理解，我们仅仅是进行了存储迁移，数据库基本上没动，为什么会有SQL性能问题呢？ 我们先来看看有问题的SQL：
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：
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 ？存储迁移之后就有问题了呢 ？第一感觉可能是调整了优化器参数，检查发现果然是：
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的查询块信息即可，如下：
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
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走半连接呢 ？ 肯定是可以的，如下：
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
[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 添加非空约束即可。
Related posts: