2. 首页 > Oracle教程 > 正文

Oracle教程FG303-Oracle排序优化实战

教程整理：风哥教程  |  更新时间：2026-01-10  |  教程分类：Oracle教程  |  文档学习：4

内容大纲

内容简介：本文主要介绍Oracle数据库的排序优化，包括排序的原理、类型、监控和优化方法。风哥教程参考Oracle官方文档排序优化相关内容，为生产环境提供完整的排序优化解决方案。

Part01-基础概念与理论知识

1.1 排序概念

Oracle排序是指Oracle数据库对数据进行排序操作的过程，主要用于ORDER BY、GROUP BY、DISTINCT、UNION等操作。排序操作需要使用临时表空间作为工作空间，当排序数据量较大时，会使用磁盘空间进行排序，这会影响查询性能。

1.2 排序类型

  • 内存排序：当排序数据量较小，能够在PGA中完成排序
  • 磁盘排序：当排序数据量较大，需要使用临时表空间进行排序
  • 混合排序：当排序数据量介于内存和磁盘之间，先在内存中排序，然后使用磁盘完成排序

1.3 排序优化方法

  • 优化SQL语句：减少排序操作，使用索引避免排序
  • 调整PGA大小：增加PGA大小，提高内存排序的能力
  • 调整临时表空间：增加临时表空间大小，提高磁盘排序的性能
  • 使用并行排序：对于大表排序，使用并行排序提高性能
  • 监控排序性能：定期监控排序性能，及时发现问题

Part02-生产环境规划与建议

2.1 排序优化规划

制定合理的排序优化规划：

  • 评估数据库的排序需求
  • 分析数据库的负载情况
  • 制定排序优化策略
  • 建立排序优化的流程和规范
  • 定期执行排序优化
  • 跟踪排序优化的效果

2.2 排序优化建议

排序优化建议：

  • 优化SQL语句，减少排序操作
  • 使用索引避免排序
  • 调整PGA大小，提高内存排序的能力
  • 调整临时表空间大小，提高磁盘排序的性能
  • 对于大表排序，使用并行排序提高性能
  • 定期监控排序性能，及时发现问题

2.3 排序优化结果管理

排序优化结果管理建议：

  • 保存排序优化的历史数据
  • 建立排序优化的审核机制
  • 跟踪排序性能的变化趋势
  • 分析排序性能的瓶颈
  • 与开发团队分享排序优化结果，提高应用程序性能

Part03-生产环境项目实施方案

3.1 排序优化配置与管理

# 1. 调整PGA大小
SQL> ALTER SYSTEM SET pga_aggregate_target=4G SCOPE=spfile;

# 2. 调整临时表空间大小
SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/oracle/data/temp02.dbf’ SIZE 20G;

# 3. 启用并行排序
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> ALTER SESSION ENABLE PARALLEL QUERY;

# 4. 设置并行度
SQL> ALTER SESSION SET parallel_degree_policy=AUTO;

# 5. 优化SQL语句
# 使用索引避免排序
CREATE INDEX idx_fgedu_orders_amount ON fgedu.fgedu_orders(amount);

3.2 排序监控

# 1. 查看排序统计信息
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%sort%’;

# 2. 查看会话的排序使用情况
SQL> SELECT * FROM v$sort_usage;

# 3. 查看排序相关的等待事件
SQL> SELECT * FROM v$session_wait WHERE event LIKE ‘%sort%’;

# 4. 查看PGA使用情况
SQL> SELECT * FROM v$pgastat;

# 5. 查看临时表空间使用情况
SQL> SELECT * FROM v$sort_segment;

# 6. 查看SQL执行计划中的排序操作
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.fgedu_orders ORDER BY amount DESC;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

3.3 排序优化

# 1. 分析排序性能
# 查看排序统计信息和等待事件

# 2. 优化SQL语句
# 减少排序操作，使用索引避免排序

# 3. 调整PGA大小
# 根据排序需求调整PGA大小
SQL> ALTER SYSTEM SET pga_aggregate_target=6G SCOPE=spfile;

# 4. 调整临时表空间大小
# 根据排序需求调整临时表空间大小
SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/oracle/data/temp03.dbf’ SIZE 30G;

# 5. 使用并行排序
# 对于大表排序，使用并行排序提高性能
SQL> ALTER SESSION SET parallel_degree_policy=AUTO;

# 6. 验证调优效果
# 查看调优后的排序性能

3.4 排序优化结果管理

# 1. 保存排序优化历史数据
# 将排序使用情况保存到表中，用于后续分析

# 2. 建立排序优化的审核机制
# 定期审核排序使用情况，确保排序资源的合理使用

# 3. 跟踪排序性能的变化趋势
# 分析排序性能的变化趋势，预测排序需求

# 4. 分析排序性能的瓶颈
# 识别排序性能的瓶颈，采取相应的措施

# 5. 与开发团队分享排序优化结果
# 提供排序优化结果给开发团队，帮助优化应用程序

Part04-生产案例与实战讲解

4.1 排序优化配置与管理实战

