请问library cache pin和library cache lock是latch吗？对这个问题一直很模糊！

不是，Library cache pin是Oracle内部针对共享池的内存块的一种锁。
一些内存块合在一起存贮了一种对象。比如游标，一般需要两个两个内存块，一个存贮一般信息，另一个存贮执行执行计划。需要有一个总的内存块来管理这些分散的内存块，一般称这个总的内存块为句柄。Library cache lock是针对这个句柄块的锁。

感谢晶晶的回答，这个我知道，但是这些都是内存锁，难道latch不是锁内存的吗？

Library cache Lock/Pin和Latch都是锁，只不过它们的实现方式不一样。相比之下，大多数的Latch只是内存中的一些字节，通过在这些字节中设置某个值，代表Latch所保护的对象是否被占用，大部分闩没有等待者队列、占用者队列等队列，等等吧。Latch的工作方式和Library cache Lock/Pin不太一样。它们都是锁，只不过有些是暗锁，有些是明锁，就是这样吧，实现方式不同，因此就说锁的类型不同。

[ 本帖最后由 晶晶小妹 于 2008-6-21 12:32 编辑 ]

chandler_1983's question can also be looked at this way: What do the latches "library cache pin" and "library
cache lock" do and what's the difference between "latch free" wait event where the latches are these two
types and the "library cache pin" or "library cache lock" waits themselves? Do the numbers
v$latch.gets for library cache lock/pin or the numbers v$system_event.total_waits for them tell us
the actual number of library cache locks/pins we've had in the database since instance startup?

If you check v$latch for these two latches and compare with the same name wait events in
v$system_event, you'll see the two latches are get'ed much more frequently. I think the numbers
for the two latches in v$latch accurately reflect the actual pins/locks we've had. There's no
statistic in v$sysstat or v$sesstat for latch gets, probably because v$latch.gets serves the
purpose already. If the database is not too busy causing waits on them, the numbers in
v$system_event (or other wait related views) will not increment. In fact, v$latch.sleeps should be
equal to these latches' "latch free" waits because latch waits are the same as latch sleeps. You
can test on a quiet single-user database, with no jobs or scheduler jobs, and very quickly type
select name, gets, misses, sleeps, immediate_gets from v$latch where name in ('library cache pin',
'library cache lock'). You'll see the pin gets keep going up (by 2 in my 10.2.0.1 database) while
lock gets stay the same. The 2 pins must be due to this SQL itself. Values in v$system_event stay
the same.

Yong Huang

[ 本帖最后由 Yong Huang 于 2008-6-23 06:07 编辑 ]

恩，学习一下

library cache pin latch和library cache lock latch好像在文档上看过，因为Library cache Lock/Pin都不是原子操作，因此为了不使在加或释放Library cache Lock/Pin时，有其他操作对它们进行打扰，因为使用Library cache Lock/Pin latch对加、释放Lock/Pin的过程加以保护。

获得一个library cache lock，表示获得到一个 handle，可以是  null、share，exclusive 三种模式。 根据这个handle可以获得到object。

pin 则是表示拥有handle情况下，要真正地使用这些object，必须把这些object 钉在内存中，这也分了 null、share、exclusive三种模式。

所以tom曾经简单描述过：  lock， get a handle，是一个寻找的结果； pin，excute ，使用寻找到的结果


结合sql的执行，编译，ddl的发生等情况可以理解三种模式的可能意义。

至于yong huang的例子中 pin增加  lock不增加，可以看作是 执行次数增加，但是由于session已经获得handle 不需要重新去寻找获得（session已经 cached这个 cursor ，表示已经获得handle）


这时我的理解，有什么不对欢迎讨论。

I modified my text and specially added these words:

"the numbers [i.e. gets]
for the two latches in v$latch accurately reflect the actual pins/locks we've had. There's no
statistic in v$sysstat or v$sesstat for latch gets, probably because v$latch.gets serves the
purpose already. If the database is not too busy causing waits on them, the numbers in
v$system_event (or other wait related views) will not increment. In fact, v$latch.sleeps should be
equal to these latches' "latch free" waits because latch waits are the same as latch sleeps."

Yong Huang

QUOTE:

---

原帖由 biti_rainy 于 2008-6-23 05:21 发表
至于yong huang的例子中 pin增加  lock不增加，可以看作是 执行次数增加，但是由于session已经获得handle 不需要重新去寻找获得（session已经 cached这个 cursor ，表示已经获得handle）

---

Exactly! Can we guess what the 2 pins are for each execution? I type
select name, gets, misses, sleeps, immediate_gets from v$latch where name in ('library cache pin',
'library cache lock')
in quick succession and v$latch.gets for library cache pin goes up by 2. Why 2? One for x$kslld and one for x$ksllt, the two base table under v$latch?

Yong Huang