Re: Index Cost Calculation Mystery

  • From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
  • To: usn@xxxxxxxxx
  • Date: Thu, 27 Jun 2013 00:28:39 +0400

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@xxxxxxxxx> 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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>


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


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


Other related posts: