Oracle 性能调优学习笔记(九)-- 优化排序操作B

监控SQL使用的内存
   select sql_text,sum(onepass_executions) onepass_cnt,
       sum(multipasses_executions) multipass_cnt
   from v$sql s,v$sql_workarea wa
   where s.address=wa.address
   group by sql_text
   having sum(onepass_executions+multipasses_executions)>0;
   
   select to_number(decode(sid,65535,NULL,sid)) sid,
      operation_type operation,
      trunc(expected_size/1024) esize,
      trunc(actual_mem_used/1024) mem,
      trunc(max_mem_used/1024) maxmem,
      number_passes           pass,
      trunc(tempseg_size/1024)  tsize
     from v$sql_workarea_active
     order by 1,2;
    
     select operation ,options,object_name name,
     trunc(bytes/1024/1024) "Input(MB)",
      trunc(last_memory_used/1024) last_mem,
      trunc(estimated_optimal_size/1024) optimal_mem,
      trunc(estimated_onepass_size/1024) onepass_mem,
      decode(optimal_executions,null,null,
              optimal_executions || '/' ||onepass_executions
        ||'/'|| multipasses_executions ) "0/1/M"
        from v$sql_plan p, v$sql_workarea w
        where p.address=w.address(+)
          and p.hashvalue=w.hash_value(+)
       and p.id=w.operation_id(+)
       and p.address='88BB460C'
       and p.hashvalue=3738161960;
      
      v$pga_target_advice:
         pga区大小和pga命中率的关系
         select round(pga_target_for_estimate/1024/1024) as target_mb,
              estd_pga_cache_hit_percentage as cache_hit_percent,
        estd_overalloc_count
        from v$pga_target_advice
        order by target_mb;
     这个命中率至少高于60%,
     PGA调优
     1.PGA_AGGREGATE_TARGET至少375MB.
     2.分析PGA和命中率的关系
      v$pgastat
      statistics_level必须设置级别为typical.
      select name profile,cnt,decode(total,0,0,round(cnt*100/total)) percentage
         from ( select name ,value cnt, (sum(value) over()) total
      from v$sysstat
      where name like 'workarea exec%');
      OEM PGA区的信息.
    v$PGA_TARGET_ADVICE_HISTORGRAM显示V$SQL_WORKAREA_HISTOGRAM.
    排序所需的空间:
        在专用服务器模式,为PGA中UGA的一部分.
     在共享服务器模式,为共享池中UGA的一部分.
    Oracle 不推荐使用SORT_AREA_SIZE,建议使用pga_aggregate_target
          alter session ....deferred;
    alter system ....deferred;
    
    sort_area_retained_size:排序结束之后,UGA保留的数值.
   排序的区域的参数
      一个执行可能包含多个排序.
      一个单个服务器需要一个排序.
         需要SORT_AREA_SIZE字节大小.
      需要join sort的最小区域sort_area_retained_size.
    每一个并行查询服务需要sort_area_size大小.
   排序调优:
       1.使用自动排序区管理
    2.尽可能的避免排序.
    2.尽量排序时候交换区和page的频繁交换.
   
   临时表空间:
    临时表空间只有一个sort segment;
    不用更新数据字典,更新基于内存的.
    在第一次使用排序,开始申请表空间.
    临时表空间的exetents不需要在数据字典管理.
    在SGA中有一个Sort exetent pool(SEP0.
    
   创建临时表空间
   create temporary tablespace temp tempfile
     '$ORACLE_HOME/ORADATA/u01/temp.dbf' size 200m;
      创建临时表空间,只是创建一个临时文件的头部和尾部,在使用的时
   候才开始真正的分配表空间.在创建之后df -k文件系统的大小不变.
   
   需要排序的情形
    索引需要排序.
    并行插入表.
    order by 或者group by.
    distinct去除重复.
    union,intersect,minus操作.
    sort-merge join.
    analyze命令分析表.
   
   查看系统排序的情况(内存,磁盘,行数)
      select * from v$sysstat where name like '%sort%';
   
   避免排序方法:
      1.create indexes 使用nosort;
      2.使用union all替代union.
      3.使用index访问表的joins
      4.在排序列上创建索引
      5.针对large objects使用estimate而不是compute
     
   排序的性能工具
    服务器进程排序在UGA中排序在内存,和临时表空间排序,使用情况的为:v$sort_usage;
    临时表空间相关的排序临时段:v$sort_segment;
    磁盘上排序情况看v$sysstat中sort字段.
   诊断和指导线
    在OLTP系统中磁盘排序占内存排序比率应该小于5%;如果高于5%需要增加SORT_AREA_SIZE/PGA_AGGREGATE_TARGET的值.
    select d.value " DISK" ,m.value "MEM",
       (d.value/m.value)*100 "Ratio"
    FROM v$sysstat m ,v$sysstat d
    WHERE m.name='sorts(memory)'
    and d.name ='sorts(disk)';
   
   监控临时表空间
       默认存储参数应用在排序segment.
    sort segment有无限的extents.
    临时表空间中所有每一个排序segment.
     select tablespace_name,current_users,total_extents,
     used_extents,extent_hits,max_used_blocks,
     max_sort_blocks
     from v$sort_segment;
   临时表空间的设置.
       1.设置适当的存储值.
    2.根据排序需要设置不同的表空间.
    3.条带化表临时空间.
    4.根据v$tempfile和dba_temp_files查看临时表空间的信息.
    查看表空的存储信息
    select session_num,tablespace,extents,blocks
       from v$sort_usage;