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错误方法：

1. alter system flush shared_pool;(不建议操作);
2. 共享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;

1. 增加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

   );