【转】Oracle性能调优-优化排序操作

　　关于Oracle 10g性能方面，谈论最多的就是新的自动工作负荷存储库（AWR）特性和通过自动数据库诊断监视（ADDM）提供的前瞻性性能监视。
　　当建立同Oracle会话时，会在服务器内存中划分出一个专门用来排序的区域，从而为会话提供排序空间。但是，这个排序空间毕竟有限，若记录数量超 过这个排序空间的话，就需要进行磁盘排序。但是，我们都知道，磁盘排序的执行速度要比内存排序的执行速度慢1400倍。而且，磁盘排序会消耗临时表空间的 资源，并且可能影响到正在进行的其他SQL排序，因为Oracle必须为临时表空间中的数据块分配缓冲池。而且，过多的磁盘排序会导致空闲缓冲等待，以及 将执行其他任务的数据块从缓冲池中分页出去。对于数据库管理员来说，在内存中进行排序总是比磁盘排序更受欢迎。所以说，磁盘排序是影响Oracle数据库 性能的罪魁祸首。
　　在数据库优化的时候，我们应该想法设法降低数据库的磁盘排序。为此，笔者有如下建议。
　　一、合理设置Sort_area_size参数。
　　虽然说Oracle10G以后的数据库会自动对内存进行管理。但是，在一些性能要求比较高或者排序频率比较高的数据库中，仍然有必要对一些影响内存分配的参数进行调整。其中，最重要的一个参数就是Sort_area_size。
　　Oracle数据库会为所有的链接Oracle会话分配Sort_area_size这个参数。所以，对于拥有大量用户的数据库来说，如果增加 这个参数的值，会让磁盘排序的几率明显降低，不过数据库也要为此付出这个代价，很容易导致内存过载。但是，如果这个参数的值设置的过低的话，又会导致过多 的磁盘排序。
　　所以，这个参数并不是越大越好。因为这个参数如果设置的过大的话，其带来的性能收益反而会降低。因为为了提高有限几个查询的速度，可能会浪费大量的内存。这无疑是我们数据库管理员不希望看到的。
　　在实际工作中，我们往往需要在两者之间进行一个均衡。设置一个合理的参数，尽量让数据库减少磁盘排序的几率，同时也不能使得服务器内存过载。
　　为此笔者有一个建议。数据库管理员应该每隔一段时间增加这个参数的值，并使用Statspack工具定时监控内存排序与磁盘排序的数据。在起初进行调整的时候最好每个小时查询一次。通过这些数据，我们就可以得到一个合理的参数值，在两这之间取得一个均衡。
　　前期调整完成后，在后期仍然需要进行监控。因为后期随着企业应用的改变，这个参数仍然需要根据实际情况进行调整，以提高数据库的性能。
　　二、尽量减少不必要的排序。
　　在某些情况下，尽管数据库管理员没有直接通过Order By等语句对数据库记录进行排序，可是Oracle数据库服务器仍然会对查询结果进行排序。因为这些语句需要起作用，必须要先对数据进行排序。所以，他们往往带有隐性的排序功能。
　　我们在数据库维护或者前台应用程序设计的时候，要尽量的减少这种不必要的排序。如Distinct关键字，它的作用就是取消重复的记录。但是， 要实现这个目的的话，则数据库必须要先对记录进行排序，然后才能够去除重复的记录内容。故在设计的时候，尽量要避免使用Distinct关键字。其实，笔 者在工作中，经常会碰到这种情况，某些记录其实不存在重复记录，但是程序开发人员为了保障数据的准确性，就在SQL语句中加入了Distinct关键字， 从而造成了不必要的排序。
　　另外，在其他一些情况下，也会导致不必要的排序。如排序合并连接，也会导致不必要的排序。故无论何时，只要使用了排序合并连接，就会执行排序已 连接关键值。故在数据库与应用程序设计的时候，要尽量避免排序合并连接。其实，在许多情况下，嵌套循环连接反而使更好的选择。因为这个嵌套循环连接，它更 加有效而且不会导致不必要的排序以及不比要的全表扫描。
　　其次，有时候缺失索引也会导致一些并不要的排序。
　　故数据库管理员在平时的工作中，要尽量的减少这些不必要的排序，以让宝贵的内存资源交给更重要的任务来适用。
　　三、利用Statspack工具监控排序活动。
　　Statspack工具是一款提高Oracle数据库性能的很好的辅助工具。因为它可以帮助我们收集很多有用的信息。故我们数据库管理员也可以利用Statspack工具对数据库中的排序活动进行监控。
对于一个有经验的数据库管理员来说，对内存排序和磁盘排序保持必要的排需是非常必要的。因为我们无法左右用户的行为;而用户的行为又会有所调整。用 户在调整的过程中，有可能又会增加额外的磁盘排序。当然，也有可能磁盘排序的几率会减少。但是，通常情况下，随着用户交易数据的增加，这个磁盘排序的几率 在理论上仍然是往上爬的。而实际上也是往上升的，只是这个升的速度没有理论上那么快而已。这主要是看数据库管理员如何进行管理了。
　　根据笔者的了解，企业用户的操作往往会有一个周期性的变化，如按年或者按月进行周期性的变化。数据库管理员应该养成一个好习惯，每个月利用Statspack工具定期的对数据库进行监控。特别是要监控数据库的排序情况。
　 　Statspack工具还有额外的一个功能，就是自动监测与警告功能。也就是说，可以让Statspack这个工具在磁盘排序数量超过一个预设置的阀值 时，自动给数据库管理员发送一个警告，如通过邮件形式发送给管理员等等。笔者通过监控发现，每到月底与月初的时候，磁盘排序的数量会大大的增加。这主要是 因为在月底的时候，用户会对当月的交易数据进行统计。所以当月底月初的时候，由于交易记录比较多，所以，会有比较多的磁盘排序发生。在这种情况下，数据库 管理员有必要对相关参数进行调整。不过这个调整是暂时的调整，等到这个周期过去后，仍然要把参数调回来。只有如此，数据库的整体性能才会有所保障。即不会 因为内存过载而降低数据库性能;也不会因为磁盘排序而给数据库造成额外的负担。
　　所以，虽然排序是SQL语句执行中很微小的一个部分，但 是其对数据库性能影响却比较大，而且也是非常显著的。可惜的是，排序是SQL调整中往往被忽视的地方。在Oracle数据库中，排序对用户来说是透明的。 也就是说，排序对用户很少有所限制，用户可以根据自己的需要来对数据进行随意地排序。但是，用户并不知道，什么样的操作会降低数据库的性能。故如何降低用 户的不合理操作而产生额外的排序，甚至是磁盘排序，这是数据库管理员在平时工作中必须要考虑到的一个问题。通过以上三个方法，或许可以给数据库管理员找到 一些解决问题的思路。相信通过以上方法，可以最大程度的减少磁盘排序的发生，不再让磁盘排序成为影响数据库性能的罪魁祸首。
优化排序操作
    1、概念
    服务器首先在sort_area_size指定大小的内存区域里排序，如果所需的空间超过sort_area_size，排序会在临时表空间里进行。在专 用服务器模式下，排序空间在PGA中，在共享服务器模式下，排序空间在UGA中。如果没有建立large pool，UGA处于shared pool中，如果建立了large pool，UGA就处于large pool中，而PGA不在sga中，它是与每个进程对应单独存在的。
     PGA：program global area,为单个进程（服务器进程或后台进程）保存数据和控制信息的内存区域。PGA与进程一一对应，且只能被起对应的进程读写，PGA在用户登录数据库创建会话的时候建立。
    有关排序空间自动管理的两个参数：
    Pga_aggregate_target: 10M-4000G，等于分配给oracle instance的所有内存减去SGA后的大小。
    Workarea_size_policy: auto/manual，只有Pga_aggregate_target已定义时才能设置为auto。
    这两个参数会取代所有的*_area_size参数。
    措施：
    尽可能避免排序；尽可能在内存中排序；分配合适的临时空间以减少空间分配调用。
    2、需要进行排序的操作：
    A、创建索引；
    B、涉及到索引维护的并行插入
    C、order by或者group by（尽可能对索引字段排序）
    D、Distinct
    E、union/intersect/minus
    F、sort-merge join
    G、analyze命令（仅可能使用estamate而不是compute）
    3、诊断和措施
    Select * from v$sysstat where name like ‘%sort%’;
    Sort(disk):要求Io去临时表空间的排序数目
    Sort(memory)：完全在memory中完成的排序数目
    Sort(rows)：被排序的行数合计
    Sort（disk）/ Sort（memory）<5%,如果超过5％，增加sort_area_size的值。
SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio FROM v$sysstat disk,v$sysstat mem WHERE mem.NAME=’sorts (memory)’ AND disk.NAME=’sorts (disk)’;
      DISK        MEM      RATIO
———- ———- ———-
       182   27333829 0.00066584
    4、监控临时表空间的使用情况及其配置
    Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks FROM v$sort_segment ;
    Column Description
    CURRENT_USERS Number of active users
    TOTAL_EXTENTS Total number of extents
    USED_EXTENTS Extents currently allocated to sorts
    EXTENT_HITS Number of times an unused extent was found in the pool
    MAX_USED_BLOCKS Maximum number of used blocks
    MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort
    临时表空间的配置：
    A、initial/next设置为sort_area_size的整数倍，允许额外的一个block作为segment的header
    B、pctincrease=0
    C、基于不同的排序需要建立多个临时表空间
    D、将临时表空间文件分散到多个磁盘上

转自：http://boyun.sh.cn/bio/?p=851

另一个网址的：
1。 使用并行参数，8个CPU, 可以用parallel 6 ,最多占用6个CPU, 正常情况下没问题（前提是其他应用没有占超过2个CPU的资源）
2。 nologging, 绝对应该使用，会使速度大幅上升。（减少大量redo log)
3。 PGA, 普通的auto pga最大才100M, 显然没有达到最好性能，应该使用manual pga
alter session set workarea_size_policy=manual;
alter session set  hash_area_size=100000; -- hash_area_size 默认情况下会自动根据sort_area_size*2来调，导致sort_area_size不能超过1G, 手动设了就没问题了。
alter session set sort_area_size=2000000000; -- 在系统可用内存足够的情况下，最大可以到2G
4。 设置高一点的db_file_multiblock_read_count 也有利于提高I/O性能。保证足够大的db_cache_size可以避免free_buffer_wait的出现
5。 可以通过v$session_longops 来监视进度