shared pool的组成部分
librery cache,data dictionary cache,uga
shared pool的大小由参数shared_pool_size决定,但使用自动 sga 大小管理时,shared pool的大小由 oracle决定。(建议使用自动 sga管理,设置sga_target=总大小即指定了sga的总大小)
查看shared pool中librery cache的大小
select * from v$sgastat a where a.NAME = 'library cache';
查看shared pool中free momery的大小
select * from v$sgastat a where a.pool = 'shared pool' and a.NAME = 'free memory';
shared pool的大小可以在 v$ sgainfo中得到。
参数CURSOR_SPACE_FOR_TIME=false,游标可能老化,以让出空间给新的游标,=true,游标不老化,新游标分配新的空间。(已不建议使用)
在OLTP系统中对 shared pool调优的目标是减少硬解析。
2条SQL语句构成共享的条件
1.发出的sql语句和 shared pool中已存在的sql语句对比.
2.比较依据sql语句的hash值。如果hash值不一致则编译新语句。
3.发现hash值一致,则将shared pool中已有的具有相同hash值的语句和新sql语句逐个字符进行比较,包括空格,大小写,注释
4.如果被引用的对象发生改变,则进行hard parse.
5.使用绑定变量提高共享率(变量名要一致)
6. session的当前环境一致(sql优化器)
在v$sysstat中查询name = parse count(hard)的值,可以获得自instance启动以来的硬解析次数。name = parse count(total)的值表示总解析次数。parse count(total) - parse count(hard)的值是软解析次数。
与shared pool有关的latch
shared pool latch
librery cache latch
对shared pool调优的原则:使parse的次数尽可能的少。
确保用户session能共享sql
防止已经parse的语句老化(增加shared pool的容量,但需要先确定free memory是否已经很小)
sql语句访问的对象保持结构稳定。
避免大的匿名pl/sql块
v$librarycache描述了librery cache的性能和活动指标。
Name Type Nullable Default Comments
------------------------- ------------ -------- ------- --------
NAMESPACE VARCHAR2(15) Y library cache中各种对象名称分类
GETS NUMBER Y 在编译过程中library cache中寻找的次数
GETHITS NUMBER Y 在编译过程中library cache中寻找并获取次数
GETHITRATIO NUMBER Y 在编译过程中library cache中命中的比率
PINS NUMBER Y 在执行过程中library cache中寻找的次数
PINHITS NUMBER Y 在执行过程中library cache中寻找并获取次数
PINHITRATIO NUMBER Y 在执行过程中library cache中命中的比率
RELOADS NUMBER Y 被访问对象从磁盘读取到内存的次数
INVALIDATIONS NUMBER Y 被访问对象结构发生变化导致hard parse的次数
DLM_LOCK_REQUESTS NUMBER Y
DLM_PIN_REQUESTS NUMBER Y
DLM_PIN_RELEASES NUMBER Y
DLM_INVALIDATION_REQUESTS NUMBER Y
DLM_INVALIDATIONS NUMBER Y
如何提高shared pool的效率
1.提高v$libraercache中gethits的值(9i)。(使用绑定变量)
2.尽量避免使用动态sql。
3.各个session不要修改sql优化器模式
4.尽可能使用存储过程
5.使用同一用户名连接oracle
6.高峰期不要运行导致被访问对象结构改变的DDL语句
如何找出不能共享cursor的sql.
在v$sql或v$sqlarea中查找执行次数较小的sql语句,观察这些sql语句是否是经常执行的。
检查shared pool
1.检查v$libraercache中reloads的次数,要尽可能低。
2.检查v$libraercache中invalidations的次数,要尽可能低。
3.检查v$sgastat中free memory的值,如果值过低,应该扩大shared pool的容量。
4.检查shared pool的命中率。
1.v$libraercache中PINHITRATIO的值
select sum(pinhits)/sum(pins)*100 from v$librarycache;
2.select sum(pins) "hits",
sum(reloads) "misses",
sum(reloads)/sum(pins) "Hits Ratio"
from v$librarycache;
不要大于1%
如何决定library cache的大小
select a.SHARED_POOL_SIZE_FOR_ESTIMATE,a.ESTD_LC_SIZE,a.ESTD_LC_TIME_SAVED from v$shared_pool_advice a;
SHARED_POOL_SIZE_FOR_ESTIMATE:估算的sharad pool容量
ESTD_LC_SIZE:library cache的大小
ESTD_LC_TIME_SAVED :估算的节约的时间
如何计算library cache的大小
计算所有非SQL语句对象占用的内存
select sum(sharable_mem) from v$db_object_cache;
计算SQL语句占用的内存
select sum(sharable_mem) from v$sqlarea;
它们的和就是library cache的大小
计算shared pool的使用率
SELECT
(1 - ROUND(BYTES / (xxx), 2)) * 100 || '%'
from V$SGASTAT
WHERE NAME = 'free memory' AND POOL = 'shared pool';
xxx:v$sgainfo中shared pool的大小
保留池相关
在shared pool内部有一部分空间属于保留池,当一些对象需要存储到shared pool中,但shared pool已有的连续的空间已经不足时, oracle就会使用保留池的空间。使得对大对象的内存分配更高效。PL/SQL块,触发器编译等都有可能使用保留池,当保留池的空间释放后,该空间交还给保留池。如果在分配内存空间时发现保留池的空间都已经用完,则会让一部分对象老化。
一般情况下使用默认的保留池大小即,有必要设置保留池大小时可以设置参数shared_pool_reserved_size,最大不能超过shared pool容量的50%,建议保留池大小为shared pool大小的5%--10%
与保留池相关的视图:v$shared_pool_reserved
重点查看FREE_SPACE(空余空间),REQUESTS(对保留池空间的请求次数),
REQUEST_MISSES(请求保留池空间失败的次数),REQUEST_FAILURES(失败次数);
将PL/SQL对象固定在library cache中
查询可以被固定的对象
select * from v$db_object_cache where sharable_mem > 10000
and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type='PROCEDURE')
and kept = 'NO';
执行 dbms_shared_pool.keep('对象名');
调整data dictionary cache
主要视图:v$rowcache
PARAMETER:data dictionary cache中每个子部件的名称
GETS:向data dictionary cache发起请求的次数
GETMISSES:向data dictionary cache发起的请求未命中的次数
GETMISSES占比不能超过15%。
查看data dictionary cache中各部件的命中率
select parameter,sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets),sum(modifications)
from v$rowcache
where gets>0 group by parameter;
转载于:https://my.oschina.net/abcijkxyz/blog/720316