Library Cache优化

   Library Cache用来存放sql语句和PL/SQL语句块，通过LRU算法来管理，减小代码的重新编译。当用户执行的一条语句在Library中已经有了，那么Oracle Server会直接执行该语句，而不用再编译。 
   当Library Cache的大小不够时，没有足够的空间存放已经编译过的代码，数据库效能就会下降，这个时候就可以通过两个办法来解决。
   首先是调整参数，提高Library Cache的大小，然而当内存不够时，只能考虑优化Lirary Cache的内部结构了，优化内部结构的两个目标：一是将编译(parsing)降至最低，二是减小碎片。 
   怎么判定Library Cache需要优化
     几个指标:
     Get：(Parse)The number of lookups for objects of the namespace
     Pins:(Execution)The number of reads or executions of the objects of the namespace
     Reloads:(Reparse)The number of library cache misses on the execution step,causing implict reparsing of the statement and block  

  优化方法：
     1 GetHitRatio:determines the percentage of parse calls that find a cursor to share (GetHits/Gets).This ratio should be in the high 90s in OLTP environments.If not ,you can improve the efficiency of your application code.
     SQL查询语句：select namespace,gethitratio from v$librarycache;
    
     2 ReLoads should be less than 1% of the pins,如果大于1%，应该考虑重新设定share_pool_size:
     SQL查询语句：select sum(pins),sum(reloads),sum(reloads)/sum(pins) from v$librarycache

     3 The DBA can reserve memory within the shared pool to satisfy large allocations during  operations such as PL/SQL compilation and trigger compilation.Smaller object will not fragment the reserved list,helping to ensure that the reserved list has large contiguous 
chunks of memory.
     参数shared_pool_reserved_size默认为shared_pool_size的 10%
     v$shared_pool_reserved 描述了相关reserve list 的相关信息
     request_misses: is the number of times the served list did not have a free piece of memory to satisfy the request and proceeded to start flushing objects from the LRU list
     request_failures : is the numbr of times that no memory was found to satisfy a request
     free_space : is the total free space in teh reserced list
     当request_misses等于0时，考虑
      1 增加reserve list ,这样不会影响别的内存结构的分配
      2 增加share_pool，减小从reserved list 里获取内存，但这样影响别的内存结构的分配

     5 Pin large package in the library cache

     6 Convert anonymous PL/SQL blocks

     怎么样确定Library的大小
     Testing Your Application，查出各个部分的大小
     1 For stored objects such as packages and views ,use the following query:
       select sum(sharable_mem) from v$db_object_cache where type='package' or type='package body' or type = 'function' or type='procedure'
     2 For SQL statements ,you need to query v$sqlarea after the application has been runing for a while:
       select sum(sharable_mem) from v$sqlarea where executions > 5
     3 You should also allow about 250 bytes in the shared pool per user per open cursor. This can be tested during peak times
       select sum(250*users_opning) from v$sqlarea
     4 In a test environment,You can measure shareable memory by selecting the number of open cursors for a test user.You mltiply the resulting
       value by the total number of users:
       select 250*value bytes_per_user from v$sesstat s,v$statname n where s.statistic#=n.statistic# and n.name = 'opened cursors current' and s.sid = 15
     library cache 应大于以上各部分之和