share pool的组成
share pool的组成
3块区域：free,library cache,row cache
通过查看v$librarycache视图，可以监控library cache的活动情况，进一步衡量share pool设置是否合理;
其中reloads列，表示对象被重新加载的次数，在一个设置合理的系统里，
这个数值应该接近于0，另外，invalidations列表示对象失效的次数，对象失效后，这意味着sql必须要被重新解析;
select namespace, pins, pinhits, reloads, invalidations
from v$librarycache order by namespace;
硬解析大量存在的时候会产生ora-4031错误:
清空library cache的sql计划缓存，后期执行的sql会硬解析(不建议操作)
alter system flush shared_pool;
查看library cache的命中率的命中率
select sum(pinhits) / sum(pins) from v$librarycache;
查看free的空间情况
select * from v$sgastat where pool ='shared pool' and name ='free memory';
查看row cache的空间使用情况
select * from v$sgastat where name = 'row cache';
解决ora-4031错误方法：
- alter system flush shared_pool;(不建议操作);
- 共享sql，绑定变量;
共享-完全相同-空格、大小写、数值不一样、回车等等，统统认为sql不一样，会发生硬解析。
统一书写风格
使用绑定变量
declare
v_sql varchar2(50);
begin
for i in 1..1000 loop
v_sql :='insert /*hello*/ into test values(:1)';
execute immediate v_sql using i;
end loop;
end;
select sql_id,sql_text,executions from v$sql where sql_text like '%hello%' ; --executions执行次数
找出没有共享的sql语句：
在v$sql中查找执行次数比较小的sql语句，观察这些sql语句是否经常执行;
select sql_fulltext from v$sql where executions =1 and sql_text like '%from t%';
select sql_fulltext from v$sql where executions =1 order by sql_text;
3）select * from v$db_object_cache where sharable_mem >1000
and (type='PACKAGE' or type ='PACKAGE BODY' or type='FUNCTION' ortype='PROCEDURE') and kept ='NO';
执行dbms_shared_pool.keep('对象名');
打开dbms_shared_pool包的方式:@?/rdbms/admin/dbmspool.sql;
4）保留区
select request_misses from v$shared_pool_reserved;
- 增加shared pool空间
select component,current_size from v$sga_dynamic_components;
show parameter sga_target;
show parameter sga_max_size;
alter system set shared_pool_size =150M scope=both;(设置必须比SGA自动分配的值大才会生效)。
查看sql硬解析，软解析的具体情况，硬解析比较耗资源（parse count (hard)），少比较好
select * from v$sysstat where name like 'parse%';
解析命中率：
软解析命中率
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcache where gets >0;
6.解决4031错误
1.alter system flush shared_pool; 临时解决办法
2.共享sql(开发人员)，如果开发人员改不了，可设置 alter system set cursor_sharing ='force';字面值没有绑定变量，系统会自动sql共享，但解决不了书写规格不统一问题;
3.保留区
select request_misses from v$shared_pool_reserved;
show parameter shared_pool_reserved_size;
4.增加shard pool空间
查看shared pool大小
select component,current_size from v$sga_dynamic_components; --current_size单位bytes
show parameter sga_target;
show parameter sga_max_size;
alter system set shared_pool_size='400M' scope =both;(管理员执行,设置的大小必须比sga自动分配的shared_pool大,参照v$sga_dynamic_components查询的值
,否则设置不成功(会发现show parameter share pool查出来的和v$sga_dynamic_components(sga自动分配的)查的不相等，))
10g后只需要设置sga_target大小，然后系统根据sga_target自动分配给shared_pool，buffer cache等，但还是可以修改，但修改的值必须比sga分配的大，才能生效;
设置shared pool大小，当parse_time_factor=1时对应的estdsp_size大小
select 'shared pool' component,shared_pool_size_for_estimate estdsp_size,estd_lc_time_saved_factor parse_time_factor,
case when current_parse_time_elapsed_s+adiustment_s<0
then 0
else current_parse_time_elapsed_s+adiustment_s
end response_time
from (
select shared_pool_size_for_estimate,shared_pool_size_factor,estd_lc_time_saved_factor,a.estd_lc_time_saved,
b.value/100 current_parse_time_elapsed_s,c.estd_lc_time_saved-a.estd_lc_time_saved adiustment_s
from
v$shared_pool_advice a,
(select * from v$sysstat where name ='parse time elapsed') b,
(select estd_lc_time_saved from v$shared_pool_advice where shared_pool_size_factor =1) c
);