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

.调整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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13824386/viewspace-697915/