库缓存统计
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#;
Oracle 性能调优学习笔记(四)-- 库缓存统计
最新推荐文章于 2024-08-26 16:32:13 发布