Hi
i need help in interpreting a trace file from the 10053 Event (Optimizer Trace)
All happens on AIX with Oracle 11.2.0.2
It is not obvious for me, why the optimizer chooses the access path with the higher cost.
But i found the following quote in the trace file, which is probably the answer, but i did not understand what circumstance let the optimizer say such things:
New AP rejected: guess used for New but not for Current Best
So, that was the short story, now the long one witch quotes from the trace file
Here the query directly from the trace file. I am unable to change this query, even a hint is not doable, so i need to understand the wrong access path to avoid it.
**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=0zdbb3hzztdaf) -----
explain plan for
SELECT
aktl_aend,
aktl_akgrnr,
aktl_akgrpos,
aktl_akh,
aktl_alterart,
aktl_anz_in,
aktl_anz_nach,
aktl_artrab,
aktl_beg,
aktl_belart1,
aktl_belart2,
aktl_belnr1,
aktl_belnr2,
aktl_bestkz,
aktl_bestmg1,
aktl_bestmg1_alt,
aktl_bestmg2,
aktl_bestmg2_alt,
aktl_eindat,
aktl_ek,
aktl_ekdat1,
aktl_ekdat2,
aktl_ekkopie,
aktl_ekn,
aktl_eknorm,
aktl_ek_alt,
aktl_end,
aktl_fbdarst,
aktl_fbd_alt,
aktl_fbseite,
aktl_fbs_alt,
aktl_fbtxt,
aktl_info_werbeabt,
aktl_info_wgm,
aktl_kaek,
aktl_kaekn,
aktl_kaek_alt,
aktl_kombiart,
aktl_ldat1,
aktl_ldat2,
aktl_lf,
aktl_lf_alt,
aktl_mgfk,
aktl_mg_p,
aktl_mg_r,
aktl_mindmg,
aktl_neudat,
aktl_nnek,
aktl_nnekn,
aktl_pabw,
aktl_pic,
aktl_plan_menge,
aktl_plan_menge_hela,
aktl_plan_stueck,
aktl_plan_stueck_hela,
aktl_plan_woche,
aktl_plan_woche_hela,
aktl_rdat,
aktl_ro_p,
aktl_ro_r,
aktl_seite,
aktl_sigkz,
aktl_sokoeur,
aktl_sokoeur_alt,
aktl_sokopr,
aktl_sokopr_alt,
aktl_sort_vorab,
aktl_stat,
aktl_text,
aktl_txtbau,
aktl_txtbaukz,
aktl_um_p,
aktl_um_r,
aktl_user,
aktl_vk,
aktl_vkn,
aktl_vk_alt,
aktl_zen,
akt_at_at_no,
akt_at_at_se_se_ag_ag_no,
akt_at_at_se_se_ag_ag_wg,
akt_at_at_se_se_no,
ROWID,
rownum,
uid,
USER,
vid
FROM
akt
WHERE
(
aktl_stat <> :"SYS_B_0"
)
AND
(
aktl_zen = :"SYS_B_1"
)
AND
(
aktl_akh BETWEEN :"SYS_B_2" AND :"SYS_B_3"
AND
(
(
akt_at_at_se_se_ag_ag_wg = :"SYS_B_4"
AND akt_at_at_se_se_ag_ag_no = :"SYS_B_5"
)
)
)
ORDER BY
akt_at_at_se_se_ag_ag_wg ASC,
akt_at_at_se_se_ag_ag_no ASC,
aktl_akh ASC,
aktl_akgrnr ASC,
aktl_akgrpos ASC,
aktl_kombiart ASC,
akt_at_at_se_se_no ASC,
akt_at_at_no ASC
*******************************************
And here is the strange part.
As far as i know, in this quote the optimizer realizes, that there is a better access path than the index AKT_INDEX, but it ignores it.
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: AKT Alias: AKT
#Rows: 14006583 #Blks: 477882 AvgRowLen: 281.00 ChainCnt: 570341.00
Index Stats::
Index: AKTL_UMNUM Col#: 44
LVLS: 2 #LB: 60137 #DK: 64171 LB/K: 1.00 DB/K: 179.00 CLUF: 11535830.00
Index: AKT_INDEX Col#: 2 3 4 5 1 6
LVLS: 3 #LB: 83897 #DK: 13955093 LB/K: 1.00 DB/K: 1.00 CLUF: 11889950.00
Index: AKT_INDEX010 Col#: 54 56
LVLS: 2 #LB: 37453 #DK: 392568 LB/K: 1.00 DB/K: 5.00 CLUF: 1127943.00
Index: AKT_INDEX011 Col#: 55 57
LVLS: 2 #LB: 33967 #DK: 2102 LB/K: 65.00 DB/K: 869.00 CLUF: 449507.00
Index: AKT_INDEX012 Col#: 2 3 4 5 6
LVLS: 2 #LB: 66503 #DK: 2506277 LB/K: 1.00 DB/K: 5.00 CLUF: 13420693.00
Index: FK_AKH_AKTL_AKH Col#: 1 6
LVLS: 2 #LB: 35870 #DK: 184651 LB/K: 1.00 DB/K: 6.00 CLUF: 1131183.00
Index: KEY_AKT Col#: 1 2 3 4 5 6
LVLS: 2 #LB: 82467 #DK: 13793510 LB/K: 1.00 DB/K: 1.00 CLUF: 11691370.00
Access path analysis for AKT
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for AKT[AKT]
Column (#6):
NewDensity:0.000000, OldDensity:0.000000 BktCnt:4202816, PopBktCnt:4202810, PopValCnt:133, NDV:139
Column (#6): AKTL_ZEN(
AvgLen: 4 NDV: 139 Nulls: 0 Density: 0.000000 Min: 1 Max: 61044
Histogram: Freq #Bkts: 139 UncompBkts: 4202816 EndPtVals: 139
Column (#2):
NewDensity:0.129575, OldDensity:0.000000 BktCnt:4201875, PopBktCnt:4201875, PopValCnt:2, NDV:2
Column (#2): AKT_AT_AT_SE_SE_AG_AG_WG(
AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.129575 Min: 6 Max: 7
Histogram: Freq #Bkts: 2 UncompBkts: 4201875 EndPtVals: 2
Column (#3):
NewDensity:0.000000, OldDensity:0.000000 BktCnt:4197711, PopBktCnt:4197711, PopValCnt:52, NDV:52
Column (#3): AKT_AT_AT_SE_SE_AG_AG_NO(
AvgLen: 3 NDV: 52 Nulls: 0 Density: 0.000000 Min: 6 Max: 99
Histogram: Freq #Bkts: 52 UncompBkts: 4197711 EndPtVals: 52
Column (#85): SYS_STUF1FT2E0X6B6C$__BGEZQRHS(
AvgLen: 12 NDV: 5571 Nulls: 0 Density: 0.000180 Min: 2117233 Max: 9997926970
Column (#84):
NewDensity:0.000000, OldDensity:0.000000 BktCnt:4201269, PopBktCnt:4201265, PopValCnt:71, NDV:75
Column (#84): SYS_STULHCKPI#FOQE1JMOOXNGSFB0(
AvgLen: 12 NDV: 75 Nulls: 0 Density: 0.000000 Min: 157628517 Max: 9982459671
Histogram: Freq #Bkts: 75 UncompBkts: 4201269 EndPtVals: 75
Column (#4):
NewDensity:0.000001, OldDensity:0.000000 BktCnt:4201338, PopBktCnt:4201338, PopValCnt:69, NDV:69
Column (#4): AKT_AT_AT_SE_SE_NO(
AvgLen: 3 NDV: 69 Nulls: 0 Density: 0.000001 Min: 1 Max: 98
Histogram: Freq #Bkts: 69 UncompBkts: 4201338 EndPtVals: 69
Column (#5): AKT_AT_AT_NO(
AvgLen: 4 NDV: 7466 Nulls: 0 Density: 0.000134 Min: 1 Max: 9964
Column (#1): AKTL_AKH(
AvgLen: 5 NDV: 16307 Nulls: 0 Density: 0.000061 Min: 14 Max: 543136
Column (#55):
NewDensity:0.000001, OldDensity:0.000000 BktCnt:4203610, PopBktCnt:4203610, PopValCnt:5, NDV:5
Column (#55): AKTL_BELART2(
AvgLen: 3 NDV: 5 Nulls: 0 Density: 0.000001
Histogram: Freq #Bkts: 5 UncompBkts: 4203610 EndPtVals: 5
Column (#57):
NewDensity:0.000002, OldDensity:0.000001 BktCnt:254, PopBktCnt:253, PopValCnt:1, NDV:2102
Column (#57): AKTL_BELNR2(
AvgLen: 3 NDV: 2102 Nulls: 0 Density: 0.000002 Min: 0 Max: 9902670
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 2
Column (#54):
NewDensity:0.000088, OldDensity:0.000000 BktCnt:4204429, PopBktCnt:4204429, PopValCnt:6, NDV:6
Column (#54): AKTL_BELART1(
AvgLen: 3 NDV: 6 Nulls: 0 Density: 0.000088
Histogram: Freq #Bkts: 6 UncompBkts: 4204429 EndPtVals: 6
Column (#56):
NewDensity:0.000000, OldDensity:0.000001 BktCnt:254, PopBktCnt:223, PopValCnt:1, NDV:392568
Column (#56): AKTL_BELNR1(
AvgLen: 3 NDV: 392568 Nulls: 0 Density: 0.000000 Min: 0 Max: 9904026
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 32
ColGroup (#5, Index) AKT_INDEX
Col#: 1 2 3 4 5 6 CorStregth: 8702149.74
ColGroup (#3, Index) AKT_INDEX012
Col#: 2 3 4 5 6 CorStregth: 2971.37
ColGroup (#1, VC) SYS_STUF1FT2E0X6B6C$__BGEZQRHS
Col#: 2 3 6 CorStregth: 2.59
ColGroup (#4, Index) FK_AKH_AKTL_AKH
Col#: 1 6 CorStregth: 12.28
ColGroup (#7, Index) AKT_INDEX010
Col#: 54 56 CorStregth: 6.00
ColGroup (#6, Index) AKT_INDEX011
Col#: 55 57 CorStregth: 5.00
ColGroup (#2, VC) SYS_STULHCKPI#FOQE1JMOOXNGSFB0
Col#: 2 3 CorStregth: 1.39
ColGroup Usage:: PredCnt: 3 Matches Full: #2 Partial: Sel: 0.0133
Column (#34):
NewDensity:0.000000, OldDensity:0.000000 BktCnt:4199313, PopBktCnt:4199311, PopValCnt:9, NDV:11
Column (#34): AKTL_STAT(
AvgLen: 2 NDV: 11 Nulls: 0 Density: 0.000000
Histogram: Freq #Bkts: 11 UncompBkts: 4199313 EndPtVals: 11
Table: AKT Alias: AKT
Card: Original: 14006583.000000 Rounded: 3 Computed: 3.05 Non Adjusted: 3.05
Access Path: TableScan
Cost: 131075.91 Resp: 131075.91 Degree: 0
Cost_io: 129428.00 Cost_cpu: 16864951683
Resp_io: 129428.00 Resp_cpu: 16864951683
kkofmx: index filter:"AKT"."AKTL_ZEN"=TO_NUMBER(:B1)
kkofmx: index filter:"AKT"."AKTL_AKH">=TO_NUMBER(:B1)
kkofmx: index filter:"AKT"."AKTL_AKH"<=TO_NUMBER(:B1)
kkofmx: index filter:"AKT"."AKTL_ZEN"=TO_NUMBER(:B1)
kkofmx: index filter:"AKT"."AKTL_ZEN"=TO_NUMBER(:B1)
kkofmx: index filter:"AKT"."AKTL_ZEN"=TO_NUMBER(:B1)
kkofmx: index filter:"AKT"."AKT_AT_AT_SE_SE_AG_AG_WG"=TO_NUMBER(:B1)
kkofmx: index filter:"AKT"."AKT_AT_AT_SE_SE_AG_AG_NO"=TO_NUMBER(:B1)
ColGroup Usage:: PredCnt: 2 Matches Full: #2 Partial: Sel: 0.0133
ColGroup Usage:: PredCnt: 3 Matches Full: #2 Partial: Sel: 0.0133
ColGroup Usage:: PredCnt: 3 Matches Full: #2 Partial: Sel: 0.0133
Access Path: index (RangeScan)
Index: AKT_INDEX
resc_io: 1128.00 resc_cpu: 45259416
ix_sel: 0.013333 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 150.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 151.08
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 151.13
***** End Logdef Adjustment ******
Cost: 567.59 Resp: 567.59 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: #2 Partial: Sel: 0.0133
ColGroup Usage:: PredCnt: 3 Matches Full: #2 Partial: Sel: 0.0133
ColGroup Usage:: PredCnt: 3 Matches Full: #2 Partial: Sel: 0.0133
Access Path: index (skip-scan)
SS sel: 0.000096 ANDV (#skips): 7466.000000
SS io: 7466.000000 vs. index scan io: 887.000000
Skip Scan rejected
Access Path: index (RangeScan)
Index: AKT_INDEX012
resc_io: 2177.00 resc_cpu: 54677577
ix_sel: 0.013333 ix_sel_with_filters: 0.000096
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 150.00
***** End Logdef Adjustment ******
Cost: 1092.54 Resp: 1092.54 Degree: 1
Access Path: index (RangeScan)
Index: FK_AKH_AKTL_AKH
resc_io: 201.00 resc_cpu: 15070140
ix_sel: 0.004500 ix_sel_with_filters: 0.000032
Cost: 101.24 Resp: 101.24 Degree: 1
New AP rejected: guess used for New but not for Current Best
ColGroup Usage:: PredCnt: 3 Matches Full: #2 Partial: Sel: 0.0133
ColGroup Usage:: PredCnt: 3 Matches Full: #2 Partial: Sel: 0.0133
Access Path: index (RangeScan)
Index: KEY_AKT
resc_io: 380.00 resc_cpu: 15131385
ix_sel: 0.004500 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 150.00
***** End Logdef Adjustment ******
Cost: 191.20 Resp: 191.20 Degree: 1
New AP rejected: guess used for New but not for Current Best
****** trying bitmap/domain indexes ******
Access Path: index (IndexOnly)
Index: FK_AKH_AKTL_AKH
resc_io: 164.00 resc_cpu: 13914716
ix_sel: 0.004500 ix_sel_with_filters: 0.000032
Cost: 82.68 Resp: 82.68 Degree: 0
SORT ressource Sort statistics
Sort width: 612 Area size: 1048576 Max Area size: 107366400
Degree: 1
Blocks to Sort: 1 Row size: 21 Total Rows: 252
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 10324700
Total Temp space used: 0
ColGroup Usage:: PredCnt: 2 Matches Full: #2 Partial: Sel: 0.0133
ColGroup Usage:: PredCnt: 2 Matches Full: #2 Partial: Sel: 0.0133
ColGroup Usage:: PredCnt: 2 Matches Full: #2 Partial: Sel: 0.0133
Access Path: index (IndexOnly)
Index: AKT_INDEX
resc_io: 1122.00 resc_cpu: 45203856
ix_sel: 0.013333 ix_sel_with_filters: 0.013333
Cost: 563.21 Resp: 563.21 Degree: 0
SORT ressource Sort statistics
Sort width: 612 Area size: 1048576 Max Area size: 107366400
Degree: 1
Blocks to Sort: 481 Row size: 21 Total Rows: 186754
Initial runs: 2 Merge passes: 1 IO Cost / pass: 262
Total IO sort cost: 743 Total CPU sort cost: 169417045
Total Temp space used: 3015000
Bitmap nodes:
Used FK_AKH_AKTL_AKH
Cost = 83.692016, sel = 0.000018
Not used AKT_INDEX
Cost = 1940.139796, sel = 0.013333
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: AKT_INDEX
Cost: 567.59 Degree: 1 Resp: 567.59 Card: 3.05 Bytes: 0
***************************************
So my question of course, why do i not get the access path via FK_AKH_AKTL_AKH?
Thx in advance
Joerg
Edited by: Joerg on Sep 23, 2011 5:05 PM