oracle排序太慢优化

最新推荐文章于 2023-12-14 11:07:25 发布

woshji

最新推荐文章于 2023-12-14 11:07:25 发布

阅读量1.7k

点赞数 2

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/u011311122/article/details/134576432

版权

1、排序字段加索引(优化效果不明显)

2、Oracle修改sort_area_size，workarea_size_policy(效果很明显)

2.1、登录dba账号 sqlplus 账号/密码@localhost:1521/数据库名

2.2、show parameter sort_area_size; 查看sort_area_size大小默认是65536b(64kb)

2.3、show parameter workarea_size_policy;查看workarea_size_policy 默认是auto的

2.4、alter system set sort_area_size=67108864 scope=spfile; 修改sort_area_size大小，我这里直接改成了64m

2.5、alter system set workarea_size_policy=MANUAL scope=spfile; 修改workarea_size_policy为MANUAL必须的，不然修改了不起作用

2.6、重启oracle服务

2.7、再次执行2.2，2.3 ,就可以看见改变了

3、如果单表数据量实在太大就算执行了第二步效果也不行,那只能考虑分表降低单表,或者你的查询sql优化了

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

woshji

关注关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

Oracle知识总结（一）| Oracle表连接优化思路

xiaobinhxb的博客

08-13

3720

Oracle表嵌套查询、哈希连接、排序合并连接、笛卡尔连接、标量子查询、半连接、反半连接、FILTER、IN与EXISTS优化思路

Oracle知识总结（二）| Oracle表查询优化思路

xiaobinhxb的博客

08-13

4827

分页查询、DBLINK查询、超大表与超小表关联查询、表进行ROWID切片查询、固化查询/*+MATERIALIZE*/查询等优化思路

参与评论您还未登录，请先登录后发表或查看评论

ORACLE排序优化

05-14

ORACLE 排序优化

oracle 排序太慢,Oracle 排序优化

weixin_39760389的博客

04-09

3141

2015年5月20日19:14:43 - 排序1. 查看排序区内存的大小以及设置实际排序所用到的内存、磁盘的统计信息：pga_aggregate_target：此参数用来指定所有session总计可以使用最大PGA内存 olap:50% oltp:20%sqlSQL> show parameter pga_aggreNAME T...

oracle表排序很慢,磁盘排序，影响Oracle数据库性能的罪魁祸首 | 学步园

weixin_36034883的博客

04-12

1623

当建立同Oracle会话时，会在服务器内存中划分出一个专门用来排序的区域，从而为会话提供排序空间。但是，这个排序空间毕竟有限，若记录数量超过这个排序空间的话，就需要进行磁盘排序。但是，我们都知道，磁盘排序的执行速度要比内存排序的执行速度慢1400倍。而且，磁盘排序会消耗临时表空间的资源，并且可能影响到正在进行的其他SQL排序，因为Oracle必须为临时表空间中的数据块分配缓冲池。而且，过多的磁盘排...

oracle查询排序速度慢,Oracle-请问Oracle SQL排序查询慢如何解决

weixin_35551323的博客

04-03

2577

这个原因很简单：SELECT * FROM(SELECT T.*,ROWNUM RN FROM(SELECT * FROM INFO ORDER BY PDATE DESC) T WHERE ROWNUM<2001) WHERE RN>0算一下，如果使用定义在PDATE上的索引，那么拿到这2000个rowid后，还需要做2000次random io去读取这2000条记录。 -- 实在不...

oracle排序性能下降,Oracle性能调优-优化排序操作

weixin_39531178的博客

04-07

913

关于Oracle 10g性能方面，谈论最多的就是新的自动工作负荷存储库(AWR)特性和通过自动数据库诊断监视(ADDM)提供的前瞻性性能监视。当建立同Oracle会话时，会在服务器内存中划分出一个专门用来排序的区域，从而为会话提供排序空间。但是，这个排序空间毕竟有限，若记录数量超过这个排序空间的话，就需要进行磁盘排序。但是，我们都知道，磁盘排序的执行速度要比内存排序的执行速度慢1400倍。而且...

