RE: why is the optimizer doing this?
Date: Fri, 11 Feb 2011 14:39:32 +0000
Message-ID: <853BE8E3785A554D92010F1FB6C0B279AE3E9BD8_at_LDNPCMMGMB11.INTRANET.BARCAPINT.COM>
Optimizer mode of FIRST_ROWS was deprecated in 10g, no? Or was it 10gR2 when it was officially deprecated?
Anyway, on page 76 of "Cost-based Oracle Fundamentals", Jonathan Lewis says "One of the critical features of first_rows optimization ... was the existence of a few rules for overriding normal costing behaviour. One such rule was that if there was an index that could be used to avoid a sort, then the optimizer would use it - apparently, regardless of how expensive the path might be".
So, one of the reasons why FIRST_ROWS is now deprecated in favour of FIRST_ROWS_N (where n = 1,10,100 or 1000).
See also 10gR1 Performance Tuning Guide
http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/optimops.htm#38218
" FIRST_ROWS
The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead."
Hope this helps.
Cheers,
Dominic
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Adams, Matthew (GE, Appl & Light)
Sent: 11 February 2011 14:00
To: oracle-l_at_freelists.org
Subject: why is the optimizer doing this?
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-l _______________________________________________ This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Barclays. Any views or opinions presented are solely those of the author and do not necessarily represent those of Barclays. This e-mail is subject to terms available at the following link: www.barcap.com/emaildisclaimer. By messaging with Barclays you consent to the foregoing. Barclays Capital is the investment banking division of Barclays Bank PLC, a company registered in England (number 1026167) with its registered offic e at 1 Churchill Place, London, E14 5HP. This email may relate to or be sent from other members of the Barclays Group. _______________________________________________ -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 11 2011 - 08:39:32 CST