最近系統上線，經常發現有ORDER BY操作的查詢緩慢，觀察執行計劃發現，優化器進行了ORDER BY 列上的索引INDEX FULL SCAN操作且執行計劃少了SORT ORDER BY操作。 SQL> select column_name from dba_ind_columns where index_name = 'I_DJXH_1';
COLUMN_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DJXH
SQL> select column_name from dba_ind_columns where index_name = 'I_FP_WLFP_KJXX_SWJG_DM';
COLUMN_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SWJG_DM
--原SQL，執行計劃對DJXH索引做了FULL SCAN,謂詞信息中索引未做任何訪問，返回表中才做了條件過濾，成本計算了1103K。一致性讀高達500W,但消除了排序 SQL> select ?* ?from hx_fp.fp_wlfp_kjxx a ? 2 ? ? ?where a.FPKJQK_DM = '10' ? ?and a.sfyyj = 'N' ?? 3 ? ? ?and a.swjg_dm = '25001050700' ? ?and a.sjgsdq like '250%' 4 ? ? order by a.djxh;
275188 rows selected.
Elapsed: 00:00:40.42
Execution Plan ---------------------------------------------------------- Plan hash value: 3647387228
-------------------------------------------------------------------------------------------- | Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? ? ? ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? | -------------------------------------------------------------------------------------------- | ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ? ? ? ?| ? 242K| ? ?42M| ?1103K ?(1)| 03:40:40 | |* ?1 | ?TABLE ACCESS BY INDEX ROWID| FP_WLFP_KJXX | ? 242K| ? ?42M| ?1103K ?(1)| 03:40:40 | | ? 2 | ? INDEX FULL SCAN ? ? ? ? ? | I_DJXH_1 ? ? | ?4756K| ? ? ? | ?5216 ? (1)| 00:01:03 | --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("A"."SWJG_DM"='25001050700' AND "A"."SFYYJ"='N' AND "A"."FPKJQK_DM"='10' AND "A"."SJGSDQ" LIKE '250%')

Statistics ---------------------------------------------------------- 1 ?recursive calls 0 ?db block gets 4879867 ?consistent gets 591 ?physical reads 3764 ?redo size 27501236 ?bytes sent via SQL*Net to client 202315 ?bytes received via SQL*Net from client 18347 ?SQL*Net roundtrips to/from client 0 ?sorts (memory) 0 ?sorts (disk) 275188 ?rows processed
--添加HINT，此時成本僅為61474，一致性讀明顯下降，耗時明顯減少,執行計劃出現sort order by操作。 SQL> select ?/*+index (a?I_FP_WLFP_KJXX_SWJG_DM?)*/* ?from hx_fp.fp_wlfp_kjxx a ? 2 ? ? ?where a.FPKJQK_DM = '10' ? ?and a.sfyyj = 'N' ?? 3 ? ? ?and a.swjg_dm = '25001050700' ? ?and a.sjgsdq like '250%' 4 ? ? order by a.djxh;
275188 rows selected.
Elapsed: 00:00:07.72
Execution Plan ---------------------------------------------------------- Plan hash value: 4137008994
--------------------------------------------------------------------------------------------------------------- | Id ?| Operation ? ? ? ? ? ? ? ? ? ?| Name ? ? ? ? ? ? ? ? ? | Rows ?| Bytes |TempSpc| Cost (%CPU)| Time ? ? | --------------------------------------------------------------------------------------------------------------- | ? 0 | SELECT STATEMENT ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ?| ? 242K| ? ?42M| ? ? ? | 61474 ? (1)| 00:12:18 | | ? 1 | ?SORT ORDER BY ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ?| ? 242K| ? ?42M| ? ?54M| 61474 ? (1)| 00:12:18 | |* ?2 | ? TABLE ACCESS BY INDEX ROWID| FP_WLFP_KJXX ? ? ? ? ? | ? 242K| ? ?42M| ? ? ? | 51626 ? (1)| 00:10:20 | |* ?3 | ? ?INDEX RANGE SCAN ? ? ? ? ?| I_FP_WLFP_KJXX_SWJG_DM | ? 261K| ? ? ? | ? ? ? | ? 373 ? (1)| 00:00:05 | ---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("A"."SFYYJ"='N' AND "A"."FPKJQK_DM"='10' AND "A"."SJGSDQ" LIKE '250%') 3 - access("A"."SWJG_DM"='25001050700')

