Optimizer 10053 trace file, strange choises made by Oracle??

From: HansP <Hans-Peter.Sloot_at_atosorigin.com>
Date: Thu, 10 Dec 2009 12:21:10 -0800 (PST)
Message-ID: <b4ec3efd-18cd-4fa5-afbc-e97d127c856f_at_d20g2000yqh.googlegroups.com>

Hi all,

I had a 10053 trace file generated of a Siebel query and I do not understand the SINGLE TABLE ACCESS PATH part. Below is a snippet of the tracefile.

It seems to calculate the costs of a full tablescan as 1988.76. Then the different index access paths are calculated which are calculated as 215.78 and 236.68 respectively. At the end to my surprise it says "Best:: AccessPath: TableScan" with cost 1988.76.

Can someone shed some light on this. Why is the TableScan chosen whereas the index accesses have a lower cost.

Regards HansP

(Oracle version 10.2.0.4, optimizer_index_cost_adj = 10)

SINGLE TABLE ACCESS PATH

---

  BEGIN Single Table Cardinality Estimation

---

  Table: S_ORG_EXT_LSX Alias: T13
    Card: Original: 784404 Rounded: 784404 Computed: 784404.00 Non Adjusted: 784404.00

---

  END Single Table Cardinality Estimation

---

  Access Path: TableScan
    Cost: 1988.76 Resp: 1988.76 Degree: 0

      Cost_io: 1947.00  Cost_cpu: 817088980
      Resp_io: 1947.00  Resp_cpu: 817088980

• trying bitmap/domain indexes ****** Access Path: index (FullScan) Index: S_ORG_EXT_LSX_P1 resc_io: 2149.00 resc_cpu: 171334375 ix_sel: 1 ix_sel_with_filters: 1 Cost: 215.78 Resp: 215.78 Degree: 0 Access Path: index (FullScan) Index: S_ORG_EXT_LSX_U1 resc_io: 2358.00 resc_cpu: 172822756 ix_sel: 1 ix_sel_with_filters: 1 Cost: 236.68 Resp: 236.68 Degree: 0
• finished trying bitmap/domain indexes ****** Best:: AccessPath: TableScan Cost: 1988.76 Degree: 1 Resp: 1988.76 Card: 784404.00 Bytes: 0

Received on Thu Dec 10 2009 - 14:21:10 CST