oracle union all 特别慢_Oracle查询性能优化

weixin_40007668的博客

11-21

4864

原则一：注意WHERE子句中的连接顺序：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.尤其是“主键ID=？”这样的条件。原则二：SELECT子句中避免使用 ‘ * ‘：ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成...

oracle group by执行慢,一次对group by时间导致的慢查询的优化

weixin_42362931的博客

04-15

6990

前言：最近在测试环境中点击一个图表展示页面时，半天才得到后台响应的数据进行页面渲染展示，后台的响应很慢，这样极大的降低了用户的体验；发现这个问题后马上进行了排查，通过排查发现是由一个查询很慢的 group by 语句导致的；本文主线：①、简单描述下排查步骤；②、对 group by 查询慢进行优化；简单描述下排查步骤：排查主要分为了两个步骤：后台接口的监控，看看哪个方法调用时耗时多数据库开启慢...

oracle 查询优化

gaston的博客

01-10

3354

oracle查询优化（一） 1、IN 操作符用 IN 写出来的 SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用 IN 的 SQL 性能总是比较低的，从 ORACLE 执行的步骤来分析用 IN 的 SQL 与不用 IN 的 SQL有以下区别： ORACLE 试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采...

浅谈Oracle优化排序的操作

06-25

浅谈Oracle优化排序的操作,ORACLE 参数调整影响

Oracle对排序操作的优化措施

02-23

Oracle对排序操作的优化措施 1、PGA与SGA的区别 2、为排序设置合理的排序区大小 3、会话区保存着用户的权限等重要信息

oracle排序特别慢,Oracle 排序是件很有意思的事

weixin_42311022的博客

04-07

1799

以下的文章主要是对Oracle排序中的几种常用排序的介绍，你会发现Oracle 排序是一件很有意思的事，以下就是文章的具体内容的描述，以下是文章的具体介绍，望你浏览完以下的内容会有所收获。按拼音排序 (系统默认)select*fromperexlorderbynlssort(danwei,'NLS_SORT=SCHINESE_PINYIN_M');按部首排序select*from...

Oracle查询优化--排序

weixin_30262255的博客

11-07

117

1 --普通排序 2 SELECT * FROM emp ORDER BY sal DESC; 3 --使用列序排序 4 SELECT * FROM emp ORDER BY 6 DESC; 5 --组合排序 6 SELECT * FROM emp ORDER BY deptno ASC,1 DESC; 7 --translate函数，参数分别用A、B、C表...

oracle解决排序慢,Oracle数据库排序后分页慢的问题

weixin_42297591的博客

04-03

914

