CBO，ORACLE，隱含引數，_sort_elimination_cost_ratio的含義

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=0是一個比較特殊的值，代表任何時候都要消減排序，即使排序的成本的是無窮大。
如果想取消這個特性，那麼就把這個引數值設定成1。
有一點沒想明白，這個引數值9I就有的。這個特性導致的問題是，走了耗費時間更長的index full scan.
如果這個特性存在問題，那麼9I就應該存在問題，可是網上大多數網友遇到的問題是，升級到10G以後出現了這個問題。