Oracle 关于sort_area_size参数的设置

最新推荐文章于 2025-07-07 12:49:43 发布

原创最新推荐文章于 2025-07-07 12:49:43 发布 · 8.5k 阅读

12 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

69 篇文章

订阅专栏

本文介绍Oracle数据库中的排序优化方法，重点讲解sort_area_size参数的作用及其调整策略。通过合理配置此参数，可以显著提升数据库查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 关于sort_area_size参数的设置

sort_area_size是当查询需要排序的时候，数据库会话将使用这部分内存进行排序，当内存大小不足的时候，使用临时表空间进行磁盘排序。由于磁盘排序效率和内存排序效率相差好几个数量级，所以这个参数的设置很重要。
当出现大量排序时的磁盘I/O操作时，可以考虑增加sort_area_size的值。sort_area_size是Oracle用于一次排序所需的最大内存数，在排序结束但是结果列返回之前，Oracle会释放sort_area_size大小的内存，但是会保留sort_area_retained_size大小的内存，知道最后一行结果列返回以后，才释放所有的内存。
会导致排序的语句有 Select DISTINCT , MINUS , INTERSECT , UNION 和 min()、max()、count() 操作；而不会导致排序的语句有 Update , 带BETWEEN子句的Select 等等。

监控内存和硬盘的排序比率，最好使它小于 .10，增sort_area_size

Select name, value FROM v$sysstat Where name IN ('sorts (memory)', 'sorts (disk)');

1 sorts (memory) 23851793
2 sorts (disk) 59

Order by提速，与索引关系不大。
因为order by 是对查询结果的排序，添加索引只能优化产生检索结果这个步骤，后面Order by的速度并不能够提高。
如果速度难以忍受，应该考虑对排序区扩容。
扩大sort_area_size的大小。

ORACLE內存管理之一 ORACLE PGA SGA

轉自:http://sunmoonking.spaces.live.com/blog/cns!E3BD9CBED01777CA!509.entry

MEM主要由兩部分組成

SGA, System Global Area---可以被所以PROCESS訪問。
PGA, Process Global Area—單個PROCESS（thread）私有。
UGA會包括在其中之一里
UGA, User Global Area—SESSION私有，shared server的時候在SGA，dedicated server的時候在PGA

HASH_AREA_SIZE server process用來在內存裡存hash table的大小大致來看,如果workarea_size_policy=manual那麼pga_aggregate_target將會不被使用,而是使用響應的 sort_area_size,hash_area_size等參數.;如果workarea_size_policy=auto的話那麼就會使用 pga_aggregate_target而不使用其他的sort_area_size,hash_area_size等參數.
if ( 數據庫版本 >= 10gR1 )
then
   if (workarea_size_policy=auto)
   then
       sort area size 無效
   else
       sort area size 有效
   end if
else -- 數據庫版本 < 10gr1
   if ( shared server 連接方式 )
   then
       sort area size 有效
   else -- dedicated server連接方式
       if (workarea_size_policy=auto)
       then
           sort area size 無效
       else
           sort area size有效
       end if
    end if
end if
用sort_area_size 512K,1M,1G來看手工與自動管理時的異同，以下是自動管理
SQL> create table wwm as select * from all_objects;

SQL> alter session set sort_area_size=524288;
           session xxx memory表示現在這個時刻用了多少，session xxx memory max   表示最高峰時用了多少
SQL> set autotrace traceonly statistics;
SQL> select * from wwm order by 1,2,3,4
---------------------------------------------------------
          0 recursive calls
          0 db block gets
        420 consistent gets
          0 physical reads
          0 redo size
    2178018 bytes sent via SQL*Net to client
      22768 bytes received via SQL*Net from client
       2038 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      30547 rows processed

SQL> alter session set sort_area_size=1048576;           同上
SQL> alter session set sort_area_size=1048576000;    同上
自動管理時sort_area_size不起作用.

改成手工管理後
SQL> alter session set workarea_size_policy=manual;
SQL> alter session set sort_area_size = 65536;                   --64K

SQL> set autotrace traceonly statistics;
SQL> select * from wwm order by 1,2,3,4
----------------------------------------------------------
          0 recursive calls
         22 db block gets
        420 consistent gets
        513 physical reads
          0 redo size
    2178018 bytes sent via SQL*Net to client
      22768 bytes received via SQL*Net from client
       2038 SQL*Net roundtrips to/from client
          0 sorts (memory)
          1 sorts (disk)
      30547 rows processed

============退出，重進
SQL> alter session set sort_area_size=1048576;
SQL> set autotrace traceonly statistics;
SQL> select * from wwm order by 1,2,3,4;
----------------------------------------------------------
          0 recursive calls
          4 db block gets
        420 consistent gets
        424 physical reads
          0 redo size
    2178018 bytes sent via SQL*Net to client
      22768 bytes received via SQL*Net from client
       2038 SQL*Net roundtrips to/from client
          0 sorts (memory)
          1 sorts (disk)
      30547 rows processed
           還有排序操作在DISK上
SQL> set autotrace off
==============退出，重進
SQL> alter session set sort_area_size=1048576000;
SQL> set autotrace traceonly statistics;
SQL> select * from wwm order by 1,2,3,4;
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        420 consistent gets
          0 physical reads
          0 redo size
    2178018 bytes sent via SQL*Net to client
      22768 bytes received via SQL*Net from client
       2038 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      30547 rows processed

           以上分別用64K,1M,1G來測試,分配1G並不代表就要切實分配1G的MEM出去。而是說你有權利用到1G,相反,設置64K並不代表你的session只能用64k,一個SQL可能有多個SORT,指的是每個SORT 的限額

可以使用“alter session set sort_area_size=3000000”使得新值只对当前连接生效
可以使用“alter sysetm set sort_area_size=3000000 deferred”使得新值对随后登录的账号生效，而对当前连接不生效
可以使用“alter system set sort_area_size=3000000 scope=spfile”使得新值在数据库重新启动后生效
这里的数值不能包含K、M、G作单位，多数错误基本都是在数值中包含了这些单位引起的