Optimizer Mode
- From: Simon Palmer <si_g_palmer@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Jan 2012 08:10:44 -0800 (PST)
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.0050089
Access 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"<=4
Final 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
