Index Cost Calculation Mystery

  • From: Martin Klier <usn@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 26 Jun 2013 22:23:42 +0200

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 (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
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:
  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
         Cost: 94.49  Degree: 1  Resp: 94.49  Card: 0.00  Bytes: 0

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


Other related posts: