_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@]