Optimizer Mode
Date: Fri, 27 Jan 2012 08:10:44 -0800 (PST)
Message-ID: <1327680644.17010.YahooMailNeo_at_web161502.mail.bf1.yahoo.com>
Hello,
Just been doing some SPA work, just setting up and testing against some Swingbench SQLs. Did a trial of adjusting the optimizer_mode from ALL_ROWS to FIRST_ROWS.
The SQL improved noticebly in terms of execution time, CPU costing rose... Anyway, I'll get to the point.
The SQL is:
SELECT count(order_id,
customer_id) FROM orders WHERE order_status <= 4 AND warehouse_id = :B1 AND ROWNUM < 10
What really interested me was the Estimated rows in the execution plans (taken from lib cache):
ALL_ROWS:
<snip>
| 6 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 10 | 7 (0)| 9 |00:00:00.04 | 13 | 81 |
|* 7 | INDEX RANGE SCAN | ORD_WAREHOUSE_IX | 1 | 2114 | 2 (0)| 9 |00:00:00.02 | 4 | 17 |
</snip>
FIRST_ROWS:
<snip>
| 6 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 1996 | 1026 (1)| 9 |00:00:00.02 | 13 | 81 |
|* 7 | INDEX RANGE SCAN | ORD_WAREHOUSE_IX | 1 | 2114 | 4 (0)| 9 |00:00:00.02 | 4 | 17 |
</snip>
The rest of the plan for each execution was exactly the same, but the ESTIMATED rows for ALL_ROWS was 10 and for FIRST_ROWS was 1996.
Between each run I reset the environment (flush etc...) if only to allow the 53on to work.
The cardinality estimates for were 1996:
~53 trace: Note: This entry is seen in both FIRST_ROW and ALL_ROW ~53 traces.
Table: ORDERS Alias: ORDERS
Card: Original: 4500000.000000 Rounded: 1996 Computed: 1996.43
So, I can see where 1996 in the second run came from... (the 1996 were not accurate anyway, but that's an accuracy of stats digression)....
When one considers the "ROWNUM < 10" predicate why then, did the FIRST_ROWS appear to get the estimate wrong and the ALL_ROWS get it spot on?
Looking at the ~53 traces I can see the ALL_ROWS session seems to have 2 "OPTIMIZER STATISTICS AND COMPUTATIONS" sections (compared to 1 in FIRST_ROWS) and in the second of those sections it has the following:
<snip> ALL_ROWS session ~53 trace
Best so far: Table#: 0 cost: 1025.9149 card: 1996.4330 bytes: 37924
First K Rows: non adjusted N = 1996.00, sq fil. factor = 1.000000 First K Rows: K = 9.00, N = 1996.00 First K Rows: old pf = -1.0000000, new pf = 0.0050089Access path analysis for ORDERS
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for ORDERS[ORDERS]
Table: ORDERS Alias: ORDERS Card: Original: 22541.000000 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00 Access Path: TableScan Cost: 51.10 Resp: 51.10 Degree: 0 Cost_io: 50.00 Cost_cpu: 9390626 Resp_io: 50.00 Resp_cpu: 9390626
</snip>
So, in an environment where ~mode set to ALL_ROWS there appears to be some decisions made around "First K Rows" and in the env where the ~mode is set to FIRST_ROWS there isn't. And in the former, it gets the ESTIMATE right, in the latter it doesn't...
So even though in the FIRST_ROWS environment there is acknowledgement of the ROWNUM condition:
<snip> FIRST_ROWS session ~53 trace
Trying or-Expansion on query block SEL$1 (#0) Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 fptrnum predicate=ROWNUM<10 id=0 frofkks[i] (index start key) predicate="ORDERS"."WAREHOUSE_ID"=:B1 id=0 frofkke[i] (index stop key) predicate="ORDERS"."WAREHOUSE_ID"=:B1 id=0 frofkke[i] (index stop key) predicate="ORDERS"."ORDER_STATUS"<=4Final cost for query block SEL$1 (#0) - First Rows Plan: Best join order: 1
Cost: 1025.9149 Degree: 1 Card: 1996.0000 Bytes: 37924
</snip>
.....it doesn't seemed to be "used" in the cardinality estimates...
I see from http://dioncho.wordpress.com/2009/01/30/89/ that "Oracle converts ROWNUM predicate to first_rows mode internally. " but if we're using FIRST_ROWS explicitly why doesn't it come back with the same ESTIMATE?
Anyone got any comments?
Oracle 11.1.0.7 BTW...
Thanks,
Simon
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 27 2012 - 10:10:44 CST