摘自ITPUB的一篇帖子http://www.itpub.net/thread-1605027-1-1.html
Oracle 9I下optimizer_mode为first_rows,而此时的一个隐含参数_sort_elimination_cost_radit为0,也就是cbo会尽量避免sort,认为sort消耗巨大,此参数可能会引起执行计划的不合理,而在10g中该参数已经修改为1,也就是排序与不排序的成本是一样的。
SQL> alter session set optimizer_mode=first_rows;
Session altered
SQL> create table test01 as select * from dba_objects;
Table created
SQL> insert into test01 select * from dba_objects;
49745 rows inserted
SQL> insert into test01 select * from dba_objects;
49745 rows inserted
SQL> insert into test01 select * from dba_objects;
49745 rows inserted
SQL>
SQL> insert into test01 select * from dba_objects;
49745 rows inserted
SQL> commit;
Commit complete
SQL> create index index_a on test01(object_type);
Index created
SQL> create index index_b on test01(object_id);
Index created
SQL> alter ssession set "_sort_elimination_cost_ratio"=0;
Session altered
SQL> explain plan for select * from test01 where object_type='VIEW' and object_id between 800 and 56402 order by object_id;
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 769283169
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19090 | 3299K| 241K (1)| 00:
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST01 | 19090 | 3299K| 241K (1)| 00:
|* 2 | INDEX RANGE SCAN | INDEX_B | 251K| | 541 (2)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='VIEW')
2 - access("OBJECT_ID">=800 AND "OBJECT_ID"<=56402)
filter("OBJECT_ID">=800 AND "OBJECT_ID"<=56402)
Note
-----
- dynamic sampling used for this statement
20 rows selected
其中这个object_id已经返回了表中90%的数据,选择性很差,无论是走全表扫描还是利用object_type上的索引都会减小很多消耗,而由于设置_sort_elimination_cost_ratio=0 cbo会认为sort消耗巨大,而利用了低效率的索引
当不排序成本/排序成本>_sort_elimination_cost_ratio 执行计划走排序反之则不走排序,也就是当设置较大的_sort_elimination_cost_ratio或者不设置时,很有可能会导致不合理的执行计划,这也体现了oracle 10g中该参数默认为1的来由!(大多网友提到了9I升级到10G后,会频繁引起上述错误的执行计划,有点不解,应该是在9I后出现较多,而在10g后应该大量减少才是)
SQL> alter session set
2 "_sort_elimination_cost_ratio"=1;
Session altered
SQL> explain plan for select * from test01 where object_type='VIEW' and object_id between 800 and 56402 order by object_id;
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4262596394
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19090 | 3299K| | 1510 (2)| 00:00
| 1 | SORT ORDER BY | | 19090 | 3299K| 7848K| 1510 (2)| 00:00
|* 2 | TABLE ACCESS FULL| TEST01 | 19090 | 3299K| | 765 (3)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='VIEW' AND "OBJECT_ID">=800 AND
"OBJECT_ID"<=56402)
Note
-----
- dynamic sampling used for this statement
19 rows selected
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25362835/viewspace-1057986/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25362835/viewspace-1057986/