Optimizer Mode

From: Simon Palmer <si_g_palmer_at_yahoo.com>
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.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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 27 2012 - 10:10:44 CST