关于library cache的一点点总结

库高速缓存的基本结构是hash table-bucket-chain-handle-object。

oracle对库缓存中的对象生成hash函数，对相同hash值的对象分配bucket，并且通过chain来管理

[@more@]

库高速缓存的基本结构是hash table-bucket-chain-handle-object。

oracle对库缓存中的对象生成hash函数，对相同hash值的对象分配bucket，并且通过chain来 管理。也存在一个library cache handle(句柄)来管理library chache object(LCO)。handle里面包含

library cache object的元数据和指针等信息。

library cache object实际包含一下及部分信息：

1.dependency table:当前LCO依赖的其它LCO的信息。sql语句所参照的table，view等具有依赖性的。

2.child table：单曲LCO的子LCO信息，对于sql文本，对于具有相同文本名称的sql创建父LCO,将实际执行的

sql cursor存储到子LCO，比如两个个sql文本，但是参照对象不同。

3.data blocks：LCO包含的实际信息所存储的chunk区域的指针信息。sql cursor，sql语句，执行计划，执行文本信息

会被保存到特定chunk，这些chunk的地址被LCO的data blocks区域管理。

转储library cache探究一下：

SQL> select*from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

Elapsed: 00:00:00.09

SQL> alter session set events'immediate trace name library_cache level 10';

Session altered.

摘取部分内容：

214706 BUCKET#84769-----bucket编号

mtx=0x3131d828(0, 102, 0):

214707 LIBRARY HANDLE:0x2eb4220c-----library cache handle

bid=84769 hid=316f4b21 lmd=0 pmd=0 sta=VALD

214708 name=SYSMAN.MGMT_TEST_PROP_QUALIFIER_ARR

214709 hash=7cb1175ccbcf3d7bed87ed7f316f4b21 idn=64585

214710 tim=08-03-2007 01:59:09 kkkk-dddd-llll=0055-0055-0055

214711 exc=0 ivc=0 ldc=1 cbb=1 rpr=1 kdp=0 slc=0 dbg=0

214712 dmtx=0x2eb42268(0, 5, 0) mtx=0x2eb4228c(850000, 113, 0)

214713 nsp=TABL(01) typ=TYPE(13) flg=KGHP/TIM/KEP/SML/[02800800]

214714 lwt=0x2eb42258[0x2eb42258,0x2eb42258]

214715 pwt=0x2eb42248[0x2eb42248,0x2eb42248]

214716 ref=0x2eb42260[0x2f85d7bc,0x2f8a3558]

214717 HANDLE REFERENCES:

214718 reference handle flags

214719 --------- --------- -------------------

214720 2f85d7bc 2ea6ee80 DEP[01]

214721 2f85e090 2eb43148 DEP[01]

214722 2f85fbc0 2ea75074 DEP[01]

214723 2f86096c 2ea76444 DEP[01]

214724 2f8a3558 2eb44448 DEP[01]

214725 LIBRARY OBJECT: 0x2e32a114

214726 flg=EXS/LOC[0005] pfl=[0000] ssta=VALD load=0

214727 DEPENDENCIES: count=2 size=16---------dependency信息

214728 dependency# table reference handle position flags

214729 ----------- -------- --------- -------- -------- -------------------

214730 0 2e32a4d0 2e32a288 32deb120 0 DEP[01]

214731 1 2e32a4d0 2e32a2bc 2ea74a68 0 DEP[01]

254058 name=select*From emp-----LCO名称

254059 hash=7d4ae2c017a220f97be9f333a25f8ae8 idn=0

254060 tim=08-01-2011 12:12:49 kkkk-dddd-llll=0000-0001-0001

254061 exc=2 ivc=0 ldc=2 cbb=1 rpr=1 kdp=1 slc=0 dbg=0

254062 dmtx=0x2bbbfc48(0, 0, 0) mtx=0x2bbbfc6c(850000, 45, 0)

254063 nsp=CRSR(00) typ=CRSR(00) flg=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120108d0]

254064 lwt=0x2bbbfc38[0x2bbbfc38,0x2bbbfc38]

254065 pwt=0x2bbbfc28[0x2bbbfc28,0x2bbbfc28]

254066 ref=0x2bbbfc40[0x2bbbfc40,0x2bbbfc40]

254067 LIBRARY OBJECT: 0x2c248860

254068 flg=EXS[0001] pfl=[0000] ssta=VALD load=0

254069 CHILDREN: size=16

254070 child# table reference handle

254071 ------ -------- --------- --------

254072 0 2c248c0c 2c2265bc 2bb7ecbc

254073 LIBRARY HANDLE:0x2bb7ecbc bid=0 hid=0 lmd=0 pmd=0 sta=VALD

254074 exc=2 ivc=0 ldc=1 cbb=1 rpr=1 kdp=0 slc=0 dbg=0

254075 dmtx=0x2bb7ed18(0, 0, 0) mtx=0x2bbbfc6c(850000, 45, 0)

254076 nsp=CRSR(00) typ=CRSR(00) flg=RON/KGHP/PN0/SML/EXP/[12010100]

