CBO学习之一What do you mean by Cost
CBO学习之一What do you mean by Cost
Mr. Lewis代码如下，注释部分略作改动：
[sql]
/*
Script: first_rows.sql
Author: Jonathan Lewis
Dated: June 2002
Purpose: Problems with FIRST_ROWS optimisation
Last tested
10.1.0.4
9.2.0.6
8.1.7.4
The FIRST_ROWS optimizer does not have a sensible strategy
for dealing with optimisation of an ORDER BY (and possibly
other variants such as GROUP BY) when it finds it can use
an index to do the ORDER BY "free of charge".
This example demonstrates the point. Without the in-line
view, Oracle does a full scan on the primary key index to
return the 100 required rows in order, at a total cost
and total execution time that is much higher than that of
using the required index.
Of course, it is arguably the case that this is correct
behaviour if we assume that the time to the first row is
important, and therefore we avoid collecting a large number
of rows and sorting them. In practice, this is perhaps not
really likely to be the case.
Bug number 670328 applies
Parameter _sort_elimination_cost_ratio affects the break
point between optimising for the WHERE clause and optimising
for the ORDER BY clause.
If the parameter is zero, the ORDER BY clause takes precedence
If the parameter is non-zero, then the index is used if the cost
of using it is less than the cost of doing the sort multiplied
by the value of the parameter.
Special note for 10g:
The parameter still has the same effect in general, but zero
now means zero, not (effectively) infinity. The default of
zero will now effectively ignore the index option unless it
is actually cheaper than the non-index option. A non-zero
value will behave as it always used to
*/
--start setenv
drop table t1;
begin
begin execute immediate 'purge recyclebin';
exception when others then null;
end;
begin execute immediate 'begin dbms_stats.delete_system_stats; end;';
exception when others then null;
end;
begin execute immediate 'alter session set "_optimizer_cost_model"=io';
exception when others then null;
end;
end;
/
create table t1 as
select
rownum id,
-- mod(rownum,100) modded,
mod(rownum,300) modded,
lpad(rownum,1000) padding
from
all_objects
where
rownum <= 10000
;
alter table t1 add constraint t1_pk primary key(id);
create index t1_mod on t1(modded);
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
alter session set optimizer_mode=first_rows;
set autotrace traceonly explain
spool first_rows
/*
Get a base-line cost and plan for acquiring the rows
*/
select *
from t1
where modded = 0
;
/*
See what happens if we add an 'order by primary key'.
*/
select *
from t1
where modded = 0
order by
id
;
/*
Block the PK index from being used for ordering, and see
that Oracle could notionally get a better path. Strangely
the cost varies depending on the strategy used for blocking
the index. On my 9.2.0.6 test, the no_merge hint managed to
reduce the lengths of the rows to be sorted, and therefore
calculated a smaller cost.
*/
/*
Block the index with a no_merge hint
*/
select * from (
select /*+ no_merge */
*
from t1
where modded = 0
)
order by
id
;
/*
Block the index with a no_index hint
*/
select /*+ no_index(t1,t1_pk) */
*
from t1
where modded = 0
order by
id
;
set autotrace off
/*
The costs under 8i are:
Using the PK index to avoid the sort: 1,450
Block the PK index (no_index) and sorting: 43
Cost ratio: 1450/43 = 33.729, so we test
_sort_elimination_cost_ratio at 33 and 34
At 33: 43 * 33 = 1,419: so the PK nosort should be ignored
At 34: 43 * 34 = 1,462: so the PK nosort falls inside the limit.
(Because of a change in the cost in 10g, t
