why is the optimizer doing this?
Date: Fri, 11 Feb 2011 08:59:50 -0500
Message-ID: <81BA3F0709F0B343AF28678897F2F3A301396D83_at_LOUMLVEM03.e2k.ad.ge.com>
If anyone who understand the optimizer better than me wants to wade through this and give me their opinion, I would be very grateful. Fair warning...this kind of long and involved
Platform: Oracle 10.2.0.4 on HP-UX (Itanium) (I've applied the 10.2.0.5 patchset thinking this might bug 7430474. Problem remained) Optimizer Mode: FIRST_ROWS
Table TEST1 has three columns
A - CHAR(8) - NOT NULL B - number(4) - NOT NULL C - CHAR(8) - NOT NULL
The primary key "TEST1_PK" is (A,B)
there is also a unique index "TEST1_I2" on (B,A)
The data in column A is a char representation of a date in 'YYYYMMDD' format. There are 10862 distinct values of A. For EACH value of A, there are 317 values of B. The data in C is irrelevant to this issue. So the data looks like.
A B
-------- -------
20110201 1 20110201 2 20110201 3 .... 20110201 316 20110201 317 20110202 1 20110202 2 20110202 3 ..... 20110202 316 20110202 317
etc
Given the query
select a, b, c from test1
where a = '20110210' and b > 10 (this query will return 307 rows)
I would expect range scan on the primary key (a,b) to be the optimal plan. And indeed, if I force the execution plan to use that index (which I can do by using a ALL_ROWS hint, a RULE hint, and INDEX hint or by removing the ORDER BY clause), it uses the PK and returns in about .2 seconds.
However, as executed with no hints, it returns in about 1.06 seconds, which would not be a big deal if we weren't running it somewhere between 3 to 5 times per second (with different parameters and using bind variables).
In order to try to understand why the optimizer was doing this I generated an EVENT 10053 trace file. Here are some relevant portions
BASE STATISTICAL INFORMATION
Table Stats::
Table: TEST1 Alias: TEST1
#Rows: 3413297 #Blks: 12585 AvgRowLen: 21.00 Index Stats::
Index: TEST1_I2 Col#: 2 1
LVLS: 2 #LB: 10822 #DK: 3413297 LB/K: 1.00 DB/K: 1.00 CLUF: 3408068.00
Index: TEST1_PK Col#: 1 2
LVLS: 2 #LB: 10834 #DK: 3413297 LB/K: 1.00 DB/K: 1.00 CLUF: 23542.00
OK, this looks about like I expected. The clustering factor is much better for the primary key, as I more or less expected based on the way the table is loaded.
Access Path: TableScan
Cost: 1145.44 Resp: 1145.44 Degree: 0
Cost_io: 987.00 Cost_cpu: 1523229875
Resp_io: 987.00 Resp_cpu: 1523229875
kkofmx: index filter:"TEST1"."A"='20110210' AND "TEST1"."B">10
Using density: 9.2064e-05 of col #1 as selectivity of unpopular value
pred
Access Path: index (RangeScan)
Index: TEST1_I2
resc_io: 10805.00 resc_cpu: 739184909 ix_sel: 0.97 ix_sel_with_filters: 8.9302e-05 Cost: 10881.89 Resp: 10881.89 Degree: 1 Using density: 9.2064e-05 of col #1 as selectivity of unpopular value pred
Access Path: index (RangeScan)
Index: TEST1_PK
resc_io: 6.00 resc_cpu: 161679
ix_sel: 8.9302e-05 ix_sel_with_filters: 8.9302e-05 Cost: 6.02 Resp: 6.02 Degree: 1
****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: TEST1_PK
Cost: 6.02 Degree: 1 Resp: 6.02 Card: 304.81 Bytes: 0
This looks good. It evaluated the Table Scan with a cost of 1145, a Range Scan of the TEST1_I2 index with a cost of 10881 and a Range Scan of the primary key with a cost of 6, and decided that the best path was a range scan of the PK.
Here's the part I don't understand. Immediately after the section above, it does this.
OPTIMIZER STATISTICS AND COMPUTATIONS
GENERAL PLANS
Considering cardinality-based initial join order. Permutations for Starting Table :0
Join order[1]: TEST1[TEST1]#0
Best so far: Table#: 0 cost: 6.0168 card: 304.8148 bytes: 6405 ****** Recost for ORDER BY (using index) ************
SINGLE TABLE ACCESS PATH
BEGIN Single Table Cardinality Estimation
Using density: 9.2064e-05 of col #1 as selectivity of unpopular value pred
Table: TEST1 Alias: TEST1
Card: Original: 3413297 Rounded: 305 Computed: 304.81 Non Adjusted: 304.81
END Single Table Cardinality Estimation
Access Path: TableScan
Cost: 1145.44 Resp: 1145.44 Degree: 0
Cost_io: 987.00 Cost_cpu: 1523229875
Resp_io: 987.00 Resp_cpu: 1523229875
kkofmx: index filter:"TEST1"."A"='20110210' AND "TEST1"."B">10
Using density: 9.2064e-05 of col #1 as selectivity of unpopular value
pred
Access Path: index (RangeScan)
Index: TEST1_I2
resc_io: 10805.00 resc_cpu: 739184909 ix_sel: 0.97 ix_sel_with_filters: 8.9302e-05 Cost: 10881.89 Resp: 10881.89 Degree: 1 Best:: AccessPath: IndexRange Index: TEST1_I2
Cost: 10881.89 Degree: 1 Resp: 10881.89 Card: 304.81 Bytes: 21
Notice that line up there that says
'****** Recost for ORDER BY (using index) ************' ??
It apparently is going to go through the calculations again and adjust
for the cost of the 'order by' clause. But it doesn't even consider
using the Primary Key!! It only evaluates the Table Scan and Range scan
of TEST_I2.
And chooses the more expensive of the two!!!
Are there other parts of the 10053 trace that I should be examining more closely and am just not seeing? Can anyone explain why it's doing this?
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 11 2011 - 07:59:50 CST