oracle 11g新特性Cardinality Feedback基数反馈造成同一sql几乎同时执行产生不同的执行计划

ardinality Feedback基数反馈是版本11.2中引入的关于SQL 性能优化的新特性,该特性主要针对 统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况, Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估,造成CBO选择不当的执行计划
在几乎同时执行如下语句:

select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_
SQL>select plan_hash_value,id,operation,options,object_name,depth,cost,timestamp,child_address
  from v$SQL_PLAN
   where sql_id ='64q8v2p41c1vc'
  and plan_hash_value in (1059287951,3791045646);

PLAN_HASH_VALUE     ID       OPERATION            OPTIONS           OBJECT_NAME          DEPTH    COST       TIMESTAMP
---------------     --       ----------------     --------       ------------------     -------   ------     -------------
3791045646          0        SELECT STATEMENT                                            0        43960      2013-10-17 14:52:53
3791045646          1        SORT                 AGGREGATE                              1                   2013-10-17 14:52:53
3791045646          2        VIEW                                                        2        43960      2013-10-17 14:52:53
3791045646          3        COUNT                                                       3                   2013-10-17 14:52:53
3791045646          4        FILTER                                                      4                   2013-10-17 14:52:53
3791045646          5        HASH JOIN            OUTER                                  5        43960      2013-10-17 14:52:53
3791045646          6        NESTED LOOPS         OUTER                                  6        43873      2013-10-17 14:52:53
3791045646          7        NESTED LOOPS         OUTER                                  7        43822      2013-10-17 14:52:53
3791045646          8        MERGE JOIN           CARTESIAN                              8        43822      2013-10-17 14:52:53
3791045646          9        MERGE JOIN           CARTESIAN                              9        43822      2013-10-17 14:52:53
3791045646          10       NESTED LOOPS                                                10       2          2013-10-17 14:52:53
3791045646          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:52:53
3791045646          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:52:53
3791045646          13       INDEX                UNIQUE SCAN       PK_AEZ1              11       0          2013-10-17 14:52:53
3791045646          14       BUFFER               SORT                                   10       43822      2013-10-17 14:52:53
3791045646          15       TABLE ACCESS         BY INDEX ROWID    KC21                 11       43820      2013-10-17 14:52:53
3791045646          16       INDEX                RANGE SCAN        IDX_KC21_PKA025      12       192        2013-10-17 14:52:53
3791045646          17       BUFFER               SORT                                   9        2          2013-10-17 14:52:53
3791045646          18       INDEX                RANGE SCAN        IDX_KAA1_AKA130      10       0          2013-10-17 14:52:53
3791045646          19       INDEX                RANGE SCAN        IDX_KAA2_PKA006      8        0          2013-10-17 14:52:53
3791045646          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:52:53
3791045646          21       INDEX                FULL SCAN         IDX_KA06_AKA120      6        86         2013-10-17 14:52:53
1059287951          0        SELECT STATEMENT                                            0        51         2013-10-17 14:52:03
1059287951          1        SORT                 AGGREGATE                              1                   2013-10-17 14:52:03
1059287951          2        VIEW                                                        2        51         2013-10-17 14:52:03
1059287951          3        COUNT                                                       3                   2013-10-17 14:52:03
1059287951          4        FILTER                                                      4                   2013-10-17 14:52:03
1059287951          5        NESTED LOOPS         OUTER                                  5        51         2013-10-17 14:52:03
1059287951          6        NESTED LOOPS         OUTER                                  6        50         2013-10-17 14:52:03
1059287951          7        NESTED LOOPS                                                7        49         2013-10-17 14:52:03
1059287951          8        NESTED LOOPS         OUTER                                  8        49         2013-10-17 14:52:03
1059287951          9        MERGE JOIN           CARTESIAN                              9        49         2013-10-17 14:52:03
1059287951          10       MERGE JOIN           CARTESIAN                              10       49         2013-10-17 14:52:03
1059287951          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:52:03
1059287951          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:52:03
1059287951          13       BUFFER               SORT                                   11       47         2013-10-17 14:52:03
1059287951          14       TABLE ACCESS         BY INDEX ROWID    KC21                 12       47         2013-10-17 14:52:03
1059287951          15       INDEX                RANGE SCAN        IDX_KC21_PKA045      13       3          2013-10-17 14:52:03
1059287951          16       BUFFER               SORT                                   10       2          2013-10-17 14:52:03
1059287951          17       INDEX                RANGE SCAN        IDX_KAA1_AKA130      11       0          2013-10-17 14:52:03
1059287951          18       INDEX                RANGE SCAN        IDX_KAA2_PKA006      9        0          2013-10-17 14:52:03
1059287951          19       INDEX                UNIQUE SCAN       PK_AEZ1              8        0          2013-10-17 14:52:03
1059287951          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:52:03
1059287951          21       INDEX                RANGE SCAN        IDX_KA06_AKA120      6        1          2013-10-17 14:52:03
1059287951          0        SELECT STATEMENT                                            0        51         2013-10-17 14:50:37
1059287951          1        SORT                 AGGREGATE                              1                   2013-10-17 14:50:37
1059287951          2        VIEW                                                        2        51         2013-10-17 14:50:37
1059287951          3        COUNT                                                       3                   2013-10-17 14:50:37
1059287951          4        FILTER                                                      4                   2013-10-17 14:50:37
1059287951          5        NESTED LOOPS         OUTER                                  5        51         2013-10-17 14:50:37
1059287951          6        NESTED LOOPS         OUTER                                  6        50         2013-10-17 14:50:37
1059287951          7        NESTED LOOPS                                                7        49         2013-10-17 14:50:37
1059287951          8        NESTED LOOPS         OUTER                                  8        49         2013-10-17 14:50:37
1059287951          9        MERGE JOIN           CARTESIAN                              9        49         2013-10-17 14:50:37
1059287951          10       MERGE JOIN           CARTESIAN                              10       49         2013-10-17 14:50:37
1059287951          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:50:37
1059287951          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:50:37
1059287951          13       BUFFER               SORT                                   11       47         2013-10-17 14:50:37
1059287951          14       TABLE ACCESS         BY INDEX ROWID    KC21                 12       47         2013-10-17 14:50:37
1059287951          15       INDEX                RANGE SCAN        IDX_KC21_PKA045      13       3          2013-10-17 14:50:37
1059287951          16       BUFFER               SORT                                   10       2          2013-10-17 14:50:37
1059287951          17       INDEX                RANGE SCAN        IDX_KAA1_AKA130      11       0          2013-10-17 14:50:37
1059287951          18       INDEX                RANGE SCAN        IDX_KAA2_PKA006      9        0          2013-10-17 14:50:37
1059287951          19       INDEX                UNIQUE SCAN       PK_AEZ1              8        0          2013-10-17 14:50:37
1059287951          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:50:37
1059287951          21       INDEX                RANGE SCAN        IDX_KA06_AKA120      6        1          2013-10-17 14:50:37

