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#;