# 1. 连接数据库
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Apr 4 11:00:00 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

# 2. 查看当前排序统计信息
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%sort%’;

STATISTIC# NAME CLASS VALUE
———- —————————————- ———- ———-
150 sorts (memory) 2 123456
151 sorts (disk) 2 1234
152 sorts (rows) 2 12345678

# 3. 调整PGA大小
SQL> ALTER SYSTEM SET pga_aggregate_target=4G SCOPE=spfile;

System altered.

# 4. 调整临时表空间大小
SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/oracle/data/temp02.dbf’ SIZE 20G;

Tablespace altered.

# 5. 启用并行排序
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> ALTER SESSION ENABLE PARALLEL QUERY;
SQL> ALTER SESSION SET parallel_degree_policy=AUTO;

4.2 排序监控与分析实战

# 1. 执行排序操作
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.fgedu_orders ORDER BY amount DESC;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
Plan hash value: 1234567890

———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 100000| 10M| | 123 (100)| 00:00:01 |
| 1 | SORT ORDER BY | | 100000| 10M| 100M| 123 (100)| 00:00:01 |
| 2 | TABLE ACCESS FULL| FGEDU_ORDERS| 100000| 10M| | 45 (2)| 00:00:01 |
———————————————————————————————————-

# 2. 执行排序操作并记录时间
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.fgedu_orders ORDER BY amount DESC;

100000 rows selected.

Elapsed: 00:00:02.34

# 3. 查看排序使用情况
SQL> SELECT * FROM v$sort_usage;

USERNAME SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE CONTENTS SEGFILE SEGBLK EXTENTS BLOCKS
—————————— —————- ———- —————- ———- ————- —————————— ——— ———- ———- ———- ———-
FGEDU 000000007F0A12C0 1234 000000007F0B1234 1234567890 1234567890abcdef TEMP TEMPORARY 2 128 8 64

# 4. 查看排序相关的等待事件
SQL> SELECT * FROM v$session_wait WHERE event LIKE ‘%sort%’;

no rows selected

4.3 排序优化实战

# 1. 分析排序性能
# 查看排序统计信息和等待事件

# 2. 创建索引避免排序
SQL> CREATE INDEX idx_fgedu_orders_amount ON fgedu.fgedu_orders(amount);

Index created.

# 3. 验证索引是否避免排序
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.fgedu_orders ORDER BY amount DESC;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
Plan hash value: 9876543210

———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 100000| 10M| 100 (1)| 00:00:01 |
| 1 | INDEX FULL SCAN DESCENDING| IDX_FGEDU_ORDERS_AMOUNT| 100000| 10M| 100 (1)| 00:00:01 |
———————————————————————————————————-

# 4. 执行排序操作并记录时间
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.fgedu_orders ORDER BY amount DESC;

100000 rows selected.

Elapsed: 00:00:00.45

# 执行时间减少：2.34s → 0.45s

# 5. 调整PGA大小
SQL> ALTER SYSTEM SET pga_aggregate_target=6G SCOPE=spfile;

System altered.

# 6. 重启数据库使参数生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 7. 执行大表排序操作
SQL> CREATE TABLE fgedu.large_table AS SELECT * FROM fgedu.fgedu_orders;
SQL> INSERT INTO fgedu.large_table SELECT * FROM fgedu.large_table;
SQL> /
SQL> /
SQL> /
SQL> COMMIT;

# 8. 执行大表排序操作并记录时间
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.large_table ORDER BY amount DESC;

800000 rows selected.

Elapsed: 00:00:05.67

# 9. 启用并行排序
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> ALTER SESSION ENABLE PARALLEL QUERY;
SQL> ALTER SESSION SET parallel_degree_policy=AUTO;

# 10. 执行大表排序操作并记录时间
SQL> SET TIMING ON
SQL> SELECT /*+ PARALLEL(8) */ * FROM fgedu.large_table ORDER BY amount DESC;

800000 rows selected.

Elapsed: 00:00:02.12

# 执行时间减少：5.67s → 2.12s

Part05-风哥经验总结与分享

5.1 排序优化最佳实践

  • 优化SQL语句：减少排序操作，使用索引避免排序
  • 调整PGA大小：增加PGA大小，提高内存排序的能力
  • 调整临时表空间：增加临时表空间大小，提高磁盘排序的性能
  • 使用并行排序：对于大表排序，使用并行排序提高性能
  • 定期监控：定期监控排序性能，及时发现问题

5.2 排序优化注意事项

  • 确保PGA有足够的空间
  • 确保临时表空间有足够的空间
  • 使用索引避免排序，提高查询性能
  • 对于大表排序，使用并行排序提高性能
  • 定期监控排序性能，及时发现问题

5.3 排序优化建议

  • 建立排序优化流程，定期执行排序性能分析
  • 培训DBA，提高排序优化能力
  • 建立排序优化结果的审核机制
  • 跟踪排序性能的变化趋势
  • 与开发团队分享排序优化结果，提高应用程序性能

更多视频教程www.fgedu.net.cn

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息