Oracle 性能调优学习笔记(四)-- 库缓存统计

库缓存统计
    1.当更改共享池的目标提供SQL语句的软解析.
    reload:reload的在对象无效或者变化使用需要重新reload.减少reload的值.
    INVLIDATIONS: 在v$librarycache,库缓存的无效次数.
    2.共享池中空闲内存的大小(v$sgastat).
      库缓存命中率.
       select gethitratio from v$librarycache
       where namespace='SQL AREA';
   查看SQL正在运行的语句
        select sql_text,users_executing,executions,loads
        from v$sqlarea;
     select *  from v$sqltext
     where sql_text like  ' %q%';
    
   3.库缓存reload必须小于1%.
   select sum(pins) "Excutions " ,
       sum(reloads) "Cache Misses",
    sum(reloads)/sum(pins) reloads-to-pins
   from v$librarycache;
   如果reloads-to-pins大于1%,需要增加共享池的大小.
   
   select namespace,gets,gethits,pins,pinhits,reloads,invalidations from v$librarycache;
   备注:一般只关注:前四条结果,其他几条不准确.
   4.无效次数
             select namespace,gets,gethits,pins,pinhits,reloads,invalidations from v$librarycache;
         搜集表的信息
      exec dbms_stats.gather_table_stats('SCOTT','EMP');
    
    library Cache大小调整:
     1.存储的各种对象的大小.
     2.通常sql语句的内存信息.
     3.在share pool 定义一个大的内存空间(reserve space forlarge memory).
     4.pin频率
    Shared Pool Advisory
       share_pool调优必须要查看share_pool_size和节省时间的关系
        select shared_pool_size_for_estimate as pool_size,
            estd_lc_size,estd_lc_time_saved
          from v$shared_pool_advice;
    执行计划的缓存
        SQL语句缓存在内存中.
     SQL语句和SQL的执行计划并存的.
     查询执行计划进行性能调优.
     v$sql_plan:保存library cache中部分缓存.
          v$sql_plan中plan_hash_value和v$sql中的hash_value列关联.
     select operation,object_owner,object_name,cost
            from v$sql_plan
           order by hash_value;
     
     全局缓存空间大小
       非SQL语句对象library cache占用内存大小.
      select sum(sharable_mem) from v$db_object_cache;
       SQL语句library cache对象占用内存
        select sum(sharable_mem) from v$sql_area
           where executions>5;
       保留池(约占共享池的10%):
         保留池:存储碎片等.当共享池中内存不足的时候,可以
      采用保留池镇南关的内存.共享池释放内存,将归还保留池.
      shared_pool_reserved_size为保留池的大小.
      shared_pool_size为共享池的大小
      v$shared_pool_reserved用于调优保留池大小.free_space,
        request_misses使用保留池的次数.如果共享池中保留和非
        保留之和仍然不足,那么使用LRU算法,去除就的数据.
       select free_space,requests,request_misses,request_failures
          from v$shared_pool_reserved;
          v$shared_pool_reserved的request_failures,将继续增加,shared_pool太小:
             request_misses如果经常为0,或者free_memory经常为共享
          保留池的50%以上,那么可以适当减小保留池.
            request_failures>0 and request_misses>0 增加保留池.
         request_failures>0 and free_memory>=50% 增加共享池
         request_failures=0 or free_memory>=50% 降低共享保留池
       
     大堆固化在内存中
       查询非固化对象
       select * from v$db_object_cache
          where sharable_mem>10000
        and type in('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE' )
                    and kept='NO';
       固化对象
         exec dbms_shared_pool.keep('package_name');
  
             清除共享池中的内容
       alter system flush shared_pool;
       匿名PLSQL块
        select sql_text from v$sqlarea
       where command_type=47
         and length(sql_text)>500;
     
     Data directionary Cache
     v$rowcache获取数据字典缓存信息
         内容:数据字典对象
       gets:对象请求的次数
       getmisses:读取数据字典缓存的次数.
       select parameter ,gets,getmisses from v$rowcache;
      
       数据字典缓冲的统计:
       select parameter,sum(gets),sum(getmisses),
            100*sum(gets-getmisses)/sum(gets) pct_succ_gets,
         sum(modifications) updates
       from v$rowcache
       where gets>0
       group by parameter;
      
      
              StatsPack  报告中percent misses最好比较低.
         <2%:数据字典对象查询(Pct SGA:缓冲申请的大小所占的百分比)
      <15%:数据字典缓存(Cache Usage)
      
     Large Pool:
        UGA最好放在Large pool,不要放在share pool中.
        Large Pool中内存使用:
          IO服务器进程:
          备份恢复操作.
       共享服务器会话
       并行查询消息
        select * from v$sgastat
        配置参数LARGE_POOL_SIZE.
       
       
     UGA中用户连接使用:
        select sum(value) ||'bytes' "Total session mermory"
           from v$mystat ,v$statname
        where name='session uga memory'
          and v$mystat.statistic#=v$statname.statistic#;
         UGA中Oracle 共享服务器用户使用的:
                     select sum(value) ||'bytes' "Total session mermory"
         from v$sesstat ,v$statname
         where name='session uga memory'
        and v$sesstat.statistic#=v$statname.statistic#;
                        UGA最大的空间:
                     select sum(value) ||'bytes' "Total session mermory"
         from v$sesstat ,v$statname
         where name='session uga memory max'
        and v$sesstat.statistic#=v$statname.statistic#;