Optimizer 10053 trace file, strange choises made by Oracle??
Date: Thu, 10 Dec 2009 12:21:10 -0800 (PST)
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.
(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