1、Shared Pool的组成
** library cache：存储共享的SQL和PL/SQL代码（解析和编译后的SQL和pl/sql blocks——procedures，Functions，packages，triggers和匿名的pl/sql块）使用最近最少使用算法管理 （LRU），避免语句的重复解析。
** data dictionary cache：保存字典对象的信息
** user global area（UGA）：用于在没有设置large pool时存放共享的server connection的信息。保存在Shared模式下session的信息。
当缺少data dictionary cache或library cache的代价比缺少database buffer cache的代价更高，所以对Shared pool的tuning有更高的优先权。在tuning Shared pool时主要关心library cache提高其hit ratio又更重要些。
当Shared pool过小，server为了管理有限的空间，需要消耗更多的cpu，从而引起争用。
Shared pool从整体上可以通过参数shared_pool_size来调节。
2、library cache
在server查找是否有被缓冲的sql时，会先把statement转换为ASCII文本，再进行hash函数的计算。
1）尽可能减少重复解析的次数：尽可能使code扁平化，使用bind variables；可以适当增大library cache的大小，从而减少由于cache不足造成的已经被解析的SQL被换出而引起重新解析的可能；如果在cache中保存的被解析的SQL相关联的 schema object被修改，则该cache中的内容将不可用，所以要尽量避免这样的事件发生。
2）避免library cache的碎片产生：为较大的内存需求保留一定的内存，主要通过shared_pool_reserved_size参数设置；将常用的较大的SQL和 PL/SQL对象pinning到内存中，避免其被换出；为Oracle Shared server设置large pool；尽量少使用较大的匿名blocks，用小的PL/SQL包的方法代替；在Oracle Shared Server中测量共享进程所用的session内存。
3）在v$librarycache中保存了每类数据保存在library cache中的统计信息。其中，三个字段较为重要：gets，显示了相应item总的请求数量；pins，显示了执行的次数；reloads显示了被换出后重载的次数
4）调节library cache的诊断工具：
** v$sgastat
** v$librarycache
eg：sql> select namespace, gethitratio from v$librarycache; –获得命中率，在OLTP中应该高于90%，如果没有达到可以考虑以下方法：
a)提高应用程序代码效率（通过绑定变量，避免SQL的硬解析）
b)增加共享池的大小（增加之前先通过v$sgastat查询是free memory是否足够大，有无增加共享池必要）
** v$sqltext：full SQL text
eg：sql> select sql_text, users_executing, executions, loads from v$sqlarea;
sql> select * from v$sqltext where sql_text like ’select %’;
** v$db_object_cache：缓冲的DB object，包括packages、tables或是SQL中参照的别名
** v$sqlarea：统计了所有的共享cursor和相应的sql的前1000个字符
** 如果重载的比率大于1%，需要考虑增大shared_pool_size。
sql> select sum(pins) “executions”, sum(reloads) “cache misses”, sum(reloads)/sum(pins) from v$librarycache;
在statspack report中，library cache activity (Instance state activity)的内容也显示了此信息：parse count（total、hard、failure）
5）还需要明确的是多次的修改对象，也可能使library cache的内容被标记为invalid，从而造成reload-to-pins值增大。在v$librarycache中的invalidations 字段显示了标记为invalid的次数。注意：analyze语句可用使标记为invalid。
sql>select namespace, pins, reloads, invalidations from v$librarycache;
可以查询v$shared_pool_advice查看oracle建议使用共享内存，也可以通过oracle 的OEM查看共享内存图。
6）与缓冲的执行计划的相关的view：
** 动态性能视图v$sql_plan可用于查看缓冲了的cursor的的执行计划信息，它比plan_table表还多7个字段，两者中的相同字段的值是一 致的。此外在v$sql视图中添加了一个字段plan_hash_value，它为一个hash值，用于匹配执行计划。与v$sqltext、 v$sql_plan、v$sqlarea中的hash_value字段对应。
7）测试applications：对应已经存在的application，先分配一个较大的shared_pool_size，开启应用，计算当前使用的共享内存：
select sum(sharable_mem)
from v$db_object_cache
where type=’PACKAGE’ or type=’PACKAGE BODY’
type = ‘FUNCTION’ or type = ‘PROCEDURE’;
对于相关的SQL语句，需要通过v$SQLAREA查询：
select sum(sharable_mem) from v$sqlarea where executions>5;
此外，也可以假设在每个user每打开一个cursor将增加250 bytes。这可以测试application在高峰期的shared pool的占用：
select sum(250* users_opening) from v$sqlarea;
在测试环境中，可以根据user的多少来估算打开的cursors数量。
select 250 * value bytest_per_user
from v$sesstat s, v$statname n
where s.statistic# = n.statistic# and n.name = ‘opened cursors current’ and s.sid=15;
这样，在理想情况下，application中将占用的library cache大约是上述的总和加上少量的动态SQL。
8）large memory的存在必要性在于满足较大的连续内存的需求，可以通过large_pool_size和 shared_pool_reservered_size来设置。一般会建议将其设置为shared_pool_size 的10%，如果设置为50%或以上，系统将报错。
通过视图v$shared_pool_reserved可以帮助tuning其大小。其中最好的目标是设置使request_misses（由于 large mem不足，通过LRU实现的flush）的长期统计结果接近0。还有一个procedure工具 dbms_shared_pool.aborted_request_threshold，用于限制shared pool在出现ORA-4031之前被flush
对于其调节：如果v$shared_pool_reserved中的request_misses值不为0并且不断增加，需要考虑增加 shared_pool_reserved_size；如果request_misses为0，并且free_memory=>50%，考虑减少 shared_pool_reserved_size；v$shared_pool_reserved中的request_failures（显示了没有 memory用于满足请求）>0并不断增加，可以适当考虑减小shared_pool_reserved_size或增大 shared_pool_size。
9）将large object保存在内存中。
通过下面的语句可以查看在内存中已经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’;
需要被pin入内存中的对象主要有：常用的较大的存储对象，如standard、diutil包；编译的常用的triggers；sequences。最 好在开机时就将其pin入内存中（我以为这里可以编写适当的开机trigger）。这样，既是使用命令alter system flush shared_pool时，也不会讲这些object flush掉。
具体pin对象到内存的方法可以使用DBMS_SHARED_POOL.keep存储过程。可以用unkeep方法解除其pin状态。
10）对于匿名pl/sql块：可以先在视图v$sqlarea中找到相应的sql_text，并将其适当的改为packaged Functions来执行。
select sql_text from v$sqlarea where command_type = 47 and length(sql_text) > 500;
此外，可以适当的将这些匿名的PL/SQL blocks pin入内存，如下：
declare /* KEEP_ME */ x number;
begin x := 5;
end;
select address, hash_value from v$sqlarea where command_type = 47 and sql_text like ‘%KEEP_ME%’;
execute dbms_shared_pool.keep(‘address, hash_value’);
11)其他影响library pool的参数
** open_cursors：此参数定义了user进程可分配到的私有SQL area中可以引用的cursor的数量。这些私有SQL area将一直存在，直到cursor被关闭。所以应用中应该及时关闭不用的cursor。
** cursor_space_for_time：其为Boolean值，默认为false，如果设置为true，则共享 SQL areas中将不会把其标识为过期，直到其被关闭。不要将其设置为true，除非v$librarycache中的reload始终保持0时再考虑。当应 用中使用了form或大量的动态SQL，应设置为false。
** session_cached_cursors：此参数用于同一个user会经常解析同一个sql的情况。这会经常出现在form的应用中。当设置了该值，会将关闭的游标的解析仍然cache在内存中，用于后面的软解析。
为了验证设置是否恰当，可以查证v$sesstat中的”session cursor cache hits”和”parse count”的值，如果parses 结果的hits比较小，则考虑增加该值，但是，其增加的开支来自于memory。
3、data dictionary cache及其术语、tuning：
1）了解一些术语：
gets：对某object请求的总次数；
getmisses：显示了data请求造成cache misses的次数
当instance被刚刚startup时，dictionary cache中是空的，所以任何sql都会引起getmisses的增加，但是随着大量data被读入cache，getmisses也会减少。最终，将达到一个稳定的平衡状态。
对data dictionary的调节只能通过间接的调节shared_pool_size。
诊断data dictionary的工具有：
** 视图v$rowcache：主要需要关注的字段是parameter、gets和getmisses
** 在statspack的report中，有相关的内容，其中每个数据字典项的misses的百分比大多数应该< 2%,整个Dictionary Cache应该< 15%。report中的cache usage是cache的实体被使用的次数。
sql>select 1-sum(getmisses)/sum(gets) "dta dic hitration" from v$rowcache;
pct SGA是用于data dictionary cache的SGA的比率。
查看getmisses比gets的总比率的方法：
select parameter, gets, getmisses from v$rowcache where parameter=’dc_objects’ or parameter=’dc_synonyms’;
应尽量将该比率降低到<15%的情况。否则，考虑增加shared_pool_size。
4、UGA和Oracle shared Server
在不同的Oracle Server模式下，UGA的位置也不同，具体如下图：
如果使用的Oracle Shared Server模式，并且没有设置large pool，则user的session data和cursor state将存储在shared pool中，而非dedicated Server模式中的PGA中。sort area和private SQL area包含在session data中。在这种模式的Server中应增加shared_pool_size，PGA将变小。
相关的查询：
select sum(value) || ‘ bytes’ “total session memory” from v$mystat, v$statname
where v$statname.name=’session uga memory’ and v$mystat.statistic#=v$statname.statistic#;
select sum(value) || ‘ bytes’ “total session memory” from v$sesstat, v$statname
where v$statname.name=’session uga memory’ and v$sesstat.statistic#=v$statname.statistic#;
select sum(value) || ‘ bytes’ “total session memory” from v$mystat, v$statname
where v$statname.name=’session uga memory max’ and v$mystat.statistic#=v$statname.statistic#;
5、Large Pool
它是在shared_pool_size之外被分配的，设置它的主要好处在于：
1.它可以用于给I/O服务器进程(dbwr_io_salves,操作系统不支持异步IO，用它来模拟异步)
2.backup、resort进程提供所需的较大的内存，
3.共享服务器的会话内存
4.并行查询消息处理
从而降低shared pool产生碎片的可能及其带来的开支。
它的大小由参数large_pool_size决定。
sql>show parameter large_pool_size
它的使用情况：
sql>select * from v$sgastat where pool='large pool'