Tuning the shared pool

1.shared pool

 shared pool 包含:library cache,data dictionary cache,user global area

 

2.规定大小

 SGA_target

 

3.查看sga大小

 show parameter sga

select * from v$sgastat

 

4.数据库指标

  select * from v$sysstat

 

5.修正匹配方式

 

SQL> alter system set cursor_sharing = 'similar';

 

6.视图

 v$shared_pool_advice---查看是否应该增大share pool大小

 v$sql_plan ---保存sql的执行计划

 v$sql通过字段plan_hash_value和v$sql_plan中的hash_value相关联。

 

select sum(sharable_mem) from v$db_object_cache.----查询出非sql占用的内存

select sum(sharable_mem) from v$sqlarea ---查询出所有sql所占用的内存

 

7.设置保留池大小

 shared_pool_reserved_size

v$shared_pool_reserved  ---查看保留池是否足够

 

8.固定内存语句

 select * from v$db_object_cache where kep = 'NO';-- 查询

 SQL>execute dbms_shared_pool.keep('package_name');

 

9.清除share_pool内容

 alter system flush shared pool;

 

10.调节large_pool

 select * from v$sgastat where pool = 'large pool';

 alter system set large_pool_size = NUM;