ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 性能调优笔记之Library cache汇总SQL

性能调优笔记之Library cache汇总SQL

原创 Linux操作系统 作者:bbs159 时间:2011-06-14 17:15:39 0 删除 编辑

.调整library cache大小(shared_pool_advice)    dbms_shahred_pool
   v$share_pool_advice;估算解释所需的时间,从pool_size 大小 ,等
       级排列出来。
     1)计算全局存储在ibrary cache对象的大小
          select sum(sharable_mem) from v$db_object_cache;         
     2)决定查看通常SQL占有内存
           select sum(sharable_mem) from v$sqlarea; 
     3)预留保留值(reserved pool:保留池),对大内存的需求
           shared_pool_reserved_size(5%-10%);
           v$shared_pool_reserved;         
     4)常住内存(大对象)
           select * from v$db_object_cache where
           sharable_mem>10000
           and (type='package' or type='package boby'
             or type='function' or type='procedure')
           and kept='no'; 
          
           execute dbms_shared_pool.keep('package_name');
           
           alter system flush shared_pool;清除系统内存;
     5)影响library cache参数
       open_cursors;
       cursor_space_for_time
       session_cached_cursors
       cursor_sharing
     6)把一些大的PL/SQL分解小的
       
      调整数据字典缓存大小 
        v$rowcache 
        select parameter ,sum(gets),sum(getmiss),
               100*sum(gets-getmisses)/ sum(gets) pct_succ_gets
               ,sum(modifications)
               from v$rowcache
               where gets>0
               group by parameter;
    其他:  

SELECT  component, current_size/1024/1024, min_size, max_size
FROM    v$memory_dynamic_components
WHERE   current_size != 0;

SELECT * FROM v$memory_target_advice ORDER BY memory_size;
select * from v$sgastat where name like '%free%';


select free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size
 from v$shared_pool_reserved bb;
 

调整共享池方法.jpg

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-11

  • 博文量
    26
  • 访问量
    40753