library cache pin和library cache lock分析

八、 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 （ K ernel  G eneric    L ibrary Cache Manager  OB ject）表，确认library cache pin
               3）联合v$session和x$kglpn（
K ernel  G eneric    L ibrary Cache Manager object  P i N s）获得当前持有该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’。