ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle sql 排序优化

oracle sql 排序优化

原创 Linux操作系统 作者:zhanglincon 时间:2009-04-29 16:50:27 0 删除 编辑

概念:    
    服务器首先在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。

1.什么导致排序:
  order by
  group by
  select distinct
  create index
  union/minus
 anlyze
  优化器调用排序合并连接
2. 排序在内存还是在磁盘中进行?
在内存执行的排序速度要比在磁盘执行的排序速度快14000倍。如果是专用连接,排序内存根据INIT.ORA的sort_area_size进行分配,如果是共享服务器连接,排序内存根据large_pool_size进行分配。sort_area_size的增大可以减少磁盘排序,但是过大将使ORACLE性能降低,因为所用的连接回话都会分配到一个sort_area_size大小的内存,所以,为了提高有限的查询速度,可能会浪费大量的内存。增加sort_multiblock_read_count的值使每次读取更多的内容,减少运行次数,提高性能。
3.怎么设置sort_area_size:每隔一段时间增加sort_area_size,并监控内存排序和磁盘排序数量。当sort_area_size的值的增加不在导致磁盘排序减少时,就合适了。
3.1、诊断和措施
   col name format a20
    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)'; 

3.2、监控临时表空间的使用情况及其配置
    Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks 

FROM v$sort_segment ;

    Column Description  [Page]
    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、将临时表空间文件分散到多个磁盘上

4.sort_area_retain_size:决定磁盘排序完后保留内存的数量。
5.sort_multiblock_read_count,db_file_multiblock_read_count。
6。添加索引避免排序

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8554499/viewspace-592271/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 一条shell命令
请登录后发表评论 登录
全部评论

注册时间:2009-03-24

  • 博文量
    79
  • 访问量
    206351