Index Cost Calculation Mystery

From: Martin Klier <usn_at_usn-it.de>
Date: Wed, 26 Jun 2013 22:23:42 +0200
Message-ID: <51CB4DCE.8020606_at_usn-it.de>

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

Received on Wed Jun 26 2013 - 22:23:42 CEST