Oracle调优之shared pool相关

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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!