Oracle 排序优化

elliott_hu 发布于2019-07-23 16:39 / 2196人阅读

摘要：查询前十条记录通常我们都会已选择行。这样是错误的因为在中永远都是先执行的也就先取条再排序。正确的做法是使用子查询已选择行。没建索引已选择行。

2015年5月20日19:14:43 - 排序

1. 查看排序区内存的大小以及设置

实际排序所用到的内存、磁盘的统计信息：

pga_aggregate_target：此参数用来指定所有session总计可以使用最大PGA内存 olap:50% oltp:20%

sqlSQL> show parameter pga_aggre

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
pga_aggregate_target                 big integer            0
SQL> show parameter workarea_size_policy

workarea_size_policy：此参数用于开关PGA内存自动管理功能 auto:自动分配sort_area_size 属于workarea 如果需要经常排序就需要把这个值设置大点

sqlSQL> show parameter workarea_size_policy

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
workarea_size_policy                 string                 AUTO

select name, value from v$sysstat where name like ‘sort%’; select * from v$pgastat;

sqlSQL> select name , value from v$sysstat where name like "sort%";

NAME                                                                                                                          VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
sorts (memory)                                                                                                                 4283
sorts (disk)                                                                                                                      0
sorts (rows)                                                                                                                  40823

2. 比较以下操作
select * from customers;

sqlSQL> set autotrace traceonly
SQL> select * from customers;

已选择55500行。

已用时间:  00: 00: 01.93

执行计划
----------------------------------------------------------
Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 55500 |  9810K|   406   (1)| 00:00:05 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS | 55500 |  9810K|   406   (1)| 00:00:05 |
-------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5057  consistent gets
       1455  physical reads
          0  redo size
   10855625  bytes sent via SQL*Net to client
      41109  bytes received via SQL*Net from client
       3701  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      55500  rows processed

select * from customers order by cust_last_name;

sqlSQL> select * from customers order by cust_last_name;

已选择55500行。

已用时间:  00: 00: 01.93

执行计划
----------------------------------------------------------
Plan hash value: 2792773903

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           | 55500 |  9810K|       |  2612   (1)| 00:00:32 |
|   1 |  SORT ORDER BY     |           | 55500 |  9810K|    12M|  2612   (1)| 00:00:32 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 55500 |  9810K|       |   406   (1)| 00:00:05 |
----------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1459  consistent gets
       1454  physical reads
          0  redo size
    6278979  bytes sent via SQL*Net to client
      41109  bytes received via SQL*Net from client
       3701  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      55500  rows processed

可以看到使用order by用到了额外的12M内存
3. 在cust_last_name创建b*索引
没建索引：

sqlexplain plan for select  cust_last_name  from customers order by cust_last_name;

sqlSQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 2792773903

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           | 55500 |   433K|       |   610   (1)| 00:00:08 |
|   1 |  SORT ORDER BY     |           | 55500 |   433K|   880K|   610   (1)| 00:00:08 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 55500 |   433K|       |   405   (1)| 00:00:05 |
----------------------------------------------------------------------------------------

建立索引：

sqlcreate index lastname_idx on customers(cust_last_name);

sqlexplain plan for select /*+ index(c lastname_idx) */ cust_last_name  from customers order by cust_last_name;

执行计划：

sqlSQL> explain plan for select /*+ index(c lastname_idx) */ cust_last_name  from customers order by cust_last_name;

已解释。

已用时间:  00: 00: 00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 3470560620

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              | 55500 |   433K|   143   (1)| 00:00:02 |
|   1 |  INDEX FULL SCAN | LASTNAME_IDX | 55500 |   433K|   143   (1)| 00:00:02 |
---------------------------------------------------------------------------------

可以发现在一列建立索引后，对该列进行排序操作不需要再执行排序操作，
他会根据索引来进行查询(索引中本来就已经排好序)
结论

建立索引可以节省排序操作的时间。

4. 查询前十条记录
通常我们都会：

sqlselect cust_last_name from customers where rownum<=20 order by 1;

sqlSQL> select cust_last_name from customers where rownum<=20 order by 1;

CUST_LAST_NAME
--------------------------------------------------------------------------------
Everett
Everett
Everett
Everett
Everett
Everett
Everett
Everett
Everett
Ruddy
Ruddy

CUST_LAST_NAME
--------------------------------------------------------------------------------
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy

已选择20行。

