一聚教程网:一个值得你收藏的教程网站

热门教程

浅析_optimizer_null_aware_antijoin引发的SQL性能问题

时间:2022-06-29 09:40:55 编辑:袖梨 来源:一聚教程网


前几天某客户联系我说之前我们进行存储迁移的系统,有个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.111com.net> 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.111com.net> 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.111com.net>
www.111com.net> set autot traceonly exp
www.111com.net> analyze table t1 compute statistics;
 
Table analyzed.
 
www.111com.net> analyze table t2 compute statistics;
 
Table analyzed.
 
www.111com.net> 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.111com.net> alter table t2 modify a not null ;
 
Table altered.
 
www.111com.net> 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.111com.net> create index idx_t2_a on t2(a);
 
Index created.
 
www.111com.net> create index idx_t1_a on t1(a);
 
Index created.
 
www.111com.net> 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.111com.net>
我们可以看到,仍然没有走办连接,还是走filter了,这里的类似nest loop,很明显效率很低,其原因是需要用T1表的每条记录去和T2 返回的结果集进行匹配。那么有没有办法让SQL走半连接呢 ? 肯定是可以的,如下:

www.111com.net> alter table t1 modify a not null ;
 
Table altered.
 

热门栏目