254077 lwt=0x2bb7ed08[0x2bb7ed08,0x2bb7ed08]

254078 pwt=0x2bb7ecf8[0x2bb7ecf8,0x2bb7ecf8]

254079 ref=0x2bb7ed10[0x2c2265bc,0x2c2265bc]

254080 HANDLE REFERENCES:

254081 reference handle flags

254082 --------- --------- -------------------

254083 2c2265bc 2bbbfbec CHL[02]

254084 LIBRARY OBJECT: 0x2c21c8e0

254085 flg=EXS[0001] pfl=[0000] ssta=VALD load=0

254086 DEPENDENCIES: count=1 size=16

254087 dependency# table reference handle position flags

254088 ----------- -------- --------- -------- -------- -------------------

254089 0 2c234000 2c21cc64 2bbbe024 12 DEP[01]

254090 AUTHORIZATIONS: count=1 size=16 minimum entrysize=16

254091 00000000 51000000 00020000 00000000

254092 ACCESSES: count=1 size=16

254093 dependency# types

254094 ----------- -----

254095 0 0009

254096 TRANSLATIONS: count=1 size=16

254097 original final

254098 -------- --------

254099 2bbbe024 2bbbe024

254100 DATA BLOCKS:

254101 data# heap pointer status pins change whr

254102 ----- -------- -------- --------- ---- ------ ---

254103 0 32e39fa8 2c21c974 2c21c8c8 I/-/A/-/- 0 NONE 00

254104 6 2c226464 2b6ac928 2b6abb20 I/-/A/-/E 0 NONE 00

（heap指针：32e39fa8,chunk指针：2c21c974)

很多进程检索库高速缓冲区时，就必须获得保护相应hash bucket的library cache latch。

但也存在library cache lock和library cache pin这样的equeue lock。比如利用alter table

修改table是，对于LCO，必须要以exclusive模式获得，获得library cache lock只是对handle加锁，如果

要修改对象就要获得pin。详细的信息如下文档：

作用：

Both library cache lock and library cache pin are provided to access objects in the library cache. Library cache lock manages concurrency between processes, whereas library cache pin manages cache coherence. In order to access an object in library cache, a process must first lock the library cache object handle, and then pin the object data heap itself. Requests for both library cache lock and library cache pin will wait until granted. This is a possible source of contention, because there is no NOWAIT request mode.

By acquiring a library cache lock on the library cache object handle, a process can prevent other processes from accessing the object, or even finding out what type it is. It can even maintain a dependency on an object without preventing other processes from accessing the object. Acquiring a library cache lock is also the only way to locate an object in cache--a process locates and locks an object in a single operation.

If the process wants to actually examine or modify the object, then it must acquire a library cache pin on the object data heap itself (after acquiring a library cache lock on the library cache object handle). Pinning the object causes information about the object and its data heaps to be loaded into memory if they were not already there. This information is guaranteed to remain in memory at least until the pin is released. Locks and pins are externalized in X$KGLLK and X$KGLPN, respectively.

模式分类：

A process acquires a share library cache lock if it intends only to read the object. For example, it wants to reference the object during compilation. A process acquires an exclusive library cache lock if it intends to create or modify the object. For example, it wants to drop the object from the database. Null library cache locks are a special case. They are acquired on objects that are to be executed like child cursor, procedure, function, package, or type body. You can use them to maintain an interest on an object for a long period of time (session persistency), and to detect if the object becomes invalid. You can break null library cache lock at any time. This is used as a mechanism to notify a session that an executable object is no longer valid. If a null library cache lock is broken, and thus the object is invalidated, then it is an indication to the user who was holding the null library cache lock that the object needs to be recompiled. A null library cache lock is acquired during the parse phase of SQL statement execution and is held as long as the shared SQL area for that statement remains in the shared pool. A null library cache lock does not prevent any DDL operation, and can be broken to allow conflicting DDL operations, hence the term "breakable parse lock." A null library cache lock on an object is broken when there is an exclusive library cache pin on the object.

-------------------------------------------------------------------------------

When a process pins an object data heap that is not in memory, the process can determine whether the data heap is to be loaded in the PGA or SGA. An object must be pinned in Exclusive mode if it is to be modified. However, the process first will always pin the object in Share mode, examine it for errors and security checks, and then, if necessary, (such as needing modification) pin it in Exclusive mode. An object is never pinned in Exclusive mode if only read access is required. This is because all dependent transient objects (cursors) are invalidated (null locks broken) when an object is unpinned from Exclusive mode. The effect would be unnecessary recompilation and reparsing of all dependent packages, procedures, and functions.

---------------------------------------------------------------------------------

11g以前有很多相关的latch，但是11g后都没有了：

SQL> select*From v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

SQL> select name,gets,misses,immediate_gets,immediate_misses,wait_time from v$latch where name like'%library%';

NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES WAIT_TIME

------------------------------ ---------- ---------- -------------- ---------------- ----------

library cache load lock 13501 3 35 0 0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25586587/viewspace-1053335/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25586587/viewspace-1053335/