Tuning the Shared pool(4)

The reserved Pool
如果有一个请求，但shared pool中由于内存过于碎片化，没


有连续一片的chunk，就会请求保留池中的内存。
show parameter shared可以看到保留池大小参数。 
Oracle是怎么分配shared pool空间呢？
1.From the unreserved part of the shared pool.
2.From the reserved pool.If there is not enough space 


in the unreserved part of the shared pool,then Oracle 


checks whether the reserved pool has enough space.
3.From memory,If there is not enough space in the 


unreserved and reserved parts of the shared pool,then 


Oracle attempts to free enough memory for the 


allocation.It then retries the unreserved and reserved 


parts of the shared pool.
4:LRU算法交换


SHARED_POOL_RESERVED_SIZE一般是SHARED_POOL_SIZE的5%-


10%。
动态性能视图：v$shared_pool_reserved
SELECT 


free_space,requests,request_misses,request_failures 


FROM v$shared_pool_reserved;


Keeping Large Objects
1.Decide which packages or cursors to pin in memory.
2.Start up the database.
3.Make the call to DBMS_SHARED_POOL.KEEP to pin your 


objects.


固定时希望在instance刚启动时固定，因为这时候没什么碎片


，有更多连续空间供固定。
alter system flush shared_pool;这个命令可以将共享池中


的对象都清空出去，但对固定了的对象没影响。


Find the anonymous PL/SQL blocks and convert them into 


small anonymous PL/SQL blocks that call packaged 


functions:
查有哪些匿名的PL/SQL块,：
SELECT sql_text FROM v$sqlarea
WHERE command_type = 47
AND length(sql_text) > 500;


Other Parameters
OPEN_CURSORS
CURSOR_SPACE_FOR_TIME
SESSION_CACHED_CURSORS
CURSOR_SHARING


Tuning the DD Cache
一般调整shared pool，首先是library cache，然后是data 


dictionary.一般dd一开始是空的，随着查询越来越多，条目


才越来越多。
v$rowcache  包含了数据字典的重要指标。
GETMISSES列一般是不可能为0的，开始时候DD里面什么都没有


，每次查询，这个值都会增加，到了一定程度，就稳定了。
SELECT parameter,gets,getmisses FROM v$rowcache;
调优目标：Avoid Dictionary cache misses
只能调SHARED_POOL_SIZE了。