oracle 11g新特性Cardinality Feedback基數反饋造成同一sql幾乎同時執行產生不同的執行計劃

eric0435發表於2013-10-17
OracleSQL
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;
[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-1060966/，如需轉載，請註明出處，否則將追究法律責任。

相關文章