_optimizer_null_aware_antijoin引发的SQL性能问题

前几天某客户联系我说之前我们进行存储迁移的系统，有个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')]',

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")

[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 添加非空约束即可。