这样是错误的,因为在oracle中where永远都是先执行的也就先取20条再排序。

正确的做法是使用子查询：

sqlselect cust_last_name from (select * from customers order by cust_last_name) where rownum<10;

sqlSQL>select cust_last_name from (select * from customers order by cust_last_name) where rownum<10;
CUST_LAST_NAME
--------------------------------------------------------------------------------
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron

CUST_LAST_NAME
--------------------------------------------------------------------------------
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron

已选择20行。

没建索引：

sqlPLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1285511559

---------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     9 |   198 |       |  2612   (1)| 00:00:32 |
|*  1 |  COUNT STOPKEY          |           |       |       |       |            |          |
|   2 |   VIEW                  |           | 55500 |  1192K|       |  2612   (1)| 00:00:32 |
|*  3 |    SORT ORDER BY STOPKEY|           | 55500 |  9810K|    12M|  2612   (1)| 00:00:32 |
|   4 |     TABLE ACCESS FULL   | CUSTOMERS | 55500 |  9810K|       |   406   (1)| 00:00:05 |
---------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   3 - filter(ROWNUM<10)

已选择17行。

建立索引后：

sqlPLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3026242074

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     9 |   198 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |              |       |       |            |          |
|   2 |   VIEW            |              |     9 |   198 |     4   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN| LASTNAME_IDX |     9 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)

已选择15行。

建立索引后,子查询效率明显提高

同时select cust_last_name from customers where rownum<=10 order by cust_last_name;和上面的结果已经大不相同。

sqlSQL> select cust_last_name from customers where rownum<=10 order by cust_last_name;

CUST_LAST_NAME
--------------------------------------------------------------------------------
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron

已选择10行。

执行计划：

sqlSQL> explain plan for
  2  select cust_last_name from customers where rownum<=10 order by cust_last_name;

已解释。

已用时间:  00: 00: 00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2820001957

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |    10 |    80 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY   |              |       |       |            |          |
|   2 |   INDEX FULL SCAN| LASTNAME_IDX | 55500 |   433K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM<=10)

已选择14行。

竟然不是刚刚那样乱的。
结论

建立索引能够对提高子查询的性能，并且查询前十条记录不再需要使用子查询，因为建立好索引后已经是排好序的，只要根据索引从表中拿出前十条记录即可。

5. 分组与索引
首先先建立一个表

sqlSQL> create table s as select * from sales;
表已创建。

s表与sales表的数据完全是一致的但s表没有sales表上的索引。

然后我们看一下执行计划：

sqlSQL> explain plan for
  2  select cust_id,avg(amount_sold) from s group by cust_id;

已解释。

已用时间:  00: 00: 00.23
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 1912481676

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   785K|    19M|  1271   (4)| 00:00:16 |
|   1 |  HASH GROUP BY     |      |   785K|    19M|  1271   (4)| 00:00:16 |
|   2 |   TABLE ACCESS FULL| S    |   785K|    19M|  1236   (1)| 00:00:15 |
---------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement (level=2)

已选择13行。

我们再看一下sales表（即建立了索引的表）:

sqlSQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2820001957

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |    10 |    80 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY   |              |       |       |            |          |
|   2 |   INDEX FULL SCAN| LASTNAME_IDX | 55500 |   433K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM<=10)

已选择14行。

结论

group by的一列上建立索引group by 性能更佳。

下面是关于order by 升序降序的执行计划可以看出建立索引升降序对排序不会影响性能
升序:

sqlSQL> select cust_last_name from customers where rownum <= 10 order by cust_last_name;
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2820001957

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |    10 |    80 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY   |              |       |       |            |          |
|   2 |   INDEX FULL SCAN| LASTNAME_IDX | 55500 |   433K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM<=10)

已选择14行。

降序：

sqlSQL> select cust_last_name from customers where rownum <= 10 order by cust_last_name;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 1596600344

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     9 |    72 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY              |              |       |       |            |          |
|   2 |   INDEX FULL SCAN DESCENDING| LASTNAME_IDX | 55500 |   433K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM<10)

已选择14行。

私有云服务器托管 oracle 排序优化插入排序java oracle 服务器优化 oracle_distinct优化

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/35012.html

上一篇：Oracle插入或修改报ORA-01704: 文字字符串太长

下一篇：配置 Oracle 11gR2 在 CentOS6 上开机自启动

