Re: Index Cost Calculation Mystery

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 27 Jun 2013 00:28:39 +0400
Message-ID: <CAOVevU6dmw+eciCfSe2+ws_N+JUoiMizwTn44KxnjP3yMGprmw_at_mail.gmail.com>

Martin,
look at selectivity:
0.000034 - for IDX2_AUFTRAG
0.000241 - IDX7_AUFTRAG Best regards,
Sayan Malakshinov
http://orasql.org

On Thu, Jun 27, 2013 at 12:23 AM, Martin Klier <usn_at_usn-it.de> wrote:

> Hi list,
> for me it's a mystery, but maybe somebody here can bring light into it.
> It's about cost calculation for two indexes in database version
> 11.2.0.3.0 (no interim patches) on Linux x86_64.
>
> Changed parameters (all time, not only during this issue):
> optimizer_index_cost_adj = 50
> statistics_level = all
>
> We have a dynamic SQL (don't scold me for not using binds, I'm the DBA,
> not the app designer):
>
> SELECT auftragId, clientId, kundeId, arsAnrufNr, arsAuftragsNr,
> arsQueueId, apothekenIdf, dategAuftragsNr, auftragsTyp,
> auftragSammelTyp, auftragSammelTourId, knzAuftragSammelnErlaubt,
> knzDispo, knzBTM, knzDefektmelden, knzRueckRuf, knzHeuteKeinAuftrag,
> knzZustellungHeute, knzZustellungMorgen, knzSelbstabholer, knzDienst,
> auftragsErfassungsTyp, lieferDat, startErfassung, endeErfassung,
> knzKontrolle, knzChargenKontrolle, knzPackungOk, nettoAuftragsWert,
> mehrwertSteuer, valutaDat, textLieferschein, hinweisText, status,
> extAuftragsNr, extKundenNr, extPruefziffer, reserved, finDat, finInfo,
> genDat, genInfo, modDat, modInfo
> FROM OPS$SCM.Auftrag
> WHERE arsAuftragsNr = '000327'
> AND substr(geninfo,1,3) = '000237'
> AND trunc(gendat) > trunc(sysdate-1 )
> ORDER BY gendat;
>
> The optimizer resolves the access to table AUFTRAG via index range scan,
> and table access by index rowid.
>
> We have two nonunique indexes to choose from:
> IDX2_AUFTRAG (columns arsAuftragsNr and dategAuftragsNr) (BLevel 2,
> 21180 leaf blocks, clustering factor 5395080)
> IDX7_AUFTRAG (column arsAuftragsNr) (Blevel 2, 16870 leaf blocks,
> clustering factor 4221470)
>
>
> The Optimizer chooses IDX2_AUFTRAG for the index range scan, but I can't
> explain why. The 10053 trace tells:
> ======10053==========
> Access Path: index (RangeScan)
> Index: IDX2_AUFTRAG
> resc_io: 186.00 resc_cpu: 1585830
> ix_sel: 0.000034 ix_sel_with_filters: 0.000034
> Cost: 94.49 Resp: 94.49 Degree: 1
> Access Path: index (AllEqRange)
> Index: IDX7_AUFTRAG
> resc_io: 1027.00 resc_cpu: 8999338
> ix_sel: 0.000241 ix_sel_with_filters: 0.000241
> Cost: 521.97 Resp: 521.97 Degree: 1
> ...
> ...
> Best:: AccessPath: IndexRange
> Index: IDX2_AUFTRAG
> Cost: 94.49 Degree: 1 Resp: 94.49 Card: 0.00 Bytes: 0
> ======10053==========
>
> But why is the cost of a AllEqRange on a single column index
> (IDX7_AUFTRAG) 521.97 and an index range scan on a multi-column index
> (IDX2_AUFTRAG) has only cost 94.49?
>
> By all I know IDX7_AUFTRAG should be the better choice. How can I find
> out why cost is calculated this way?
>
> If oracle-l can handle attachments, you will find the full 10053 trace
> including an execution plan with predactes, a "select * from
> user_indexes" for the two segments mentioned above, and an "opatch
> -lsinventory" version information with this mail.
>
> Again for clarification: I am not interested in a solution for this very
> case, my priority is to understand what I'm missing here.
>
> Thanks a lot in advance
> Martin Klier
> --
> Usn's IT Blog for Linux and Oracle
> http://www.usn-it.de
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org


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

Received on Wed Jun 26 2013 - 22:28:39 CEST