ORACLE 10G以后的ORDER BY操作优化

最新推荐文章于 2022-03-07 16:38:22 发布

ckklxjdw531374

最新推荐文章于 2022-03-07 16:38:22 发布

阅读量650

点赞数

文章标签：数据库

最近系统上线，经常发现有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

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 242K| 55M| | 65977 (1)| 00:13:12 |

| 1 | TEMP TABLE TRANSFORMATION | | | | | | |

|* 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

以上两种测试方案均通过，执行计划恢复正常。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23371754/viewspace-756858/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23371754/viewspace-756858/

ckklxjdw531374

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ORACLE 10G以后的ORDER BY操作优化

最近系统上线，经常发现有ORDER BY操作的查询缓慢，观察执行计划发现，优化器进行了ORDER BY 列上的索引INDEX FULL SCAN操作且执行计划少了SORT ORDER BY操作。SQL> sele...
复制链接

扫一扫