_sort_elimination_cost_radit和sort排序

摘自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/