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;