库缓存统计
??? 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,不要放在shar