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