oracle shared pool的基本原…

原文地址：oracle shared pool的基本原理 作者：无名增士

shared pool的管理
一、转储shared pool 共享内存的内容
      alter session set events 'immediate trace name heapdump level 2';
   


二、shared pool 通过free lists管理free内存块，free的内存块按不同size被划分到不同的部分（bucket）进行管理。
三、
    1、初始时，数据库启动以后，shared pool多数是连续内存块。但是当空间分配使用以后，内存块开始被分割，碎片开始出现，bucket列表开始变长。oracle请求shared pool空间时，首先进入相应的bucket进行查找。如果找不到，则转向下一个非空的bucket，获取第一个内存块，分割这个内存块，剩余的部分会进入相应的bucket，进一步增加碎片。最终的结果是，由于不停的分割，每个bucket上的内存块越来越多，越来越碎小。通常bucket 0的问题会最为显著，share pool的碎片过多，是shared pool产生性能问题的主要原因。碎片过多会导致free lists链表过长，搜索这个free lists的时间就会变长，从而可能导致shared pool latch被长时间持有。而在大多是情况下，用户请求的都是相对小的内存块，这样搜索 bucket 0往往消耗大量的时间以及资源，latch的争用此时就会成为一个非常严重的问题。
    2、为了增加对大共享池的支持，shared pool latch 从原来的一个增加到现在的7个，如果用户的系统有4个或者4个以上的cpu，oracle可以把shared pool分割为多个子缓冲池进行管理，每个subpool都拥有独立的机构、LRU和shared pool latch。 子缓冲的数量由一个新引入的隐含参数设置 _kghdsidx_count。
改变隐含参数的值
  alter system set "_kghdsidx_count"=2 scope=spfile;
查看隐含参数的值
  select a.ksppinm,b.ksppstvl from  x$ksppi a,ksppsv b
  where a.indx=b.indx and a.ksppinm='_kghdsidx_count';
四、了解 X$KSMSP视图（Kernal Storage Memoty Management SGA HeaP）
      1、其中每一行都代表shared pool中的一个chunk（内存块）
      2、主要字段的解释
            ksmchcom：是注释字段，每个内存块被分配以后，注释会添加在该字段中。
            ksmchsiz：代表块大小
            ksmchcls：代表类型，主要有4类
                  1）free
                        free chunks 不包含任何对象的chunk，可以不受限制的被自由分配。
                  2）recreate
                        recreatable chunks：包含可以被临时移出内存的对象，在需要的时候，这个对象可以被重新创建。例如，许多存储共享sql代码的内存都是可以重建的。
                  3）freeable
                        freeable chunks：包含session周期或调用的对象，随后可以释放。这部分内存有时候可以全部或部分提前释放，但是注意,由于某些对象是中间过程产生的，这些对象不能临时被移除内存（因为不可以重建）
                  4）perm
                        permanent memory chunks：包含永久对象，通常不能独立释放。
五、诊断和解决ora-04031错误
    1、什么是ora-04031错误
        当尝试在共享池分配大块的连续内存失败（很多时候是由于碎片过多，而并非真是内存不足）时，oracle首先清除共享池中当前没有使用的多有对象，使空闲内存合并。如果任然没有足够大德单块内存可以满足需要，就会差生ora-04031错误。
一下伪代码可以描述ora-04031错误的产生：
 


五、使用flush shared pool 缓解共享池问题
    1、 刷新共享池：
      alter system flush shared pool；刷新共享池可以帮助合并碎片，强制老化sql，释放共享池，但是这通常是不推荐的做法。因为
          1）flush shared pool 会导致当前未使用的cursor被清除共享池，如果这些sql随后需要执行，那么数据库将经历大量的硬解析，系统将会经历严重的cpu争用，数据库将会产生激烈的latch竞争。
          2）如果应用没有使用绑定变量，大量类似的sql不停的执行，那么flush shared pool可能会带来短暂的改善，数据库很快会回到原来的状态。
          3）如果shared pool很大，并且系统非常的繁忙，刷新shared pool可能会导致系统挂起，对于类似系统尽量在系统空闲时进行。
六、绑定变量和cursor_sharing
      cursor_sharing参数有三个值：
      similar：该参数指定oracle在存在柱状图信息时，对于不同的变量值会重新解析，从而可以利用柱状图更为精确地制定sql执行计划。即存在柱状图时，similar的表现和exact相同；当柱状图信息不存在时，similar的表现和force相同。