Statistics ---------------------------------------------------------- 1 ?recursive calls 0 ?db block gets 115127 ?consistent gets 1 ?physical reads 0 ?redo size 27761560 ?bytes sent via SQL*Net to client 202315 ?bytes received via SQL*Net from client 18347 ?SQL*Net roundtrips to/from client 1 ?sorts (memory) 0 ?sorts (disk) 275188 ?rows processed
對查詢添加HINT強制使用正確索引后對比發現，成本計算明顯小于使用ORDER BY 列上的索引，CONSISTENT ?GET 也明顯減少。難道ORACLE 11G對ORDER BY 操作不計算成本了？這在以前9I中還從沒有發生過這樣的事情。于是帶著大大問號TRACE 10053事件，通過檢查10053事件發現了一個可疑的隱含參數： _sort_elimination_cost_ratio ? ? ? ?= 0 再往下接著看，發現當計算到掃描ORDER BY 列索引成本時出現了一個RECOST操作。
根據字面理解該參數為消除SORT操作的成本比例，查看對該參數相關文檔： ID 154354.1 Any ORDER BY clause can induce the index access When using an index access in the query can avoid a final sort, then this plan will be used. This heuristics can be fine tuned by setting the sort_elimination_cost_ratio init.ora. This cost ratio dictates how expensive a plan with ORDER BY sort elimination can be before it is rejected in favor of a cheaper plan which uses a sort. For example, a value of 5 means that a plan that avoids a sort may not be more than 5 times more expensive than a plan that does not avoid it. The default is 0 (no value) which means a plan with ORDER BY sort elimination will be chosen even if it is infinitely more expensive.
原來ORACLE11G默認認為SORT操作是高開銷操作，因此設置了該隱含參數控制當優化器遇到SORT ORDER BY 操作時是否避免該操作，該參數默認為0即消除執行計劃中的sort order by操作時。根據理解，當然要消除sort order by 操作要么就走TABLE FULL SCAN 要么就對排序列上的索引進行INDEX FULL SCAN后再進行過濾操作了。
嘗試修改該參數： SQL> alter session set "_sort_elimination_cost_ratio" = 5;
Session altered. Elapsed: 00:00:00.00 SQL> select ?* ?from hx_fp.fp_wlfp_kjxx a ? 2 ? ? ?where a.FPKJQK_DM = '10' ? ?and a.sfyyj = 'N' ?? 3 ? ? ?and a.swjg_dm = '25001050700' ? ?and a.sjgsdq like '250%' 4 ? ? order by a.djxh;
275188 rows selected.
Elapsed: 00:00:07.01
Execution Plan ---------------------------------------------------------- Plan hash value: 4137008994
--------------------------------------------------------------------------------------------------------------- | Id ?| Operation ? ? ? ? ? ? ? ? ? ?| Name ? ? ? ? ? ? ? ? ? | Rows ?| Bytes |TempSpc| Cost (%CPU)| Time ? ? | --------------------------------------------------------------------------------------------------------------- | ? 0 | SELECT STATEMENT ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ?| ? 242K| ? ?42M| ? ? ? | 61474 ? (1)| 00:12:18 | | ? 1 | ?SORT ORDER BY ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ?| ? 242K| ? ?42M| ? ?54M| 61474 ? (1)| 00:12:18 | |* ?2 | ? TABLE ACCESS BY INDEX ROWID| FP_WLFP_KJXX ? ? ? ? ? | ? 242K| ? ?42M| ? ? ? | 51626 ? (1)| 00:10:20 | |* ?3 | ? ?INDEX RANGE SCAN ? ? ? ? ?| I_FP_WLFP_KJXX_SWJG_DM | ? 261K| ? ? ? | ? ? ? | ? 373 ? (1)| 00:00:05 | ---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("A"."SFYYJ"='N' AND "A"."FPKJQK_DM"='10' AND "A"."SJGSDQ" LIKE '250%') 3 - access("A"."SWJG_DM"='25001050700')

Statistics ---------------------------------------------------------- 1 ?recursive calls 0 ?db block gets 115127 ?consistent gets 0 ?physical reads 0 ?redo size 27919470 ?bytes sent via SQL*Net to client 202315 ?bytes received via SQL*Net from client 18347 ?SQL*Net roundtrips to/from client 1 ?sorts (memory) 0 ?sorts (disk) 275188 ?rows processed