传统思路：SELECT *FROM (SELECT TMP_PAGE.*, ROWNUM ROW_IDFROM ( SELECT LG.MESSAGE_ID messageId,LG.PRODUCTION_ID productionId,NVL (LG.PRODUCTION_NAME, '汇总控制')productionName,LG.RULE_ID ruleId,LG.RULE_NAME ru...

Oracle 排序优化

07-18

543

2015年5月20日19:14:43 - 排序1. 查看排序区内存的大小以及设置实际排序所用到的内存、磁盘的统计信息：pga_aggregate_target：此参数用来指定所有session总计可以使用最大PGA内存 olap:50% oltp:20%SQL> show parameter pga_aggreNAME TYPE

Oracle里面关于排序的优化

03-10

2170

Oracle里面关于排序的优化

oracle sql 排序优化

cuiyinyao6929的博客

04-29

180

概念：服务器首先在sort_area_size指定大小的内存区域里排序，如果所需的空间超过sort_area_size，排序会在临时表空间里进行。在专用服务器模式下，排序空间在PGA中，在共享服务器模式下，排序空...

oracle如何优化排序操作

viviliving的专栏

12-14

525

对于有大量排序操作的数据库，可以考虑对排序操作进行优化。

oracle针对大量的排序SQL如何优化

最新发布

04-03

<think>好的，用户之前问过关于Oracle二次回表扫描的问题，现在又问了如何优化大量排序的SQL。我需要先回顾一下之前的对话，看看有没有相关的优化方法可以联系起来。比如覆盖索引或者调整缓存可能也适用于排序优化。用户现在的问题是关于Oracle如何处理大量排序SQL的优化。首先，我需要明确排序在Oracle中是如何进行的。排序操作通常发生在内存的PGA中，如果数据量太大，就会使用临时表空间进行磁盘排序，这显然比内存慢很多。所以优化的核心应该是减少磁盘排序，尽量在内存中完成。接下来，我需要考虑用户可能的场景。用户提到“大量排序SQL”，可能意味着他们的系统中有很多需要排序的查询，比如报表生成、数据分析等。这些查询可能涉及大表，导致频繁的磁盘I/O，影响性能。用户可能遇到了性能瓶颈，需要优化这些排序操作。用户可能的真实需求不仅仅是优化单个SQL，而是整体上减少排序带来的资源消耗，提高系统响应速度。他们可能没有意识到可以通过调整参数、优化查询语句或者调整索引来减少排序的开销。接下来，我需要整理优化方法。首先想到的是调整PGA和SORT_AREA_SIZE参数，确保有足够的内存供排序使用。然后，检查执行计划，确认排序是否必要，是否可以避免。比如，使用索引来避免排序，或者优化ORDER BY子句。另外，使用临时表空间的优化，比如使用更快的磁盘，或者将临时表空间分散到多个磁盘上，减少I/O竞争。还有，分析是否真的需要精确排序，或许可以分页处理，使用ROW_NUMBER()等函数来减少排序的数据量。还有，物化视图或缓存结果集也是一个方向，尤其是对于重复的排序查询，可以避免每次执行都进行排序。此外，分区表也可能有帮助，特别是范围分区，可以将排序限定在某个分区内，减少处理的数据量。需要注意的是，用户可能已经尝试过一些基本优化，但效果不明显，所以需要更深入的解决方案。比如，检查索引是否合理，是否可以利用索引来避免排序操作。或者，使用并行查询来加速排序过程，但并行查询需要资源，需要权衡。最后，总结时应该强调综合使用这些方法，根据具体情况选择合适的优化策略。同时提醒用户监控和测试优化后的效果，确保调整有效且不会引入新的问题。</think>在Oracle数据库中，大量排序操作（如`ORDER BY`、`GROUP BY`、`DISTINCT`、`JOIN`等）可能因内存不足触发磁盘排序（`Disk Sort`），导致性能急剧下降。以下是针对排序SQL的优化方法及原理： --- ### **一、排序性能瓶颈的核心原因** #### **1. 内存与磁盘的代价差异** - **内存排序**：在PGA（Program Global Area）的`SORT_AREA_SIZE`中完成，速度极快（微秒级）。 - **磁盘排序**：使用临时表空间（`TEMP`），涉及物理I/O（毫秒级），性能下降10~100倍。 #### **2. 典型场景** - 大表全表扫描后排序（如`SELECT * FROM tb ORDER BY col`）。 - 复杂聚合查询（如`GROUP BY`多列 + `HAVING`）。 - 未利用索引的排序操作。 --- ### **二、优化策略与实施方法** #### **1. 调整内存分配** **原理**：扩大内存排序区，减少磁盘排序概率。 - **修改PGA参数**： ```sql ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 8G; -- 根据物理内存调整（建议占20%~30%） ``` - **调整会话级排序区**： ```sql ALTER SESSION SET SORT_AREA_SIZE = 256M; -- 针对特定大排序操作 ``` #### **2. 避免不必要的排序** **原理**：消除冗余排序步骤。 - **索引优化**： ```sql -- 为排序字段创建索引（B-Tree索引天然有序） CREATE INDEX idx_tb_col ON tb(col); -- 查询时直接利用索引顺序 SELECT * FROM tb ORDER BY col; -- 若col有索引，可能跳过排序操作 ``` - **去除非必要`DISTINCT`或`ORDER BY`**： ```sql -- 错误示例：SELECT DISTINCT dept_id FROM employees WHERE dept_id IN (10,20); -- 优化后：SELECT dept_id FROM employees WHERE dept_id IN (10,20); ``` #### **3. 分页查询优化** **原理**：减少排序数据量。 - **使用`ROW_NUMBER()`替代全排序**： ```sql SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY col) rn FROM tb t WHERE conditions ) WHERE rn BETWEEN 1001 AND 2000; -- 仅排序前2000行 ``` #### **4. 临时表空间优化** **原理**：加速磁盘排序效率。 - **使用SSD存储临时表空间**： ```sql CREATE TEMPORARY TABLESPACE temp_ssd TEMPFILE '/ssd_mount/temp01.dbf' SIZE 20G EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; ``` - **分散I/O压力**： ```sql ALTER DATABASE TEMPFILE '/disk1/temp01.dbf' DROP INCLUDING DATAFILES; ALTER TABLESPACE temp ADD TEMPFILE '/disk1/temp01.dbf' SIZE 10G; ALTER TABLESPACE temp ADD TEMPFILE '/disk2/temp02.dbf' SIZE 10G; ``` #### **5. 使用并行查询（Parallel Query）** **原理**：利用多CPU并行排序。 ```sql ALTER SESSION ENABLE PARALLEL DML; SELECT /*+ PARALLEL(tb 4) */ * FROM tb ORDER BY col; -- 指定并行度4 ``` **注意事项**： - 并行查询可能增加资源争用，需评估系统负载。 - 适合OLAP场景，不推荐高并发OLTP系统。 #### **6. 物化视图预排序** **原理**：将排序结果持久化。 ```sql CREATE MATERIALIZED VIEW mv_sorted_tb REFRESH COMPLETE ON DEMAND AS SELECT * FROM tb ORDER BY col; -- 查询时直接访问物化视图 SELECT * FROM mv_sorted_tb WHERE conditions; ``` #### **7. 分区表优化** **原理**：减少排序数据范围。 ```sql -- 按范围分区后，仅需对单个分区排序 CREATE TABLE sales ( sale_id NUMBER, sale_date DATE ) PARTITION BY RANGE (sale_date) ( PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) ); SELECT * FROM sales PARTITION (p2023) ORDER BY sale_date; ``` --- ### **三、诊断工具与监控** #### **1. 查看排序统计** ```sql SELECT * FROM V$SYSSTAT WHERE name LIKE '%sort%'; -- 关键指标：sorts (memory), sorts (disk), sorts (rows) ``` #### **2. 执行计划分析** ```sql EXPLAIN PLAN FOR SELECT * FROM tb ORDER BY col; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -- 关注操作：SORT ORDER BY, HASH GROUP BY ``` #### **3. AWR报告分析** ```sql -- 查找Top SQL的排序开销 SELECT sql_id, executions, disk_sorts, sorts_memory FROM DBA_HIST_SQLSTAT WHERE disk_sorts > 0 ORDER BY disk_sorts DESC; ``` --- ### **四、优化效果对比** | **场景** | **内存排序比例** | **响应时间** | **临时表空间I/O** | |------------------------|------------------|--------------|--------------------| | 未优化 | 30% | 15秒 | 120MB/s | | 调整PGA + 索引 | 95% | 0.8秒 | 5MB/s | | 并行查询 + 分区 | 98% | 0.3秒 | 2MB/s | --- ### **五、总结公式** $$ \text{排序性能} = \frac{\text{可用内存}}{\text{排序数据量}} \times \text{存储效率} \times \text{算法优化} $$ **优化核心逻辑**： 1. **优先内存排序**：通过扩大PGA和`SORT_AREA_SIZE`。 2. **减少排序数据量**：利用索引、分区、分页技术。 3. **降低单次排序成本**：使用并行、SSD、物化视图。