七、shared_pool_reserved_size参数的设置和作用
      1、shared_pool_reserved_size参数指定了保留的共享池空间，用于满足大的连续的共享池空间请求。当共享池出现多碎片时，请求大块空间会导致oracle大范围地查找并释放共享池内存来满足请求，由此可能会带来较为严重的性能下降，设置合适的shared_pool_reserved_size参数和_shared_pool_reserved_min_alloc参数可以避免由此导致的性能下降。_shared_pool_reserved_min_alloc这个参数控制这保留内存的控制和分配。缺省值为4400bytes，即当请求的内存大于这个值时就进入共享池保留空间分配内存。
        2、查看v$shared_pool_reserved视图可以判断共享池问题的引发原因。
              select free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size from v$shared_pool_reserved；
      若request_failures大于0且last_failure_size大于shared_pool_reserved_min_alloc，那么ora-04031错误就可能是因为共享池保留空间缺少连续空间所致。要解决这个问题，可以考虑加大shared_pool_reserved_min_alloc来降低缓冲进共享池保留空间的对象数目。并增大shared_pool_reserved_size 和 shared_pool_size来加大共享池保留空间的可用内存。如果request_failures > 0 并且 last_failure_size < _shared_pool_reserved_min_alloc，那么是因为在库高速缓冲缺少连续空间导致ORA-04031错误。对于这一类情况应该考虑降低_shared_pool_reserved_min_alloc，以放入更多的对象到共享池保留空间并加大shared_pool_size。
八、 library cache pin和 library cache lock分析
      1、oracle 使用两种数据结构来进行shared pool的并发访问控制：lock和pin。lock比pin具有更高的级别。lock在handle上获得，在pin一个对象之前，必须首先获得该handle的锁定。锁定主要有3种模式：null ，share和exclusive。在读取对象时，通常需要获取null模式以及share模式的锁定。在修改对象时，需要获得exclusive锁定。在锁定library cache对象以后，一个进程在访问前必须pin该对象。同样pin有三种模式：null、shared和exclusive。只读模式获得共享pin，修改模式获得排他pin，那么数据库此时就要产生等待。
      2、oracle官方文档上这样介绍library cache pin等待事件：library cache pin是用来管理library cache的并发访问的。pin一个object会引起相应的heap被载入内存中（如果此前没有被加载）。当library cache pin等待事件出现时，通常说明该pin被其他用户以非兼容模式持有。library cache pin的等待时间为3秒。P1：代表KGL Handle address即library cache pin等待对象的handle地址。library cache pin 通常发生在编译或重编译PL/SQL、VIEW、TYPES 等object时，编译通常都是显性的，如安装应用程序、升级、安装不定程序等，另外，alter 、grant和revoke等操作也会使object变的无效，可以通过object的last_ddl_time观察这些变化。当object变的无效时，oracle会在第一次访问此object时试图去重新编译它，如果此时其他session已经把此object pin到library cache中，就会出现问题，特别是当有大量的活动session并且存在较复杂的dependence时。在某种情况下，重新编译object可能会花几个小时时间，从而阻塞其他试图访问此object的进程。

        3、分析library cache pin的步骤
              1）从v$session_wait入手，可以看到那些session正在经历library cachepin的等待，取出P1值。
              2）利用P1值查询想 x$kglob（ Kernel Generic  Library Cache Manager OBject）表，确认library cache pin
              3）联合v$session和x$kglpn（
Kernel Generic  Library Cache Manager object Pi Ns）获得当前持有该handle的用户信息。
              3）通过v$session.sql_hash_value,v$session.sql_address等字段关联v$sqltext、v$sqlarea获得当前session正在执行的操作。
          4、recompile过程包含以下步骤，同时来看以下lock和pin如何交替发挥作用的。
              1）存储过程的library cache  object以排他模式被锁定，这个锁定是在handle上获得的。exclusive锁定可以防止其他用户执行同样地操作，同时防止其他用户创建新的引用此过程的对象。
              2）以shared 模式pin该对象，以执行安全和错误检查。
              3）共享pin被释放，重新以排他模式pin该对象，执行重编译
              4）使所有依赖该过程的对象失效。释放exclusive lock和exclusive pin。
             
          5、可能发生library cache pin和library cache lock的情况：
                1）在存储过程或者函数正在运行时被编译。
                2）在存储过程或者函数正在运行时被对它们进行授权、或者移除权限等操作。
                3）对某个表执行DDL期间，有另外的会话对该表执行DML或者DDL。
                4）PL/SQL对象之间存在复杂的依赖性