但通常我們在項目中要修改生產系統參數需要做大量的基準測試，如何在不修改該參數的情況下使執行計劃恢復到9I的行為呢？（即先過濾數據，再進行sort order by操作），要避免這種情況出現無非2種思想： 1、在order by列上使用函數或者連接一個空字符串避免優化器使用ORDER BY列上的索引。 2、使優化器執行計劃固定，即強制優化器先過濾數據，再進行ORDER BY操作。 測試寫法如下： SQL> alter session set "_sort_elimination_cost_ratio" = 0;
Session altered.
Elapsed: 00:00:00.00 --SQL1 SQL> select ?* ?from hx_fp.fp_wlfp_kjxx a ? 2 ? ? ?where a.FPKJQK_DM = '10' ? ?and a.sfyyj = 'N' ?? 3 ? ? ?and a.swjg_dm = '25001050700' ? ?and a.sjgsdq like '250%' 4 ? ? order by a.djxh || ' ';
275188 rows selected.
Elapsed: 00:00:07.94
Execution Plan ---------------------------------------------------------- Plan hash value: 4137008994
--------------------------------------------------------------------------------------------------------------- | Id ?| Operation ? ? ? ? ? ? ? ? ? ?| Name ? ? ? ? ? ? ? ? ? | Rows ?| Bytes |TempSpc| Cost (%CPU)| Time ? ? | --------------------------------------------------------------------------------------------------------------- | ? 0 | SELECT STATEMENT ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ?| ? 242K| ? ?42M| ? ? ? | 61474 ? (1)| 00:12:18 | | ? 1 | ?SORT ORDER BY ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ?| ? 242K| ? ?42M| ? ?54M| 61474 ? (1)| 00:12:18 | |* ?2 | ? TABLE ACCESS BY INDEX ROWID| FP_WLFP_KJXX ? ? ? ? ? | ? 242K| ? ?42M| ? ? ? | 51626 ? (1)| 00:10:20 | |* ?3 | ? ?INDEX RANGE SCAN ? ? ? ? ?| I_FP_WLFP_KJXX_SWJG_DM | ? 261K| ? ? ? | ? ? ? | ? 373 ? (1)| 00:00:05 | ---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("A"."SFYYJ"='N' AND "A"."FPKJQK_DM"='10' AND "A"."SJGSDQ" LIKE '250%') 3 - access("A"."SWJG_DM"='25001050700')

Statistics ---------------------------------------------------------- 1 ?recursive calls 0 ?db block gets 115127 ?consistent gets 0 ?physical reads 0 ?redo size 27919470 ?bytes sent via SQL*Net to client 202315 ?bytes received via SQL*Net from client 18347 ?SQL*Net roundtrips to/from client 1 ?sorts (memory) 0 ?sorts (disk) 275188 ?rows processed


--SQL2 SQL> with aa as (select ?/*+materialize*/* ?from hx_fp.fp_wlfp_kjxx a ? 2 ? ? ?where a.FPKJQK_DM = '10' ? ?and a.sfyyj = 'N' ?? 3 ? ? ?and a.swjg_dm = '25001050700' ? ?and a.sjgsdq like '250%') 4 ?select * from aa order by aa.djxh;
275188 rows selected.
Elapsed: 00:00:09.29
Execution Plan ---------------------------------------------------------- Plan hash value: 1442180058
--------------------------------------------------------------------------------------------------------------------- | Id ?| Operation ? ? ? ? ? ? ? ? ? ? | Name ? ? ? ? ? ? ? ? ? ? ? ?| Rows ?| Bytes |TempSpc| Cost (%CPU)| Time ? ? | --------------------------------------------------------------------------------------------------------------------- | ? 0 | SELECT STATEMENT ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? | ? 242K| ? ?55M| ? ? ? | 65977 ? (1)| 00:13:12 | | ? 1 | ?TEMP TABLE TRANSFORMATION ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?| | ? 2 | ? LOAD AS SELECT ? ? ? ? ? ? ?| SYS_TEMP_0FD9D66F1_BBE25862 | ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?| |* ?3 | ? ?TABLE ACCESS BY INDEX ROWID| FP_WLFP_KJXX ? ? ? ? ? ? ? ?| ? 242K| ? ?42M| ? ? ? | 51626 ? (1)| 00:10:20 | |* ?4 | ? ? INDEX RANGE SCAN ? ? ? ? ?| I_FP_WLFP_KJXX_SWJG_DM ? ? ?| ? 261K| ? ? ? | ? ? ? | ? 373 ? (1)| 00:00:05 | | ? 5 | ? SORT ORDER BY ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ? | ? 242K| ? ?55M| ? ?67M| 14351 ? (1)| 00:02:53 | | ? 6 | ? ?VIEW ? ? ? ? ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ? | ? 242K| ? ?55M| ? ? ? | ?1666 ? (1)| 00:00:20 | | ? 7 | ? ? TABLE ACCESS FULL ? ? ? ? | SYS_TEMP_0FD9D66F1_BBE25862 | ? 242K| ? ?42M| ? ? ? | ?1666 ? (1)| 00:00:20 | ---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter("A"."SFYYJ"='N' AND "A"."FPKJQK_DM"='10' AND "A"."SJGSDQ" LIKE '250%') 4 - access("A"."SWJG_DM"='25001050700')

Statistics ---------------------------------------------------------- 1010 ?recursive calls 9718 ?db block gets 122747 ?consistent gets 7429 ?physical reads 848 ?redo size 27761560 ?bytes sent via SQL*Net to client 202315 ?bytes received via SQL*Net from client 18347 ?SQL*Net roundtrips to/from client 1 ?sorts (memory) 0 ?sorts (disk) 275188 ?rows processed
以上兩種測試方案均通過，執行計劃恢復正常。

總結

以上是生活随笔為你收集整理的ORACLE 10G以后的ORDER BY操作优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。