如安装应用程序,升级,安装补丁程序等,另外,"ALTER","GRANT","REVOKE"等操作也会使object变得无效,
可以通过object的"LAST_DDL"观察这些变化.
当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin
到library cache中,就会出现问题,特别时当有大量的活动session并且存在较复杂的dependence时.在某种情况下,重新
编译object可能会花几个小时时间,从而阻塞其它试图去访问此object的进程.
首先理解一下有关shared pool中的这两个概念：
1、Hash bucket
当一条 SQL 语句进入library cache 的时候，先将 SQL 文本转化为对应 ASCII 数值，然后对该这些 ASCII 数值进行 hash 函数的运算，传入函数的是 SQL 语句的名称（name，对于 SQL 语句来说其 name 就是 SQL 语句的文本）以及命名空间（namespace，对于 SQL语句来说是“SQL AREA”，表示共享游标。可以从视图 v$librarycache 里找到所有的 namespace）。运用hash 函数后得到一个值，该值就是 hash bucket 的号码，从而该 SQL 语句被分配到该号的 hash bucket 里去。实际上，hash bucket 就是通过串连起来的对象句柄才体现出来的，它本身是一个逻辑上的概念，是一个逻辑组，而不像对象是一个具体的实体。SQL 语句相关的对象有很多（最直观的就是 SQL 语句的文本），这些对象都存放在 library cache 里，它们都通过句柄来访问。可以把 library cache理解为一本书，而SQL 语句的对象就是书中的页，而句柄就是目录，通过目录可以快速定位到指定内容的页。
2、library cache object handle
如上面所说，library cache使用多个 hash bucket 来管理，每个 hash bucket后面都串连着多个句柄（library cache object handle），这些对象句柄存放了对象的名称（name）、对象所属的命名空间（namespace）、有关对象的一些标记（比如对象是否为只读、为本地对象还是远程对象、是否被 pin 在内存中等等）以及有关对象的一些统计信息等。而且，对象句柄中还存放了当前正在 lock 住和pin 住该对象的用户列表、以及当前正在等待 lock 和pin 该对象的用户列表。
Library Cache Lock/Pin
Library cache latch
Library cache latch用来控制对library cache object的并发访问。
由于在对object操作过程中，在handle持有的 lock很容易被打破(修改或删除等dml操作)， Oracle为了保护这个lock，引入了library cache latch机制，也就是说在获得library cachelock之前，需要先获得library cache latch，当获得library cache lock之后就释放librarycache latch。如果某个librarycache object没有在内存中，那么这个lock就不能被获取，这个时候需要获得一个library cache load lock latch，然后再获取一个librarycache load lock,当load lock获得之后就释放library cache load lock latch。
Pin和Lock
Library cache Handle里保存了lock和pin的信息。而且在Library cache handle 和child cursor 上都有lock 和pin。它们称为library cache lock和library cache pin。
Library Cache lock模式
（1）Share(S)：当读取一个library cache object的时候获得
（2）Exclusive(X)：当创建/修改一个library cache object的时候获得
（3）Null(N)：用来确保对象依赖性
NULL锁主要的目的就是标记某个对象是否有效。比如一个SQL语句在解析的时候获得了NULL 锁，如果这个SQL的对象一直在共享池中，那么这个NULL锁就会一直存在下去，当这个SQL语句所引用的表被修改之后，这个NULL锁就被打破了，因为修改这个SQL语句的时候会获得Exclusive 锁，由于NULL锁被打破了，下次执行这个SQL的时候就需要从新编译。
Library Cache pin模式
0 no lock/pin held（N）
2 share mode（S）
3 exclusive mode（X）
Library cachelock/pin是用来控制对librarycache object的并发访问的。Lock管理并发，pin管理一致性，lock是针对于librarycache handle, 而pin是针对于heap。
比如用户A以N模式持有某个handle lock，并且把该handle中的object以S模式pin到了内存中，此刻用户B发出一个请求，想要编辑该object，首先B会在handle上获得X模式的lock，然后再获取该object上X模式的pin，此时如果用户A还持有S的pin，用户B将等待A释放该pin后再去以X模式的pin去编译该object。
首先理解一下有关shared pool中的这两个概念：
1、Hash bucket
当一条 SQL 语句进入library cache 的时候，先将 SQL 文本转化为对应 ASCII 数值，然后对该这些 ASCII 数值进行 hash 函数的运算，传入函数的是 SQL 语句的名称（name，对于 SQL 语句来说其 name 就是 SQL 语句的文本）以及命名空间（namespace，对于 SQL语句来说是“SQL AREA”，表示共享游标。可以从视图 v$librarycache 里找到所有的 namespace）。运用hash 函数后得到一个值，该值就是 hash bucket 的号码，从而该 SQL 语句被分配到该号的 hash bucket 里去。实际上，hash bucket 就是通过串连起来的对象句柄才体现出来的，它本身是一个逻辑上的概念，是一个逻辑组，而不像对象是一个具体的实体。SQL 语句相关的对象有很多（最直观的就是 SQL 语句的文本），这些对象都存放在 library cache 里，它们都通过句柄来访问。可以把 library cache理解为一本书，而SQL 语句的对象就是书中的页，而句柄就是目录，通过目录可以快速定位到指定内容的页。
2、library cache object handle
如上面所说，library cache使用多个 hash bucket 来管理，每个 hash bucket后面都串连着多个句柄（library cache object handle），这些对象句柄存放了对象的名称（name）、对象所属的命名空间（namespace）、有关对象的一些标记（比如对象是否为只读、为本地对象还是远程对象、是否被 pin 在内存中等等）以及有关对象的一些统计信息等。而且，对象句柄中还存放了当前正在 lock 住和pin 住该对象的用户列表、以及当前正在等待 lock 和pin 该对象的用户列表。
Library Cache Lock/Pin
Library cache latch
Library cache latch用来控制对library cache object的并发访问。
由于在对object操作过程中，在handle持有的 lock很容易被打破(修改或删除等dml操作)， Oracle为了保护这个lock，引入了library cache latch机制，也就是说在获得library cachelock之前，需要先获得library cache latch，当获得library cache lock之后就释放librarycache latch。如果某个librarycache object没有在内存中，那么这个lock就不能被获取，这个时候需要获得一个library cache load lock latch，然后再获取一个librarycache load lock,当load lock获得之后就释放library cache load lock latch。
Pin和Lock
Library cache Handle里保存了lock和pin的信息。而且在Library cache handle 和child cursor 上都有lock 和pin。它们称为library cache lock和library cache pin。
Library Cache lock模式
（1）Share(S)：当读取一个library cache object的时候获得
（2）Exclusive(X)：当创建/修改一个library cache object的时候获得
（3）Null(N)：用来确保对象依赖性
NULL锁主要的目的就是标记某个对象是否有效。比如一个SQL语句在解析的时候获得了NULL 锁，如果这个SQL的对象一直在共享池中，那么这个NULL锁就会一直存在下去，当这个SQL语句所引用的表被修改之后，这个NULL锁就被打破了，因为修改这个SQL语句的时候会获得Exclusive 锁，由于NULL锁被打破了，下次执行这个SQL的时候就需要从新编译。
Library Cache pin模式
0 no lock/pin held（N）
2 share mode（S）
3 exclusive mode（X）
Library cachelock/pin是用来控制对librarycache object的并发访问的。Lock管理并发，pin管理一致性，lock是针对于librarycache handle, 而pin是针对于heap。
比如用户A以N模式持有某个handle lock，并且把该handle中的object以S模式pin到了内存中，此刻用户B发出一个请求，想要编辑该object，首先B会在handle上获得X模式的lock，然后再获取该object上X模式的pin，此时如果用户A还持有S的pin，用户B将等待A释放该pin后再去以X模式的pin去编译该object。
Latch及latch冲突
引言
Oracle Rdbms应用了各种不同类型的锁定机制，latch即是其中的一种，本文将集中介绍latch(闩)的概念，理解latch的实现方法并说明引起latch冲突的原因。
什么是latch
Latch是用于保护SGA区中共享数据结构的一种串行化锁定机制。Latch的实现是与操作系统相关的，尤其和一个进程是否需要等待一个latch、需要等待多长时间有关。
Latch是一种能够极快地被获取和释放的锁，它通常用于保护描述buffer cache中block的数据结构。与每个latch相联系的还有一个清除过程，当持有latch的进程成为死进程时，该清除过程就会被调用。Latch还具有相关级别，用于防止死锁，一旦一个进程在某个级别上得到一个latch，它就不可能再获得等同或低于该级别的latch。
Latch与Enqueue（队列）
Enqueue是Oracle使用的另一种锁定机制，它更加复杂，允许几个并发进程不同程度地共享某些资源。任何可被并发使用的对象均可使用enqueue加以保护。一个典型的例子是表的锁定，我们允许在一个表上有不同级别的共享。与latch不同之处在于，enqueue是使用操作系统特定的锁定机制，一个enqueue允许用户在锁上存贮一个标记，来表明请求锁的模式。操作系统lock manager跟踪所有被锁定的资源，如果某个进程不能获取它所请求的那种锁，操作系统就把请求进程置于一个等待队列中，该队列按FIFO原则调度，而在latches中是没有象enqueue中排序的等待队列，latch等待进程要么使用定时器来唤醒和重试，要么spin(只用于多处理器情况下)。
何时需要latch
当一个进程准备访问SGA中的数据结构时，它就需要获得一个latch。当进程获得latch后，它将一直持有该latch直到它不再使用此数据结构，这时latch才会被释放。可通过latch名称来区分它所保护的不同数据结构。
Oracle使用元指令对latch进行操作, 当所需的latch已被其他进程持有时，执行指令进程将停止执行部分指令，直到该latch被释放为止。从根本上讲，latch防止并发访问共享数据结构，由于设置和释放latch的指令是不可分割的，操作系统就可以保证只有一个进程获得latch，又由于这只是单条指令，所以执行速度很快。latch被持有的时间是很短，而且提供了当持有者不正常中断时的清除机制，该清除工作是由Oracle后台进程PMON来完成的。
什么导致latch冲突
Latch保护SGA中的数据结构被多个用户同时访问，如果一个进程不能立即得到所需latch，它就必须等待，这就导致了CPU的额外负担和系统的速度降低。额外的CPU使用是进程‘spining’导致的，‘spining’是指进程定时地重复尝试获取latch，在连续两次之间，进程处于休眠状态，在得到latch之前，spining过程将重复进行下去。
如何标识内部latch的冲突
Server manager monitor是一个相当有用的来监视latch等待、请求和冲突的工具。也可查询相关的数据字典表：v$latch, v$latchholder, v$latchname。
v$latch表的每一行包括了对不同类型latch的统计，每一列反映了不同类型的latch请求的活动情况。不同类型的latch请求之间的区别在于，当latch不可立即获得时，请求进程是否继续进行。按此分类，latch请求的类型可分为两类：willing-to-wait和immediate。
Willing-to-wait : 是指如果所请求的latch不能立即得到，请求进程将等待一很短的时间后再次发出请求。进程一直重复此过程直到得到latch。
Immediate：是指如果所请求的latch不能立即得到，请求进程就不再等待，而是继续执行下去。
在v$latch中的以下字段反映了Willing-to-wait请求：
GETS---成功地以Willing-to-wait请求类型请求一个latch的次数。
MISSES---初始以Willing-to-wait请求类型请求一个latch不成功的次数。
SLEEPS---初始以Willing-to-wait请求类型请求一个latch不成功后，进程等待获取latch的次数。
在v$latch中的以下字段反映了Immediate类请求：
IMMEDIATE_GETS---以Immediate请求类型成功地获得一个latch的次数。
IMMEDIATE_MISSES---以Immediate请求类型请求一个latch不成功的次数。
我们可以通过对v$latch, v$latchholder, v$latchname的查询获得有关latch信息，例如：
/* 已知一个latch地址，找到latch名字 */
col name for a40
select a.name from v$latchname a, v$latch b
where b.addr = '&addr'
and b.latch#=a.latch#;
/* 显示系统范围内的latch统计 */
column name format A32 truncate heading "LATCH NAME"
column pid heading "HOLDER PID"
select c.name,a.addr,a.gets,a.misses,a.sleeps,
a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+)
and a.latch# = c.latch#
order by a.latch#;
/* 由latch名称显示对latch的统计 */
select c.name,a.addr,a.gets,a.misses,a.sleeps,
a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+) and a.latch# = c.latch#
and c.name like '&latch_name%' order by a.latch#;
latch有40余种，但作为DBA关心的主要应有以下几种：
Cache buffers chains latch: 当用户进程搜索SGA寻找database cache buffers时需要使用此latch。
Cache buffers LRU chain latch: 当用户进程要搜索buffer cache中包括所有 dirty blocks的LRU (least recently used) 链时使用该种latch。
Redo log buffer latch: 这种latch控制redo log buffer中每条redo entries的空间分配。
Row cache objects latch: 当用户进程访问缓存的数据字典数值时，将使用Row cache objects latch。
下面我们将着重介绍一下如何检测和减少redo log buffer latch的冲突。对redo log buffer的访问是由redo log buffer latch来控制的，这种latch有两种类型， redo allocation latch和redo copy latch。
Redo allocation latch控制redo entries在redo log buffer中的空间分配。Oracle的一个用户进程只有得到redo allocation latch后才能为redo entries在redo log buffer中分配空间，又由于一个instance只有一个redo allocation latch，所以一次只有一个用户进程在buffer中分配空间。当用户进程获得latch后，首先为redo entry分配空间，然后进程继续持有latch并拷贝entry到buffer中，这种拷贝称为“在redo allocation latch上的拷贝”(copying on the redo allocation latch)，拷贝完毕后，用户进程释放该latch。
一个“在redo allocation latch上的拷贝”的redo entry的最大值是由初始化参数LOG_SMALL_ENTRY_MAX_SIZE定义的，根据操作系统的不同而不同。
Redo Copy Latch只应用于多CPU的系统。在多CPU的instance中，如果一个redo entry太大，超过了LOG_SMALL_ENTRY_MAX_SIZE定义值，则不能进行“在redo allocation latch上的拷贝”, 此时用户进程必须获取redo copy latch。一个instance中可以有多个redo copy latch，其数目由初始参数LOG_SIMULTANEOUS_COPIES决定，缺省值为CPU数目。
在单CPU情况下，不存在redo copy latch，所有的redo entry无论大小, 都进行“在redo allocation latch上的拷贝”。
对redo log buffer的过多访问将导致redo log buffer latch的冲突，latch冲突将降低系统性能，我们可通过如下查询来检测这种latch冲突：
col name for a40
SELECT ln.name,gets,misses,immediate_gets,immediate_misses
FROM v$latch l,v$latchname ln
WHERE ln.name IN('redo allocation','redo copy') AND ln.latch#=l.latch#
/
若misses与gets的比例超过1%或immediate_misses与(immediate_gets+immediate_misses)比例超过1%时，应考虑采取措施减少latch的冲突。
大多数的redo log buffer latch冲突是在多个CPU情况下，两个或多个Oracle进程试图同时得到相同的latch发生的。由于一个instance只有一个redo allocation latch，为减少redo allocation latch的冲突，应减少单个进程持有latch的时间，这可以通过减小初始参数LOG_SMALL_ENTRY_MAX_SIZE以减小redo entry的数目和大小来实现。如果观察到有redo copy latch冲突，可以通过增大LOG_SIMULTANEOUS_COPIES 初始参数来加大latch数目，其缺省值为CPU数目，最大可增大到CPU数目的两倍。
如何降低library cache lock？
我们首先要确认的是 library cache 的竞争是整个系统层面的还是只发生在某个或某些 SQL 语句上。这个"library cache lock"是被一个特定的 SQL 持有很长的时间吗？或者总是在等待某
个特定的对象？还是说这个锁在短时间内被请求的次数很多从而造成的竞争？
如果问题是在整个系统层面发生的，一般来说是由于 shared pool 太小或 SQL 语句不共享造成的。一些解决竞争的方法：
增大 shared pool 从而减少 reload 的次数，这是因为 shared pool 过小会造成获取锁的时间加长。
通过将 cursor_sharing 设置为 similar 或 force 来使 SQL 语句共享。
需要小心的是这样做可能会改变SQL的执行计划，所以做之前需要做完整的测试。
在系统不繁忙的时候做统计信息的收集或其它维护作业，从而降低无效化（invalidation）的次数。
如何降低library cache pin
如果"library cache pin"等待的时间很长那么很重要的一点就是判断是只有一两个 process 在等待还是有很多的 process 都在等待。
如果说只是一两个 process 被另一个 process 阻塞的话，那么需要检查持有这个 pin 的 process 为什么这么长时间不释放。
如果说等待是大范围的那么说明 shared pool 需要优化
第一：原理知识：
1、什么是library cache lock？
官方文档有如下说明：
This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:
One client can prevent other clients from accessing the same object
The client can maintain a dependency for a long time (for example, no other client can change the object)
This lock is also obtained to locate an object in the library cache.
Wait Time: 3 seconds (1 second for PMON)
Parameter Description
object address Address of the object being loaded
lock address Address of load lock being used
mask Indicates which data pieces of the object that needs to be loaded
翻译一下：在这个library cache lock控制library cache的并发管理，以便。
1）一个客户端可以阻止其他的客户端访问同一个对象
2）这个客户端可以长时间的维护一个依赖对象（例如，没有其他用户可以修改该对象）
这种锁在用户试图在library cache中查询定位对象的时候也会获得。对于该锁有三种模式分别是null、share、exclusive，对于该锁的获得等待三秒，一秒用于pmon进程如果超过3秒那么就
会产生等待。
那对于上面说的1和2怎么理解呢？
在我们解析sql或是编译pl/sql语句期间，我们需要获得被应用的数据库对象（表，视图，过程，函数，包，包体，触发器，索引，聚簇，同义词）的library cache lock；这个锁在解析与编
译结束时会被释放。注意：cursor（sql与pl/sql区），管道（pipes）和其他瞬时（transient）对象不适用该锁，library cache lock上的死锁不会被自动检测到，对其的操作是瞎子进行的
。
2、为什么需要library cache lock
library cache lock是在对象和他们的sql语句之间维持了一种依赖对象机制，假如对象的定义（如字段）被重新定义了或是这个解析锁被“损坏”了，那么依赖对象肯定也失效了，这种维持就
是通过library cache lock实现的。举个例子来说，如果一个表的字段被删除了，那么所有依赖该表的sql将都会变的失效了，必须在下一次访问该 对象的时候重新进行解析，那么library
cache lock就是实现这种机制。而这也是需要有一个队列的，library cache lock是在library cache handle上获得，如果存在不兼容的模式，那么其他的会话必须这时候进行等待。
3、什么是library cache pin？
官方介绍为：
This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the
client must acquire a pin after the lock.
Wait Time: 3 seconds (1 second for PMON)
Parameter Description
handle address Address of the object being loaded
pin address Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is basically a State Object.
mode Indicates which data pieces of the object that needs to be loaded
namespace
翻译一下：
这个library cache pin是维护管理library cache的并发性。在pin一个对象的时候将会把heap载入到内存中，如果一个会话视图编辑或是检查这个对象的时候在获得lock之后必须获得pin。
对于heap怎么理解？它是内存堆，里面包含了很多信息，可以看一下下图就明白了。
同样library cache pin也有三种模式分别是null，share、exclusive，当需要访问被缓存到library cache中的数据对象的时候（如，表，索引，聚簇，同义词，视图，过程，包，包体，触
发器、）必须要先pin，当出现library cache pin等待的时候那么这个对象正在被其他会话进行不兼容的模式持有。对于以上的参数，下边会有介绍，就不在这个小命题说了。
4、我为什么需要library cache pin？
library cache pin是为了处理当前执行依赖对象的，例如，当一个会话在执行访问（sql）这个加载的对象的时候不应该被修改。也就是说，当一个解析锁在没有释放之前，其他会话必须获
得library cache pin 的exclusive模式才能将该对象进行修改。同时，可以想象一个长的sql可能会导致library cache pin的等待。
到了这里我发现，对于library cache 有两种锁，一种为lock另一种是pin，lock是存在于library cache object handle上的，而pin是为了在内存堆维持object的一致性。oracle在sql解析
或是编译对象的时候需要获得library cache lock和library cache pin，防止其他会话对执行的sql对象进行修改。另外在sql的硬解析的时候会获得library cache lock，阻止其他会话对对
象的修改，那么其他会话就会发生等待。
5、什么是library cache load lock？
官方解释：
The session tries to find the load lock for the database object so that it can load the object. The load lock is always obtained in Exclusive mode, so that no other
process can load the same object. If the load lock is busy the session will wait on this event until the lock becomes available.
Wait Time: 3 seconds (1 second for PMON)
Parameter Description
object address Address of the object being loaded
lock address Address of load lock being used
mask Indicates which data pieces of the object that needs to be loaded
这种锁一般发生在编译和重新编译对象的时候出现，如果我试图重新编译一个失效的对象，而这个对象正好也被其他会话给pin住了，那么会产生错误。
第二、参数分析以及咋去查？
1、x$kgllk, x$kglpn and x$kglob
x$kgllk:kernal general library lock
x$kglpn ：kernel general library pin
x$kglob:kernel general library object
查看相关结构
x$kgllk is externalizing all locking structures on an object. Entries in x$kglob acts as a resource structure. x$kglpn is externalizing all library cache pins.
对于该类视图字段，我就是猜，如x$kgllk 中的inst_id代表实例号，kgllkadr代表锁的地址，kgllkuse代表持有该锁的用户，kgllkses代表会话sid，kgllksnm代表这个锁的命名空间，
kgllkhdl代表锁定的handle地址也就是library cache object handle地址，kgllkmod代表锁的模式，kgllkreq代表需要请求的锁的类型，kgllksqlid代表该锁被sql持有的sqlid，user_name
代表用户名等。对于x$kglob和x$kglpn每个字段也是同样的方法，他们的默写字段都是可以和其他会话进行关联的，之后会发现。
2、参数分析
对于library cache pin有如下参数：
Parameter Description
handle address Address of the object being loaded
pin address Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is basically a State Object.
mode Indicates which data pieces of the object that needs to be loaded
namespace namespace&&encoded mode
这三个参数其实以前我就见过就是对应的v$session中字段p1、p2、p3而p1raw代表是p1的十六进制形式p2raw和p3raw都是一样的，需要说明的是v$session中的这三个字段对于不同的情况有不
同的含义，而现在我们在研究library cache pin那么这三个字段表示内容如下：
p1=handle address
p2=pin address
p3=namespace&&encoded mode
对于p1raw可以对应于x$kglob中的KGLHDADR字段x$kglpn中的KGLPNHDL字段，x$kgllk中的KGLLKHDL字段，后边有sql关联。
对于p2raw代表pin它自己的地址
对于namespace&&encoded mode有如下内容：
In Oracle 7.0 - 8.1.7 inclusive the value is 10 * Mode + Namespace.
In Oracle 9.0 - 11.1 inclusive the value is 100 * Mode + Namespace.
mode 代表这个pin需要获得什么样的锁。
2：代表shared mode
3：代表exclusive mode
namespace仅仅是一个在library cache的namespace的数值，其中每个数值含义如下:
0 sql area
1 table/procedure/function/package header
2 package body
3 trigger
4 index
5 cluster
6 object
7 pipe
13 java source
14 java resource
32 java data
3、诊断方式
常见的情况有如下两项：往往对于这种诊断可以更加准确的确定问题。
1）当我在一个对象上执行dml语句的时候，其他会话正在视图改变该表的定义，如alter table xxxx modify等，这个时候dml就会被hang住，需要等待的时间根据这个表的大小不同而不同。
这个时候我在v$lock视图上会看到LMODE=6, TYPE=TM 并且id1也指向了操作的object_id,并且还有其他session的等待获取锁的信息。
2）当我们在编译一个procedure或是packet的时候，会获得library cache lock以及library cache pin，其他会话去使用它们，那么这个时候也是会产生等待。
第一种去诊断的方式是通过oradebug进行查看信息或是通过alter system set events 'immediate trace name systemstat level 10';来进行查看内部信息：
注意：
As systemstate dumps are instance specific, they tend to be
inconclusive with hanging problems involving Oracle Parallel Server
(OPS) unless you get them from each node. You will need 3 system state
dumps from each node for OPS.
eg：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 266;
Statement processed.
SQL>
第二种去诊断的方式就是查看x$kgllk,x$kglob,x$kglpn,以及通过这些视图和其他视图进行关联获得想要信息。
第三、学以致用
模拟过程：
首先创建一个存储过程：
SQL> create or replace procedure rhys (amy_sleep in boolean,rhys_compile in boolean)
2 as
3 begin
4 if(amy_sleep) then
5 dbms_lock.sleep(3000);
6 elsif(rhys_compile) then
7 execute immediate 'alter procedure rhys compile';
8 end if;
9 end;
10 /
Procedure created.
SQL>
SQL>
session 1：
SQL> select * from v$mystat where rownuM<2;
SID STATISTIC# VALUE
---------- ---------- ----------
22 0 0
SQL> execute rhys(true,false);
处于等待状态：
session 2：
SQL> select * from v$mystat where rownuM<2;
SID STATISTIC# VALUE
---------- ---------- ----------
42 0 0
SQL> execute rhys(false ,true);
处于等待状态
产生是否有等待时间
SQL> select sid,username,sql_id,event,p1,p1raw,p2,p2raw,p3,p3raw,wait_time,seconds_in_wait from v$session where event='library cache pin';
SID USERNAME SQL_ID EVENT P1 P1RAW P2 P2RAW P3 P3RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------- ------------- -------------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- ---------- ---------------
42 SYS 2yv7ja732z3p0 library cache pin 2024609840 0000000078AD1830 2123642056 000000007E9434C8 4.0490E+14 0001704200010003 0 10
SQL> r
1* select sid,username,sql_id,event,p1,p1raw,p2,p2raw,p3,p3raw,wait_time,seconds_in_wait from v$session where event='library cache pin'
SID USERNAME SQL_ID EVENT P1 P1RAW P2 P2RAW P3 P3RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------- ------------- -------------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- ---------- ---------------
42 SYS 2yv7ja732z3p0 library cache pin 2024609840 0000000078AD1830 2123642056 000000007E9434C8 4.0490E+14 0001704200010003 0 15
SQL>
可以看到等待一直在增加。
通过sql_id可以得到sql
SQL> col sql_text for a60
SQL> r
1* select sql_text,sql_id from v$sqlarea where sql_id='2yv7ja732z3p0'
SQL_TEXT SQL_ID
------------------------------------------------------------ -------------
alter procedure rhys compile 2yv7ja732z3p0
SQL>
获得锁定 信息：
方法一）
下面通过oradebug查看内容：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> @trace.sql
TRACE_FILE_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------
/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_1759.trc
SQL>
查看日志发现：
client details:
O/S info: user: oracle, term: pts/2, ospid: 1929
machine: oracle-one program: sqlplus@oracle-one (TNS V1-V3)
application name: sqlplus@oracle-one (TNS V1-V3), hash value=632623916
Current Wait Stack:
0: waiting for 'library cache pin'
handle address=0x78ad1830, pin address=0x7e9434c8, 100*mode+namespace=0x1704200010003
wait_id=21 seq_num=22 snap_id=1
wait times: snap=9 min 31 sec, exc=9 min 31 sec, total=9 min 31 sec
wait times: max=15 min 0 sec, heur=9 min 31 sec
wait counts: calls=192 os=192
in_wait=1 iflags=0x15a2
There is at least one session blocking this session.
Dumping 2 direct blocker(s):
inst: 1, sid: 42, ser: 49
inst: 1, sid: 22, ser: 27
Dumping final blocker:
inst: 1, sid: 42, ser: 49
There are 0 sessions blocked by this session.
Dumping one waiter:
inst: 1, sid: 42, ser: 49
wait event: 'library cache pin'
p1: 'handle address'=0x78ad1830
p2: 'pin address'=0x7e9434c8
p3: '100*mode+namespace'=0x1704200010003
row_wait_obj#: 5541, block#: 11545, row#: 0, file# 1
min_blocked_time: 569 secs, waiter_cache_ver: 757
Wait State:
fixed_waits=0 flags=0x22 boundary=(nil)/-1
方法二：
select a.event,
a.sid,
a.SERIAL#,
a.username,
a.machine,
a.wait_time wt,
a.seconds_in_wait sw,
a.state,
p.kglpncnt,
p.kglpnmod,
p.kglpnreq,
b.kglnaown,
b.kglnaobj,
b.kglfnobj,
b.kglhdobj
from v$session a, x$kglpn p, x$kglob b
where p.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq <>0)
and p.kglpnhdl=b.kglhdadr
and a.P1RAW=rawtohex(p.kglpnhdl)
order by seconds_in_wait desc ；
刚刚开始其实我关联的是x$kglob,x$kglpn和v$session视图，但是发现这三个视图关联起来执行速度非常慢。
原因如下：
SQL> select * from v$session;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 644658511
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1378 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 1378 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 1169 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 1008 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 161 | 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 209 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."INDX"="W"."KSLWTSID")
5 - filter("W"."KSLWTEVT"="E"."INDX")
那么在对v$session查询的时候实际上是在查询x$ksuse,x$kslwt,x$ksled这三个表。倒不如直接把v$session分开。
SQL> select distinct ses.ksusenum sid,
2 ses.ksuseser serial#,
3 ses.ksuudlna username,
4 ses.ksuseunm machine,
5 ob.kglnaown obj_owner,
6 ob.kglnaobj obj_name,
7 pn.kglpncnt pin_cnt,
8 pn.kglpnmod pin_mode,
9 pn.kglpnreq pin_req,
10 w.state,
11 w.event,
12 w.wait_Time,
13 w.seconds_in_Wait
14 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
15 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
16 and ob.kglhdadr = pn.kglpnhdl
17 and pn.kglpnuse = ses.addr
18 and w.sid = ses.indx
19 order by seconds_in_wait desc
20 ;
SID SERIAL# USERN MACHINE OBJ_O OBJ_NA PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- ----- ------- ----- ------ ---------- ---------- ---------- ------------------- -------------------- ---------- ---------------
22 27 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 875
42 49 SYS oracle SYS RHYS 0 0 3 WAITING library cache pin 0 868
42 49 SYS oracle SYS RHYS 3 2 0 WAITING library cache pin 0 868
SQL>
可以看到会话2（sid为42）正在等待获得pin（exclusive），但是会话1（sid22正好获得了该对象的pin（share），因此library cache pin就产生了。
在会话 三：
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
46 0 0
SQL> execute rhys(true,false);
查询结果如下：
SID SERIAL# USERN MACHINE OBJ_O OBJ_NA PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- ----- ------- ----- ------ ---------- ---------- ---------- ------------------- -------------------- ---------- ---------------
22 27 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 2361
42 49 SYS oracle SYS RHYS 0 0 3 WAITING library cache pin 0 19
42 49 SYS oracle SYS RHYS 3 2 0 WAITING library cache pin 0 19
46 11 SYS oracle SYS RHYS 0 0 2 WAITING library cache pin 0 4
SQL>
可以看到pin在rhys上的sid为22持有share pin，在42请求获得exclusive pin，46请求获得share pin。
在session 4执行：
SQL> execute rhys(true,false);
如何我在session 5执行：
execute rhys(false ,true);
SID SERIAL# USERN MACHINE OBJ_O OBJ_NA PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- ----- ------- ----- ------ ---------- ---------- ---------- ------------------- -------------------- ---------- ---------------
22 27 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 2537
42 49 SYS oracle SYS RHYS 0 0 3 WAITING library cache pin 0 195
42 49 SYS oracle SYS RHYS 3 2 0 WAITING library cache pin 0 195
46 11 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 165
48 11 SYS oracle SYS RHYS 3 2 0 WAITING PL/SQL lock timer 0 58
43 29 SYS oracle SYS RHYS 3 2 0 WAITING library cache lock 0 0
6 rows selected
那么这时session5就会请求library cache lock。