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, the break point
for the parameter was 32/33 on my 10g system)
*/
set autotrace traceonly explain
alter session set "_sort_elimination_cost_ratio" = 34;
/*
Cost ratio set to 34 - PK path should be accepted
*/
select
*
from t1
where modded = 0
order by
id
;
alter session set "_sort_elimination_cost_ratio" = 33;
/*
Cost ratio set to 33 - PK NOSORT should be too expensive
*/
select
*
from t1
where modded = 0
order by
id
;
set autotrace off
spool off
参数“_sort_elimination_cost_ratio”含义如下（转自http://space.itpub.net/22034023/viewspace-716217）：
create table t1 as select * from dba_objects where object_id is not null;
alter table t1 add constraint t1_pk primary key(object_id);
create index t1_ind on t1(object_type);
alter session set optimizer_mode=first_rows;
alter session set "_sort_elimination_cost_ratio" = 0;
以上脚本创建了一张表，表的主键是object_id,表上有一个普通索引object_type.
set autotrace traceonly
select *
2 from t1
3 where object_type = 'TABLE'
4 order by
5 object_id
6 ;
2060 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=276 Card=423 Bytes=36378)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=276 Card=423 Bytes=36378)
2 1 INDEX (FULL SCAN) OF 'T1_PK' (UNIQUE) (Cost=23 Card=10986)
我们看到默认的执行计划走了主键索引的index full scan.算出来的cost是276.
select /*+ no_index(t1,t1_pk) */
2 *
3 from t1
4 where object_type = 'TABLE'
5 order by
6 object_id
7 ;
2060 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=46 Card=423 Bytes=36378)
1 0 SORT (ORDER BY) (Cost=46 Card=423 Bytes=36378)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=32 Card=423Bytes=36378)
3 2 INDEX (RANGE SCAN) OF 'T1_IND' (NON-UNIQUE) (Cost=1 Card=423)
通过为语句增加hint的方式，查询计划走了index RANGE scan.这个时候的cost是46.
select 276/46 from dual;
276/46
----------
6
默认的查询计划(INDEX pk full scan)的cost是走object_type索引查询计划的6倍。
我们通过设置_sort_elimination_cost_ratio的值小于6，准确点说，小于6大于0的整数都可以。
alter session set "_sort_elimination_cost_ratio" =5;
Session altered.
select
2 *
3 from t1
4 where object_type = 'TABLE'
5 order by
6 object_id
7 ;
2060 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=46 Card=423 Bytes=36378)
1 0 SORT (ORDER BY) (Cost=46 Card=423 Bytes=36378)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=32 Card=423Bytes=36378)
3 2 INDEX (RANGE SCAN) OF 'T1_IND' (NON-UNIQUE) (Cost=1 Card=423)
可以看到默认的执行计划已经走到了object_type索引的range scan.
如果设置参数_sort_elimination_cost_ratio的值大于6，那么查询计划依然会走主键列的index full scan.
alter session set "_sort_elimination_cost_ratio" =6/7(只要大等于6);
Session altered.
select
*
from t1
where object_type = 'TABLE'
order by
object_id
;
2060 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=276 Card=423 Bytes=36378)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=276 Card=423 Bytes=36378)
2 1 INDEX (FULL SCAN) OF 'T1_PK' (UNIQUE) (Cost=23 Card=10986)
可以看到 _sort_elimination_cost_ratio 的含义。
如果不走排序的成本/走排序的成本 > _sort_elimination_cost_ratio.那么执行计划会走排序。
如果不走排序的成本/走排序的成本 < _sort_elimination_cost_ratio.那么执行计划不会走排序。
_sort_elimination_cost_ratio=0是一个比较特殊的值，代表任何时候都要消减排序，即使排序的成本的是无穷大。
如果想取消这个特性，那么就把这个参数值设置成1。
有一点没想明白，这个参数值9I就有的。这个特性导致的问题是，走了耗费时间更长的index full scan.
如果这个特性存在问题，那么9I就应该存在问题，可是网上大多数网友遇到的问题是，升级到10G以后出现了这个问题。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。