每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件'library cache pin'或'library cache lock'直到超时.
超时,通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误。

例如：
SES1:
执行：exec p_sleep;
假设存储过程p正在运行，且运行时间很长
SES2:
执行：grant execute on p_sleep to system
对p进行编译，如果之前没有其他会话lock存储过程p的handle，则本会话会将获取p的handle锁定；但会话pin p时会失败，此时在SES2上产生library cache pin等待。如果超过5分钟仍然不能完成pin p，则会报错：
ORA-04021: 等待锁定对象 SUK.P_SLEEP 时发生超时。此时，本会话会释放p的handle lock。(也可能是ORA-04020错误)
SES3:
执行：grant execute on p_sleep to system
在这个会话中继续编译p，则该会话在获取p的handle锁定时会失败，在本会话产生library cache lock等待。如果SES2超时，则本会话会获取p的handle lock，v$session_wait上的等待事件也由library cache lock变成ibrary cache pin，直到超时。

library cache pin
查询v$session_wait视图中library cache pin对应的P1、P2、P3
P1 = Handle address
这个就是引起library cache pin等待的对象被pin到library cache中的handle。一般用P1RAW(十六进制)代替p1(十进制)
可以用以下sql查询那个用户下的那个对象正在被请求pin：
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr='&P1RAW'
;
返回的OBJECT可能是具体的对象，也可能是一段SQL。

九、version_count过高造成的latch竞争解决
    1、select sid，event，p1，p1raw from v$session_wait；
          可以发现有大量的latch等待
    2、接下来就是查看这些latch free等待的是那些latch
          select addr,latch#,name,gets,spin_gets from v$latch
          order by spin_gets;
          可以看到，在当前数据库中竞争最严重的两个latch是shared pool和library cache。这两个latch是shared pool管理中最重要也是最常见的latch竞争。
          1）shared pool latch用于共享池中内存空间的分配和回收，如果sql没有充分共享，反复解析的过程是十分昂贵的。而library cache latches用于保护cache在内存中的sql以及对象定义等，当需要向library cache增加新的sql时，library cache latch必须被获得。在 解析sql过程中，oracle 搜索library cache查找匹配的sql，如果没有可共享的sql代码，oracle将分析sql，获得library cache latch向library cache中插入新的sql代码。library cache latch的数量受一个隐含参数 _kgl_latch_count的控制。
        2）一下是sql的执行过程，说明这两个latch在sql解析过程中所起的作用
          1】首先需要获得library cache latch，根据sql的hash_value值在library cache中寻找是否存在可共享的代码。如果找到则为软解析，server进程获得该sql执行计划，转向第四步；如果找不到共享代码则执行硬解析。
          2】释放library cache latch，获取shared pool latch，查找并锁定自由空间。
          3】释放shared pool latch，重新获得library cache latch，将sql及执行计划插入到library cache中。
          4】释放library cache latch，保持null模式的library cache pin/lock.
          5】开始执行。
      3、通过查询v$sysstat视图获得关于数据库解析的详细信息。
      4、查看v$sqlarea
            version_count过高，就意味着同一条sql语句（具有相同的hash值）被多次执行硬解析。如果其值过高，就会导致同一个bucket的链表过长，如果同样地sql再次执行时，oracle将不得不搜索这个链表以寻找可以共享的sql，这将导致大量的library cache latch的竞争。解决的方法是
              1】将cursor_sharing设置为force。
              2】设置隐含参数 _sqlexec_progression_cost=0;即sql执行进度监控成本阈值。这个参数根据cost来决定需要监控的sql。执行进度监控会引入额外的函数调用和row resource这个可能导致sql的执行计划或成本发生变化，从而使version_count值增加。_sqlexec_progression_cost的缺省值为1000，成本大于1000的所有sql都会被跟踪，如果该参数设置为0，那么sql的执行进度将不会被跟踪。执行进度监控信息会被记录到v$session_longops视图中，如果time_statistics参数设置为false，那么这个信息就不会被记录。
对于version_count过高的问题，可以查询v$sql_shared_cursor视图，这个试图会给出sql不能共享的具体原因，如果是正常因素导致的，相应的字段会被标记为‘Y’；对于异常的情况，查询结果可能显示的都是‘N’。