Re: optimizer strange index decision

Thanks a lot Jonathan and Sayan, I completely missed this detail . Indeed this was the issue: sample size for stats collection (resulted from autosample size) created this misestimate.

Most probably I`ll try to keep just the composite index and drop the other one(or set sample size to 100 for this table if I get change resistance) .

The interesting part which drove this investigation was actually a DOP downgrade situation where for a cross-instance execution slaves were not able to replicate the same plan like coordinator and I mananged to track (via plan_hash_value in ash) the diference in the execution plans was in regards with these 2 indexes. The situation is unfortunately very rare and I`m not able to replicate it at all for further tracing, but I hope fixing the index situation might avoid this bug.

Thanks again.

În joi, 17 mar. 2022 la 17:19, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> The stats may be up to date, but they're not accurate enough.
> An index on (col2) cannot possibly have more distinct keys that an index
> on (col2, col3).
>
> The difference in cost between the two indexes is tiny but consistent with
> the size of the error in the two sets of index stats.
> As a quick check test you could do a compute / 100% sample on the two
> indexes to see if the change the choice.
>
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
> On Thu, 17 Mar 2022 at 14:43, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> wrote:
>
>> Hello everyone,
>>
>> I have a situation where a table has 2 indexes, one index on a column
>> which is used usually in joins and another composite index on 2 columns
>> with the leading column the one used in joins.
>>
>> Based on above the normal choice will be any time when this table is
>> joined via a NL to use the index on the single column and not the composite
>> one, but of course in my situation is not happening this and I cannot
>> figure it out why. Based on below the CPU cost is slightly lower for the
>> composite one:
>>
>> Stats are up to date for table and indexes. The version is 12.1. Any idea
>> what can be the issue here?
>>
>> Thanks a lot.
>>
>> Index Stats::
>>
>> Index: WRONG_INDEX Col#: 2 3
>>
>> LVLS: 2 #LB: 11009 #DK: 1344098 LB/K: 1.00 DB/K: 1.00 CLUF:
>> 1291070.00 NRW: 1344098.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR:
>> 0.00 KQDFLG: 1 BSZ: 1
>>
>> KKEISFLG: 1
>>
>> Index: RIGHT_INDEX Col#: 2
>>
>> LVLS: 2 #LB: 5926 #DK: 1346451 LB/K: 1.00 DB/K: 1.00 CLUF:
>> 1289850.00 NRW: 1346451.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR:
>> 0.00 KQDFLG: 1 BSZ: 1
>>
>> KKEISFLG: 1
>>
>>
>>
>> ***************
>>
>> Now joining: TABLE2[PA]#1
>>
>> ***************
>>
>> *NL Join*
>>
>> Outer table: Card: 880.239436 Cost: 130366373.435402 Resp:
>> 130366373.435402 Degree: 1 Bytes:
>>
>> Access path analysis for TABLE2
>>
>> Scan IO Cost (Disk) = 7983.355682
>>
>> Scan CPU Cost (Disk) = 577749676.880000
>>
>> Column (#4):
>>
>> NewDensity:0.000002, OldDensity:0.000002 BktCnt:5699.000000,
>> PopBktCnt:638.000000, PopValCnt:15, NDV:581248
>>
>> Column (#4): TABLE1_COL4(VARCHAR2)
>>
>> AvgLen: 14 NDV: 581248 Nulls: 0 Density: 0.000002
>>
>> Histogram: Hybrid #Bkts: 254 UncompBkts: 5699 EndPtVals: 254
>> ActualVal: yes
>>
>> Total Scan IO Cost = 7983.355682 (scan (Disk))
>>
>> + 0.000000 (io filter eval) (= 0.000000 (per
>> row) * 1362337.000000 (#rows))
>>
>> = 7983.355682
>>
>> Total Scan CPU Cost = 577749676.880000 (scan (Disk))
>>
>> + 68117688.325578 (cpu filter eval) (= 50.000615
>> (per row) * 1362337.000000 (#rows))
>>
>> = 645867365.205578
>>
>> * Inner table: TABLE2 Alias: PA*
>>
>> Access Path: TableScan
>>
>> NL Join: Cost: 137406040.135845 Resp: 137406040.135845 Degree: 1
>>
>> Cost_io: 135725125.000000 Cost_cpu: 66745175095067
>>
>> Resp_io: 135725125.000000 Resp_cpu: 66745175095067
>>
>> ******* Costing Index WRONG_INDEX*
>>
>> SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
>>
>>
>>
>> SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
>>
>>
>>
>> Access Path: index (RangeScan)
>>
>> Index: WRONG_INDEX
>>
>> resc_io: 3.000000 *resc_cpu: 23522*
>>
>> ix_sel: 7.3403e-07 ix_sel_with_filters: 7.3403e-07
>>
>> NL Join : Cost: 130369013.956686 Resp: 130369013.956686 Degree: 1
>>
>> Cost_io: 128702412.000000 *Cost_cpu: 66176832512680*
>>
>> Resp_io: 128702412.000000 Resp_cpu: 66176832512680
>>
>> ******* Costing Index RIGHT_INDEX*
>>
>> SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
>>
>> SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
>>
>> Access Path: index (AllEqJoinGuess)
>>
>> Index: RIGHT_INDEX
>>
>> resc_io: 3.000000 *resc_cpu: 23523*
>>
>> ix_sel: 7.3403e-07 ix_sel_with_filters: 7.3403e-07
>>
>> NL Join : Cost: 130369013.956719 Resp: 130369013.956719 Degree: 1
>>
>> Cost_io: 128702412.000000 *Cost_cpu: 66176832513970*
>>
>> Resp_io: 128702412.000000 Resp_cpu: 66176832513970
>>
>

--
http://www.freelists.org/webpage/oracle-l