得到以下不同的执行计划(这个语句执行了两次同一个sql_id产生不两个子游标使用不同的执行计划)

SQL_ID  64q8v2p41c1vc, child number 0
-------------------------------------
select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_

Plan hash value: 1059287951

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |       |       |    51 (100)|          |
|   1 |  SORT AGGREGATE                        |                 |     1 |       |            |          |
|   2 |   VIEW                                 |                 |     1 |       |    51   (0)| 00:00:01 |
|   3 |    COUNT                               |                 |       |       |            |          |
|*  4 |     FILTER                             |                 |       |       |            |          |
|   5 |      NESTED LOOPS OUTER                |                 |     1 |   115 |    51   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER               |                 |     1 |   106 |    50   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                    |                 |     1 |    97 |    49   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER             |                 |     1 |    90 |    49   (0)| 00:00:01 |
|   9 |          MERGE JOIN CARTESIAN          |                 |     1 |    85 |    49   (0)| 00:00:01 |
|  10 |           MERGE JOIN CARTESIAN         |                 |     1 |    82 |    49   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID | KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN           | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|  13 |            BUFFER SORT                 |                 |     1 |    67 |    47   (0)| 00:00:01 |
|* 14 |             TABLE ACCESS BY INDEX ROWID| KC21            |     1 |    67 |    47   (0)| 00:00:01 |
|* 15 |              INDEX RANGE SCAN          | IDX_KC21_PKA045 |    60 |       |     3   (0)| 00:00:01 |
|  16 |           BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 18 |          INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 19 |         INDEX UNIQUE SCAN              | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN                | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|* 21 |       INDEX RANGE SCAN                 | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$3        / W@SEL$2
   3 - SEL$3
  11 - SEL$3        / T2@SEL$3
  12 - SEL$3        / T2@SEL$3
  14 - SEL$3        / B@SEL$3
  15 - SEL$3        / B@SEL$3
  17 - SEL$3        / G@SEL$3
  18 - SEL$3        / H@SEL$3
  19 - SEL$3        / A@SEL$3
  20 - SEL$3        / F@SEL$3
  21 - SEL$3        / E@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "W"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("KB01"."AKB020"))
      INDEX_RS_ASC(@"SEL$3" "B"@"SEL$3" ("KC21"."AKB020" "KC21"."PKA045"))
      INDEX(@"SEL$3" "G"@"SEL$3" ("KAA1"."AKA130"))
      INDEX(@"SEL$3" "H"@"SEL$3" ("KAA2"."PKA006"))
      INDEX(@"SEL$3" "A"@"SEL$3" ("AEZ1"."AAZ001"))
      INDEX(@"SEL$3" "F"@"SEL$3" ("KA06"."AKA120"))
      INDEX(@"SEL$3" "E"@"SEL$3" ("KA06"."AKA120"))
      LEADING(@"SEL$3" "T2"@"SEL$3" "B"@"SEL$3" "G"@"SEL$3" "H"@"SEL$3" "A"@"SEL$3" "F"@"SEL$3"
              "E"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "B"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "G"@"SEL$3")
      USE_NL(@"SEL$3" "H"@"SEL$3")
      USE_NL(@"SEL$3" "A"@"SEL$3")
      USE_NL(@"SEL$3" "F"@"SEL$3")
      USE_NL(@"SEL$3" "E"@"SEL$3")
      INDEX_RS_ASC(@"SEL$7" "B2"@"SEL$7" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX(@"SEL$7" "C2"@"SEL$7" ("KCB4"."PKB019"))
      LEADING(@"SEL$7" "B2"@"SEL$7" "C2"@"SEL$7")
      USE_NL(@"SEL$7" "C2"@"SEL$7")
      NLJ_BATCHING(@"SEL$7" "C2"@"SEL$7")
      INDEX_RS_ASC(@"SEL$6" "B1"@"SEL$6" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX_RS_ASC(@"SEL$5" "T1"@"SEL$5" ("ACK1"."AAC001"))
      INDEX_RS_ASC(@"SEL$4" "T1"@"SEL$4" ("ACK1"."AAC001"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')<=TO_DATE('20130930
              23:59:59','yyyy-mm-dd hh24:mi:ss'))
  12 - access("T2"."AKB020"='002001')
  14 - filter(("B"."AKA130"='12' AND "B"."AAE100"='1' AND "B"."PKA039"='1' AND "B"."PKA037"='0'
              AND INTERNAL_FUNCTION("B"."PKA004")))
  15 - access("B"."AKB020"='002001' AND "B"."PKA045">=TO_DATE('20130901 00:00:00','yyyy-mm-dd
              hh24:mi:ss') AND "B"."PKA045"< =TO_DATE('20130930 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  17 - access("G"."AKA130"='12')
  18 - access("B"."PKA006"="H"."PKA006")
  19 - access("A"."AAZ001"="T2"."AAZ269")
  20 - access("B"."PKA031"="F"."AKA120")
  21 - access("B"."PKA026"="E"."AKA120")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   6 - "B"."PKA026"[VARCHAR2,20]
   7 - "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   8 - "T2"."AAZ269"[NUMBER,22], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   9 - "T2"."AAZ269"[NUMBER,22], "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20],
       "B"."PKA031"[VARCHAR2,20]
  10 - "T2"."AAZ269"[NUMBER,22], "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20],
       "B"."PKA031"[VARCHAR2,20]
  11 - "T2"."AAZ269"[NUMBER,22]
  12 - "T2".ROWID[ROWID,10]
  13 - (#keys=0) "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  14 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  15 - "B".ROWID[ROWID,10]
  16 - (#keys=0)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

SQL_ID  64q8v2p41c1vc, child number 1
-------------------------------------
select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_

Plan hash value: 3791045646

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       | 43960 (100)|          |
|   1 |  SORT AGGREGATE                       |                 |     1 |       |            |          |
|   2 |   VIEW                                |                 |  1079 |       | 43960   (1)| 00:08:48 |
|   3 |    COUNT                              |                 |       |       |            |          |
|*  4 |     FILTER                            |                 |       |       |            |          |
|*  5 |      HASH JOIN OUTER                  |                 |  1079 |   121K| 43960   (1)| 00:08:48 |
|   6 |       NESTED LOOPS OUTER              |                 |  1079 |   111K| 43873   (1)| 00:08:47 |
|   7 |        NESTED LOOPS OUTER             |                 |  1079 |   102K| 43822   (1)| 00:08:46 |
|   8 |         MERGE JOIN CARTESIAN          |                 |  1079 | 99268 | 43822   (1)| 00:08:46 |
|   9 |          MERGE JOIN CARTESIAN         |                 |  1079 | 96031 | 43822   (1)| 00:08:46 |
|  10 |           NESTED LOOPS                |                 |     1 |    22 |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN          | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|  14 |           BUFFER SORT                 |                 |  1079 | 72293 | 43822   (1)| 00:08:46 |
|* 15 |            TABLE ACCESS BY INDEX ROWID| KC21            |  1079 | 72293 | 43820   (1)| 00:08:46 |
|* 16 |             INDEX RANGE SCAN          | IDX_KC21_PKA025 | 56948 |       |   192   (2)| 00:00:03 |
|  17 |          BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 19 |         INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN               | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|  21 |       INDEX FULL SCAN                 | IDX_KA06_AKA120 | 22655 |   199K|    86   (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$3        / W@SEL$2
   3 - SEL$3
  11 - SEL$3        / T2@SEL$3
  12 - SEL$3        / T2@SEL$3
  13 - SEL$3        / A@SEL$3
  15 - SEL$3        / B@SEL$3
  16 - SEL$3        / B@SEL$3
  18 - SEL$3        / G@SEL$3
  19 - SEL$3        / H@SEL$3
  20 - SEL$3        / F@SEL$3
  21 - SEL$3        / E@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "W"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("KB01"."AKB020"))
      INDEX(@"SEL$3" "A"@"SEL$3" ("AEZ1"."AAZ001"))
      INDEX_RS_ASC(@"SEL$3" "B"@"SEL$3" ("KC21"."AKB020" "KC21"."PKA025"))
      INDEX(@"SEL$3" "G"@"SEL$3" ("KAA1"."AKA130"))
      INDEX(@"SEL$3" "H"@"SEL$3" ("KAA2"."PKA006"))
      INDEX(@"SEL$3" "F"@"SEL$3" ("KA06"."AKA120"))
      INDEX(@"SEL$3" "E"@"SEL$3" ("KA06"."AKA120"))
      LEADING(@"SEL$3" "T2"@"SEL$3" "A"@"SEL$3" "B"@"SEL$3" "G"@"SEL$3" "H"@"SEL$3" "F"@"SEL$3"
              "E"@"SEL$3")
      USE_NL(@"SEL$3" "A"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "B"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "G"@"SEL$3")
      USE_NL(@"SEL$3" "H"@"SEL$3")
      USE_NL(@"SEL$3" "F"@"SEL$3")
      USE_HASH(@"SEL$3" "E"@"SEL$3")
      INDEX_RS_ASC(@"SEL$7" "B2"@"SEL$7" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX(@"SEL$7" "C2"@"SEL$7" ("KCB4"."PKB019"))
      LEADING(@"SEL$7" "B2"@"SEL$7" "C2"@"SEL$7")
      USE_NL(@"SEL$7" "C2"@"SEL$7")
      NLJ_BATCHING(@"SEL$7" "C2"@"SEL$7")
      INDEX_RS_ASC(@"SEL$6" "B1"@"SEL$6" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX_RS_ASC(@"SEL$5" "T1"@"SEL$5" ("ACK1"."AAC001"))
      INDEX_RS_ASC(@"SEL$4" "T1"@"SEL$4" ("ACK1"."AAC001"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')<=TO_DATE('20130930
              23:59:59','yyyy-mm-dd hh24:mi:ss'))
   5 - access("B"."PKA026"="E"."AKA120")
  12 - access("T2"."AKB020"='002001')
  13 - access("A"."AAZ001"="T2"."AAZ269")
  15 - filter(("B"."AKA130"='12' AND "B"."AAE100"='1' AND "B"."PKA039"='1' AND "B"."PKA037"='0'
              AND INTERNAL_FUNCTION("B"."PKA004") AND "B"."PKA045"<=TO_DATE('20130930 23:59:59','yyyy-mm-dd
              hh24:mi:ss') AND "B"."PKA045">=TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')))
  16 - access("B"."AKB020"='002001')
  18 - access("G"."AKA130"='12')
  19 - access("B"."PKA006"="H"."PKA006")
  20 - access("B"."PKA031"="F"."AKA120")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   5 - (#keys=1)
   6 - "B"."PKA026"[VARCHAR2,20]
   7 - "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   8 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   9 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  11 - "T2"."AAZ269"[NUMBER,22]
  12 - "T2".ROWID[ROWID,10]
  14 - (#keys=0) "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  15 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  16 - "B".ROWID[ROWID,10]
  17 - (#keys=0)
  21 - "E"."AKA120"[VARCHAR2,50]

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing
   - cardinality feedback used for this statement

从上面两个游标的执行计划可以看到对于表kc21的访问 plan_hash_value=1059287951使用
INDEX RANGE SCAN | IDX_KC21_PKA045 | 60 | 使用索引IDX_KC21_PKA045进行索引范围扫描评估的记录是60
而plan_hash_value=3791045646使用
INDEX RANGE SCAN | IDX_KC21_PKA025 | 56948 | 使用索引IDX_KC21_PKA025 进行索引范围扫描评估的记录是56948

Plan hash value: 1059287951

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |       |       |    51 (100)|          |
|   1 |  SORT AGGREGATE                        |                 |     1 |       |            |          |
|   2 |   VIEW                                 |                 |     1 |       |    51   (0)| 00:00:01 |
|   3 |    COUNT                               |                 |       |       |            |          |
|*  4 |     FILTER                             |                 |       |       |            |          |
|   5 |      NESTED LOOPS OUTER                |                 |     1 |   115 |    51   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER               |                 |     1 |   106 |    50   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                    |                 |     1 |    97 |    49   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER             |                 |     1 |    90 |    49   (0)| 00:00:01 |
|   9 |          MERGE JOIN CARTESIAN          |                 |     1 |    85 |    49   (0)| 00:00:01 |
|  10 |           MERGE JOIN CARTESIAN         |                 |     1 |    82 |    49   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID | KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN           | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|  13 |            BUFFER SORT                 |                 |     1 |    67 |    47   (0)| 00:00:01 |
|* 14 |             TABLE ACCESS BY INDEX ROWID| KC21            |     1 |    67 |    47   (0)| 00:00:01 |
|* 15 |              INDEX RANGE SCAN          | IDX_KC21_PKA045 |    60 |       |     3   (0)| 00:00:01 |
|  16 |           BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 18 |          INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 19 |         INDEX UNIQUE SCAN              | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN                | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|* 21 |       INDEX RANGE SCAN                 | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


Plan hash value: 3791045646

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       | 43960 (100)|          |
|   1 |  SORT AGGREGATE                       |                 |     1 |       |            |          |
|   2 |   VIEW                                |                 |  1079 |       | 43960   (1)| 00:08:48 |
|   3 |    COUNT                              |                 |       |       |            |          |
|*  4 |     FILTER                            |                 |       |       |            |          |
|*  5 |      HASH JOIN OUTER                  |                 |  1079 |   121K| 43960   (1)| 00:08:48 |
|   6 |       NESTED LOOPS OUTER              |                 |  1079 |   111K| 43873   (1)| 00:08:47 |
|   7 |        NESTED LOOPS OUTER             |                 |  1079 |   102K| 43822   (1)| 00:08:46 |
|   8 |         MERGE JOIN CARTESIAN          |                 |  1079 | 99268 | 43822   (1)| 00:08:46 |
|   9 |          MERGE JOIN CARTESIAN         |                 |  1079 | 96031 | 43822   (1)| 00:08:46 |
|  10 |           NESTED LOOPS                |                 |     1 |    22 |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN          | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|  14 |           BUFFER SORT                 |                 |  1079 | 72293 | 43822   (1)| 00:08:46 |
|* 15 |            TABLE ACCESS BY INDEX ROWID| KC21            |  1079 | 72293 | 43820   (1)| 00:08:46 |
|* 16 |             INDEX RANGE SCAN          | IDX_KC21_PKA025 | 56948 |       |   192   (2)| 00:00:03 |
|  17 |          BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 19 |         INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN               | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|  21 |       INDEX FULL SCAN                 | IDX_KA06_AKA120 | 22655 |   199K|    86   (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

我们可以通过可以通过V$SQL_SHARED_CURSOR和来找出现有系统shared pool中存在的使用了Cardinality Feedback基数反馈的子游标

SQL>select sql_id,child_number,executions,loads,child_address from v$sql where sql_id='64q8v2p41c1vc';
SQL_ID        CHILD_NUMBER  EXECUTIONS	LOADS	CHILD_ADDRESS
------------- ------------  ----------  ----- -----------------
64q8v2p41c1vc 	1	            5	         3	  07000010AC4EAAE8
64q8v2p41c1vc	  2            	1	         1	  07000010AA093B00


SQL>select sql_id,address,child_address,child_number,user_feedback_stats
 from v$sql_shared_cursor where sql_id='64q8v2p41c1vc';
SQL_ID	      ADDRESS	      CHILD_ADDRESS	CHILD_NUMBER    USE_FEEDBACK_STATS
------------- ----------------      ----------------  ------------    ---------------
64q8v2p41c1vc	07000010AD35F368	07000010AC4EAAE8	1                Y
64q8v2p41c1vc	07000010AD35F368	07000010AA093B00	2                N

可以看到两个子游标的USE_FEEDBACK_STATS的值不一样,正是因这Cardinality Feedback基数反馈造成的

如果当这个特性产生更差的执行计划时可以考虑禁用这个特性
可以通过多种方法禁用该特性
1. 使用 _optimizer_use_feedback 隐藏参数 session 级别

SQL> alter session set “_optimizer_use_feedback”=false;
会话已更改。

system级别

SQL> alter system set “_optimizer_use_feedback”=false;
系统已更改。

2. 使用opt_param(‘_optimizer_use_feedback’ ‘false’) HINT在语句级进行禁用

select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’)*/ count(*) from jy;

如果要强制使用Cardinality Feedback可以使用cardinality HINT

select /*+ cardinality(jy,  1) */ count(*) from jy;

发表评论

电子邮件地址不会被公开。