相关文章

Oracle SQL性能优化

摘要：性能优化选择最有效率的表名顺序只在基于规则的优化器中有效的解析器按照从右到左的顺序处理子句中的表名，子句中写在最后的表基础表将被最先处理，在子句中包含多个表的情况下你必须选择记录条数最少的表作为基础表。 Oracle SQL性能优化（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中...

aboutU 2019-07-23 17:05 评论0 收藏0

Oracle SQL性能优化

摘要：性能优化选择最有效率的表名顺序只在基于规则的优化器中有效的解析器按照从右到左的顺序处理子句中的表名，子句中写在最后的表基础表将被最先处理，在子句中包含多个表的情况下你必须选择记录条数最少的表作为基础表。 Oracle SQL性能优化（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中...

yuanxin 2019-07-23 16:37 评论0 收藏0

Oracle数据库优化

摘要：封转数据库字段表回滚封装字段的查询执行过程，检查安全性，确保数据执行者有权限执行，检查语法，可能发生的查询重新书写，执行创建执行计划生产器接受经过解析的捆绑执行计划执行执行计划读取结果记录排序结果集执行级别数据访问方式，全表扫描顺序读取，知封转数据库字段 varray 表 CREATE OR REPLACE TYPE EMPLOYER_NAME AS OBJECT ( E_NAM...

Lycheeee 2019-07-23 17:47 评论0 收藏0

Oracle 排序优化

摘要：查询前十条记录通常我们都会已选择行。这样是错误的因为在中永远都是先执行的也就先取条再排序。正确的做法是使用子查询已选择行。没建索引已选择行。 2015年5月20日19:14:43 - 排序 1. 查看排序区内存的大小以及设置实际排序所用到的内存、磁盘的统计信息： pga_aggregate_target：此参数用来指定所有session总计可以使用最大PGA内存 olap:50...

pinecone 2019-07-23 17:06 评论0 收藏0

Oracle数据库优化

摘要：封转数据库字段表回滚封装字段的查询执行过程，检查安全性，确保数据执行者有权限执行，检查语法，可能发生的查询重新书写，执行创建执行计划生产器接受经过解析的捆绑执行计划执行执行计划读取结果记录排序结果集执行级别图片描述图片描述数据访问方式，全封转数据库字段 varray 表 CREATE OR REPLACE TYPE EMPLOYER_NAME AS OBJECT ( E_NAME...

big_cat 2019-07-23 16:52 评论0 收藏0

发表评论

登陆后可评论

0条评论

elliott_hu

男|高级讲师

我要关注我要私信

TA的文章
阅读更多

网站开启CDN能给网站带来哪些好处？

阅读 1460·2021-11-22 13:53

云、可持续性、数字化采用——2022 年亚太地区技术预测

阅读 2680·2021-11-15 18:10

C语言-常用字符函数详解+模拟实现

阅读 2594·2021-09-23 11:21

那些年踩过的坑——input输入框 ios端 readyonly 点击出现光标

阅读 2243·2019-08-30 15:55

【二次元的CSS】—— CSS3画的能换频道的电视机（合集）

阅读 358·2019-08-30 13:02

FE.ES-理解ECMA Javascript的this

阅读 643·2019-08-29 17:22

SegmentFault 技术周刊 Vol.38 - 神奇的 CSS

阅读 1380·2019-08-29 13:56

css 属性书写顺序推荐

阅读 3353·2019-08-29 11:31

云社区相关服务

提问学习认证产品技术服务售前咨询

最新活动

资讯专栏INFORMATION COLUMN

上云采购季！| 爆款云服务器低至4.6元/月，首单享0.9折

Oracle 排序优化

相关文章

Oracle SQL性能优化

Oracle SQL性能优化

Oracle数据库优化

Oracle 排序优化

Oracle数据库优化

发表评论

0条评论

elliott_hu

男|高级讲师

TA的文章

网站开启CDN能给网站带来哪些好处？

云、可持续性、数字化采用——2022 年亚太地区技术预测

C语言-常用字符函数详解+模拟实现

那些年踩过的坑——input输入框 ios端 readyonly 点击出现光标

【二次元的CSS】—— CSS3画的能换频道的电视机（合集）

FE.ES-理解ECMA Javascript的this

SegmentFault 技术周刊 Vol.38 - 神奇的 CSS

css 属性书写顺序推荐

云社区相关服务

最新活动