當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Oracle Library Cache 的 lock 与 pin 说明

發布時間：2024/4/15 编程问答 33 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle Library Cache 的 lock 与 pin 说明小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

一. 相關的基本概念

之前整理了一篇blog，講了Library Cache 的機制，參考：

??????????? Oracle Library cache 內部機制說明

??????????? http://blog.csdn.net/tianlesoftware/article/details/6629869

??????????? 在這個機制中，沒有詳細講library 上的lock 和pin。這2個概念對DB 的理解非常重要。所以單獨拿出來，進行說明。

根據hellodba 和 dbsnake 的相關blog，進行了整理，原文鏈接如下：

??????????? http://www.hellodb.net/2010/07/oracle-library-cache.html

??????????? http://dbsnake.com/2011/05/lib-cache-lck-and-pin.html

??????????? http://dbsnake.com/2011/06/lib-cache-lck-and-pin-1.html

??????????? 在library cache 機制的文章里提到，Library cache Handle 里保存了lock 和 pin 的信息。而且在Library cache handle 和child cursor 上都有lock 和pin。它們稱為library cache lock和library cache pin。

??????????? 常說的library cache lock和library cache pin是enqueue，不是latch，它們是兩種DDL lock。意的是，在11gR1之前，Oracle中又存在名為library cache lock和library cache pin的latch。

??????????? 對于這個library cache lock 是不是enqueue，在dbsnake的blog上有討論。我這里沿用dbsnake的觀點：該lock 是enqueue 來整理這篇blog。

??????????? 在DSI 405 的第二篇里有介紹Enqueue Structures。

1. Library cache中的并發控制：

??????????? Oracle利用Library cache lock和Library cache pin來實現并發控制，Library cache lock是在handle上獲取的，而Library cache pin則是在data heap上獲取。訪問對象時，首先必須獲取handle上的lock，然后將訪問的數據pin在內存中。lock的作用是控制進程間的并發訪問，而pin的作用是保證數據一致性，防止數據在訪問時被交換出去。

??????????? lock和pin的實現類似于enqueue，在每個handle上都有lock和pin的holder list和waiter list，用來保存持有該資源和等待該資源的隊列。

2. 阻塞分析：

??????????? 現實情況中，我們有一個數據庫中存在被應用大量頻繁訪問的procedure，當依賴的表發生變更時，導致該procedure失效，這時會出現大量的library cache lock和library cache pin的等待，堵塞應用訪問，造成了重大故障。出現這個問題的原因是：當procedure失效后，所有訪問該對象的進程都嘗試去編譯，大量進程嘗試獲取exclusive類型的lock和pin，出現了大量的等待。后續的Oracle版本作出了改進，當出現這種情況時，只允許第一個進程嘗試去編譯該對象，編譯通過后，所有的進程就可以并發訪問，避免了大量進程同時嘗試編譯的情況出現。

3. Library cache中的Latch：

??????????? Librarycache中相關的latch包括：shared pool latch，library cahce latch，library cache lock latch，library cache pin latch。

??????????? Sharepool latch的主要作用是分配或釋放空間時使用，從Oracle9i開始，shared pool被分成了很多個subpool，由多個shared pool latch保護，Oracle開始支持更大的sharedpool。

??????????? Librarycache latch的主要作用是在hashbucket中定位handle時使用，library cache lock latch和library cache pin latch分別是獲取lock和pin時，需要取得的latch。

??????????? sharedpool大小不合理，大量的硬解析以及SQL版本過多都可能導致shared pool latch和library cache latch的爭用。

??????????? 從Oracle10g開始，Oracle正在逐步用mutex取代library cache中的latch，cursor：pin S和cursor：pin X相當于share和exclusive類型的library cache pin，cursor：pin S wait on X則表示share方式正在等待exclusive鎖定。

4. enqueue，library cache lock和librarycache pin的作用

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

??????????? Byacquiring a library cache lock on the library cache object handle, a processcan prevent other processes from accessing the object, or even finding out whattype it is. It can even maintain a dependency on an object without preventingother processes from accessing the object. Acquiring alibrary cache lock is also the only way to locate an object in cache--a processlocates and locks an object in a single operation.

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

5. library cache lock和library cache pin的幾種lockmode

5.1 ?Library cache lock有三種lockmode，分別是share、exclusive和null

??????????? Aprocess acquires a share library cache lock if it intends only to read theobject. ? For example, it wants toreference the object during compilation. A process acquires an exclusivelibrary cache lock if it intends to create or modify the object.

??????????? Forexample, it wants to drop the object from the database. Null library cachelocks are a special case. They are acquired on objects that are to be executedlike child cursor, procedure, function, package, or type body. You can use themto maintain an interest on an object for a long period of time (sessionpersistency), and to detect if the object becomes invalid. You can break null library cache lock at any time. Thisis used as a mechanism to notify a session that an executable object is nolonger valid. If a null library cache lock is broken,and thus the object is invalidated, then it is an indication to the user whowas 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 SQLstatement execution and is held as long as the shared SQL area for thatstatement remains in the shared pool. A null librarycache lock does not prevent any DDL operation, and can be broken to allowconflicting DDL operations, hence the term "breakable parse lock."A null library cache lock on an object is broken when there is an exclusivelibrary cache pin on the object.

5.2 Library cache pin有兩種lock mode，分別是share和exclusive。

??????????? Whena process pins an object data heap that is not in memory, the process candetermine 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 bemodified. However, the process first will alwayspin the object in Share mode, examine it for errors and security checks, andthen, 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. Thisis because all dependent transient objects (cursors) are invalidated (nulllocks broken) when an object is unpinned from Exclusive mode. The effect wouldbe unnecessary recompilation and reparsing of all dependent packages,procedures, and functions.

5.3下面詳細解釋在修改和訪問對象時，lock和pin的作用：

5.3.1修改對象：

??????????? 編譯SQL或PLSQL對象，獲取該對象（cursor，procedure）handle上exclusive類型的lock，并且持有data heap上exclusive類型的pin，防止其他人讀取和修改。同時，在該對象所依賴的對象（table）上，必須持有一個share類型的lock和pin，防止在修改的過程中，被其他進程所修改。

5.3.2訪問對象：

??????????? 訪問SQL或PLSQL對象，獲取該對象（cursor，procedure）handle上NULL類型的lock，并且持有data heap上share類型的pin，同時，在其依賴的對象（table）上持有share類型的lock和pin。如果一個procedure依賴另外一個function，那么在被依賴的function上，也需要持有share類型的lock和pin。

??????????? NULL類型的lock比較特殊，它只存在于cursor和procedure等只讀對象上，它并不起到任何并發控制的作用，它更象是一個trigger，當對象失效時，通知所有訪問這個cursor的進程。比如：select* from emp這個SQL，依賴emp表，當emp表發生變化時，cursor上的NULL lock被打破，所有有訪問這個cursor的進程都會知道該對象已經失效。

??????????? 當持有對象的library cache pin時，會在row cache中對相應的對象加鎖，就是row cache lock，阻止可能導致數據字典信息混亂的DDL發生。

6. ?latch，librarycache lock和library cache pin作用

??????????? 這是一個很糾結的問題，既然已經有了作為enqueue的library cache lock和library cache pin，為什么在11gR1以前，Oracle里還有同名latch，而且明顯這些同名latch是在被使用：

Connected to Oracle Database 10g EnterpriseEdition Release 10.2.0.5.0

Connected as ipra

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%';

NAMELEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

library cache 5 9221760 1608 800 2596 76766

library cache lock 6 13548247 5826 0 0

librarycache lock allocation 3 208273 0 0 0 0

library cache pin 6 4207462 1930 2 0

library cache hash chains 9 0 0 0 0 0

librarycache pin allocation 3 57276 0 0 0 0

librarycache load lock 5 24848 0 0 1 0

7 rowsselected

??????????? 從結果里我們可以看到，對于10.2.0.5而言，Oracle存在7種跟library cache相關的latch，除了library cache hash chains latch之外，其他的跟library cache相關的latch，Oracle都有使用。

??????????? 那么library cache lock latch、library cache pin latch以及大家最耳熟能詳的library cache latch等等，這些latch是做什么用的呢？也許我們可以從下面的一段文字中找到答案：

??????????? The library cache latches serialize access to the objects inthe library cache. Access to library cache objects always occurs throughlibrary cache locks. Because locking an object is not an atomicinstruction, a library cache latch is acquired before the library cache lockrequest and is released after it. For most operations, the library cachelatches are used, and therefore they can become a point of contention.

??????????? If an object is not in memory, then a library cachelock cannot be acquired on it. In order to prevent multiple processes torequest the load of the same object simultaneously, another latch must beacquired before the load request. This is the librarycache load lock latch. The library cache load lock latch is taken andheld until a library cache load lock is allocated, then the latch is released. Loadingof the object is performed under the library cache load lock and not under thelibrary cache load lock latch as it may take quite a long time.

6.1 幾點關注的地方：

??????????? （1）Oracle使用上述library cache latches（包括library cache latch、library cache lock latch、library cache pin latch、library cache pin allocation latch、library cache load lock latch）的目的是控制并發訪問library cache object所需要的相關的enqueue或者是為了控制并發訪問library cache中的相關的內存結構，比如用相關的library cache lock latch控制并發獲得library cache lock。這里我猜測Oracle用librarycache lock latch控制并發獲得library cache lock，用library cache pin latch控制并發獲得librarycache pin，用library cache load lock latch控制并發獲得library cache loadlock，用library cache latch去控制并發訪問library cacheobject handle中的某些結構，如library cache object handle中的flag中的specialstatus flag （special status flags are protected by the library cache latch.Examples of these flags indicate that: The object is valid; The object isauthorized; The object has compilation errors）。

??????????? （2）Librarycache load lock是另外一種enqueue。The session tries to find the library cacheload lock for the database object so that it can load the object. The library cache load lock is always obtained in Exclusivemode, so that no other process can load the same object. If the librarycache load lock is busy the session will wait on this event until the lockbecomes available.

??????????? 好了，現在我們來驗證一下，還是上述10.2.0.5的環境，我將上述sql（selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%'）馬上再執行一遍，這是軟解析，必然要獲得library cache lock，不需要獲得library cache load lock，所以對應的latch應該表現為librarycache lock latch的gets增加，library cache load lock latch的gets不變：

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%';

NAMELEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

librarycache 5 9222166 1608 800 2596 76766

library cache lock 6 13548760 5826 0 0

librarycache lock allocation 3 208287 0 0 0 0

library cache pin 6 4207656193 0 2 0

librarycache hash chains 9 0 0 0 0 0

librarycache pin allocation 3 57278 0 0 0 0

library cache load lock 5 248480 0 1 0

7 rowsselected

??????????? 從結果里我們可以看到，library cache lock latch的gets從13548247遞增到了13548760，library cache pin latch的gets從4207462遞增到了4207656，但library cache load lock latch的gets還是保持24848不變。

??????????? 現在我們來讓library cache load lock latch的gets發生變化，這是非常容易的事情，我們只需要執行一個需要硬解析的sql就可以了：

SQL> select * from scott.emp_temp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO ISINSPECT

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

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%';

NAMELEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

librarycache 5 9223549 1608 800 2596 76766

librarycache lock 6 13550296 582 6 0 0

librarycache lock allocation 3 208348 0 0 0 0

librarycache pin 6 4208118 193 0 2 0

librarycache hash chains 9 0 0 0 0 0

librarycache pin allocation 3 57294 0 0 0 0

library cache load lock 5 248560 0 1 0

7 rowsselected

??????????? 由于我們執行了一個需要硬解析的sql，導致Oracle需要獲得library cache load lock以便load相關信息到這個sql的子cursor的heap 6中，而要獲得library cache load lock，必須先持有library cache load lock latch。從上述結果中我們可以看到，此時library cache loadlock latch的gets已經發生了變化，從24848遞增到了24856。

接下來我們再來看一看上述librarycache latches的子latch情況：

SQL> show parameter cpu_count

NAME TYPE VALUE

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

cpu_count integer 2

這里cpu的個數為2，顯然上述library cache latches的子latch應該為3：

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_missesfrom v$latch_children where name like 'library%';

NAME LEVEL# GETS MISSES SLEEPSIMMEDIATE_GETS IMMEDIATE_MISSES

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

library cache 5 3274551 1301 94 187 0

library cache 5 2218356 116 80 933 0

library cache 5 3731320 191 626 1476 76766

library cache lock 6 5339737 362 3 0 0

library cache lock 6 6223353 194 3 0 0

library cache lock 6 1987799 26 0 0 0

library cache pin 6 1484918 184 0 0 0

library cache pin 6 891695 3 0 2 0

library cache pin 6 1831837 6 0 0 0

library cache pin allocation 3 23177 0 0 00

library cache pin allocation 3 8272 0 0 0 0

library cache pin allocation 3 25849 0 0 00

library cache lock allocation 3 75900 0 0 00

library cache lock allocation 3 28229 0 0 00

library cache lock allocation 3 104237 0 00 0

library cache hash chains 9 0 0 0 0 0

18 rows selected

??????????? 注意，結果里并沒有library cache load lock latch，說明library cache load locklatch沒有children，它是一個solitary類型的latch。

6.2 Mutex 說明

??????????? 從10.2.0.2開始，Oracle將_kks_use_mutex_pin的默認值改成了true，這意味著從10.2.0.2開始，Oracle里將再不會有針對cursor的librarycache pin等待，取而代之的是mutex等待，具體表現為cursor: pin *等待，如cursor: pin S wait on X。

這里需要我們了解的是：

??????????? （1）從10.2.0.2開始，Oracle只是用mutex替代了針對cursor的librarycache pin，這并不代表從10.2.0.2開始Oracle里就沒有librarycache pin等待了。比如這個例子里的library cache pin等待就發生在10.2.0.4中：http://dbsnake.com/2010/06/solve-library-cache-pin.html

??????????? （2）Mutex和latch是互相獨立，沒有任何關系的：Latches and mutexes are independent mechanisms i.e. a process canhold a latch and a mutex at the same time. In the case of process death,latches are always cleaned up before mutexes. There is no generic mutexdeadlock detection (unlike latches). There is no mutex/latch hierarchy.

??????????? 從11gR1開始，Oracle用mutex替換了librarycache latches，并引了一個新的等待事件：librarycache: mutex *，我們來看一下這個知識點：

Connected to Oracle Database 11g EnterpriseEdition Release 11.2.0.1.0

Connected as nbs

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%';

NAME LEVEL# GETS MISSES SLEEPSIMMEDIATE_GETS IMMEDIATE_MISSES

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

library cache load lock 5 0 0 0 0 0

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses fromv$latch_children where name like 'library%';

NAME LEVEL# GETS MISSES SLEEPSIMMEDIATE_GETS IMMEDIATE_MISSES

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

??????????? 從結果里我們可以看到，在11.2.0.1里，各種library cache latches都沒有了，只剩下了library cache load locklatch，而且Oracle還沒有使用這個latch，因為gets是0。

二. 深入研究

在這部分主要說明如下結論：

??????????? 1、針對cursor的library cachelock的lock mode確實是null，無論該cursor所對應的sql是硬解析還是軟解析；

??????????? 2、 MOS上說Oracle說從10.2.0.2以后，會用mutex取代針對cursor的library cache pin，但我的測試結果是在10.2.0.5中，雖然在sql的軟解析時確實已經不存在library cache pin了，但如果是硬解析，則依然存在library cache pin；

??????????? 3、 sql的軟解析時，library cache pin的lock mode始終是S；

??????????? 4、 sql的硬解析時，library cache pin的lock mode一般是X，但在10.2.0.1中，即使是硬解析，也存在lock mode為S的library cache pin。

??????????? 這里測試所采用的方法就是event 10049，這個事件在10gR2以后，專門被用來trace librarycache lock和library cache pin。但好多朋友不太會用這個事件，我這里以一個實例的方式介紹了如何用10049事件來trace單個sql的library cache lock和library cache pin。

2.1 ?Oracle 10.2.0.1 下的測試

Connected to Oracle Database 10g EnterpriseEdition Release 10.2.0.1.0

Connected as SYS

10.2.0.1中_kks_use_mutex_pin的值為false，表示Oracle不會用mutex取代針對cursor的library cache pin：

SQL> select name,value,description from sys.all_parameterswhere name like '_kks%';

NAME VALUE DESCRIPTION

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

_kks_use_mutex_pin FALSE Turning on thiswill make KKS use mutex for cursor pins.

注意這里的all_parameters是自己創建的視圖。主要是為了方便查看隱含參數。

/* Formatted on2011/7/28 15:01:16 (QP5 v5.163.1008.3004) */

CREATE VIEW all_parameters

???? SELECT i.ksppinm name,

??????????? i.ksppdesc description,

??????????? CV.ksppstvl VALUE,

??????????? CV.ksppstdf isdefault,

??????????? DECODE (BITAND (CV.ksppstvf, 7),

??????????????????? 1, 'MODIFIED',

??????????????????? 4, 'SYSTEM_MOD',

??????????????????? 'FALSE')

?????????????? ismodified,

??????????? DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted

?????? FROM sys.x$ksppi i, sys.x$ksppcv CV

????? WHERE???? i.inst_id = USERENV ('Instance')

??????????? AND CV.inst_id = USERENV ('Instance')

??????????? AND i.indx = CV.indx

??????????? AND i.ksppinm LIKE '/_%' ESCAPE '/'

?? ORDER BY REPLACE (i.ksppinm, '_', '');

Oracle 參數分類和參數的查看方法

http://blog.csdn.net/tianlesoftware/article/details/5583655

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.00 30

......省略顯示部分內容

7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> select hash_value,sql_text fromv$sqlarea where sql_text like 'select * from scott.emp%';

HASH_VALUE SQL_TEXT

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

52404428 select * from scott.emp

SQL> select to_char(52404428,'XXXXXXXX')from dual;

TO_CHAR(52404428,'XXXXXXXX')

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

31FA0CC

??????????? 現在我們要來trace針對上述sql的library cache pin和library cache lock，方法我之前已經說了，就是用event 10049，用10049的難點在于如何確定level。

確定10049針對單個sql的level值的算法如下：

首先，10049的level可能會有如下一些值：

#define KGLTRCLCK 0x0010/* trace lock operations */

#define KGLTRCPIN 0x0020/* trace pin operations */

#define KGLTRCOBF 0x0040 /* trace objectfreeing */

#define KGLTRCINV 0x0080 /* traceinvalidations */

#define KGLDMPSTK 0x0100 /* DUMP CALL STACKWITH TRACE */

#define KGLDMPOBJ 0x0200 /* DUMP KGL OBJECTWITH TRACE */

#define KGLDMPENQ 0x0400 /* DUMP KGL ENQUEUE WITH TRACE */

#define KGLTRCHSH 0x2000/* DUMP BY HASH VALUE */

??????????? 其次，我們是要針對單個sql，所以需要用到這個sql的hash value，以便將10049和這個sql聯系起來，即我們一定要用到KGLTRCHSH，也就是0x2000；

另外我們是要tracelibrary cache lock和library cache pin，所以我們一定要用到KGLTRCLCK和KGLTRCPIN，即0x0010和0x0020；

??????????? 最后就是我們需要把這個sql的hash value的16進制的后兩個byte拿出來，作為10049的level的前綴。

??????????? 從上面結果中我們可以看到，select * from scott.emp的hash value的16進制的后兩個byte是0xA0CC。

??????????? 另外KGLTRCHSH | KGLTRCLCK | KGLTRCPIN ＝ 0x2000 | 0x0010 | 0x0020 = 0x2030。按照上述算法，select * from scott.emp的10049的最終level值就是0xa0cc2030，也就是2697732144：

SQL> selectto_number('a0cc2030','XXXXXXXXXXXX') from dual;

TO_NUMBER('A0CC2030','XXXXXXXX

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

2697732144

??????????? 現在我們設置好10049后再執行一遍上述sql，以觀察10.2.0.1下sql的軟解析時library cache pin和library cache lock：

SQL> oradebug setmypid

已處理的語句

SQL> oradebug event10049 trace name context forever,level 2697732144

已處理的語句

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.00 30

......省略顯示部分內容

7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5808.trc

相應的trace文件（d:\oracle\admin\cuihua\udump\cuihua_ora_5808.trc）的內容為：

*** 2011-06-01 11:59:35.500

KGLTRCLCK kglget hd = 0x33938118 KGL Lock addr = 0x3174A99C mode = N

KGLTRCLCK kglget hd = 0x33938034 KGL Lock addr = 0x31716F50 mode = N

KGLTRCPIN kglpin hd = 0x33938034 KGL Pinaddr = 0x31718A28 mode = S

KGLTRCPIN kglpndl hd = 0x33938034 KGL Pinaddr = 0x31718A28 mode = S

KGLTRCLCK kgllkdl hd = 0x33938034 KGL Lockaddr = 0x31716F50 mode = N

KGLTRCLCK kgllkdl hd = 0x33938118 KGL Lockaddr = 0x3174A99C mode = N

hd = 0x33938118所對應的library cache object的name就是select * from scott.emp：

SQL> select sql_text from v$sqlareawhere address='33938118';

SQL_TEXT

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

select * from scott.emp

hd = 0x33938034就是hd = 0x33938118的child cursor：

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob where kglhdadr='33938034';

KGLHDADR KGLHDPAR KGLNAOBJ

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

33938034 33938118 select * from scott.emp

從上述trace文件中我們可以得出如下結論：

??????????? 1、10.2.0.1中，sql軟解析時，針對cursor的library cachelock的lock mode確實是null；

??????????? 2、10.2.0.1中，sql軟解析時，針對cursor的library cachepin的lock mode確實是S；

現在我們來觀察10.2.0.1下sql的硬解析時librarycache pin和library cache lock：

SQL> shutdown immediate

數據庫已經關閉。

已經卸載數據庫。

ORACLE 例程已經關閉。

SQL> startup

ORACLE 例程已經啟動。

Total System Global Area 608174080 bytes

Fixed Size 1250404 bytes

Variable Size 318770076 bytes

Database Buffers 281018368 bytes

Redo Buffers 7135232 bytes

數據庫裝載完畢。

數據庫已經打開。

SQL> select hash_value,sql_text fromv$sqlarea where sql_text like 'select * from scott.emp%';

HASH_VALUE SQL_TEXT

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

SQL> oradebug setmypid

已處理的語句

SQL> oradebug event 10049 trace namecontext forever,level 2697732144

已處理的語句

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.00 30

......省略顯示部分內容

7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5016.trc

相應的trace文件（d:\oracle\admin\cuihua\udump\cuihua_ora_5016.trc）的內容為：

KGLTRCLCK kglget hd = 0x206ECF90 KGL Lock addr = 0x3174E068 mode = N

KGLTRCPIN kglpin hd = 0x206ECF90 KGL Pinaddr = 0x317187C0 mode = X

KGLTRCPIN kglpndl hd = 0x206ECF90 KGL Pinaddr = 0x317187C0 mode = X

KGLTRCLCK kglget hd = 0x33B19238 KGL Lock addr = 0x3174E618 mode = N

KGLTRCPIN kglpin hd = 0x33B19238 KGL Pinaddr = 0x31717F28 mode = X

KGLTRCPIN kglpndl hd =0x33B19238 KGL Pin addr = 0x31717F28 mode = S

KGLTRCLCK kgllkdl hd = 0x33B19238 KGL Lockaddr = 0x3174E618 mode = N

KGLTRCLCK kgllkdl hd = 0x206ECF90 KGL Lockaddr = 0x3174E068 mode = N

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob where kglhdadr='33B19238';

KGLHDADR KGLHDPAR KGLNAOBJ

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

33B19238 206ECF90 select * from scott.emp

從上述trace文件中我們可以得出如下結論：

??????????? 1、10.2.0.1中，sql硬解析時，針對cursor的library cachelock的lock mode依然是null；

??????????? 2、10.2.0.1中，sql硬解析時，針對cursor的library cachepin的lock mode一般是X，但也存在lock mode為S的library cachepin，且這個S是針對子cursor的。

2.2 Oracle 10.2.0.5 下的測試

Connected to Oracle Database 10g EnterpriseEdition Release 10.2.0.5.0

Connected as SYS

??????????? MOS上說：從10.2.0.2開始，Oracle將_kks_use_mutex_pin的默認值改成了true，表明Oracle將用mutex替代針對cursor的library cache pin。但實際情況并不完全是這樣，詳情見后面的測試：

SQL> select name,value,description fromsys.all_parameters where name like '_kks%';

NAME VALUE DESCRIPTION

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

_kks_use_mutex_pin TRUE Turning on thiswill make KKS use mutex for cursor pins.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30030

......省略顯示部分內容

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> select hash_value,sql_text fromv$sqlarea where sql_text like 'select * from scott.emp%';

HASH_VALUE SQL_TEXT

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

52404428 select * from scott.emp

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace namecontext forever,level 2697732144

Statement processed.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30030

......省略顯示部分內容

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> oradebug tracefile_name

/u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc

$ cat/u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc

/u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc

Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/10.2.0

System name: AIX

Node name: P550_03_LD

Release: 3

Version: 5

Machine: 0001DA17D600

Instance name: testdb

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 1237156, image:oracle@P550_03_LD (TNS V1-V3)

*** 2011-06-01 13:38:07.949

*** ACTION NAME:() 2011-06-01 13:38:07.944

*** MODULE NAME:(sqlplus@P550_03_LD (TNSV1-V3)) 2011-06-01 13:38:07.944

*** SERVICE NAME:(SYS$USERS) 2011-06-0113:38:07.944

*** SESSION ID:(146.3) 2011-06-0113:38:07.944

KGLTRCLCK kgllkal hd= 0x700000022595c38 KGL Lock addr = 0x70000001f724d78 mode = N

KGLTRCLCK kglget hd = 0x700000022595c38 KGLLock addr = 0x70000001f724d78 mode = N

KGLTRCLCK kgllkal hd= 0x7000000226ec4f8 KGL Lock addr = 0x70000001f74e128 mode = N

KGLTRCLCK kgllkdl hd = 0x7000000226ec4f8KGL Lock addr = 0x70000001f74e128 mode = N

KGLTRCLCK kgllkdl2 hd = 0x7000000226ec4f8KGL Lock addr = 0x70000001f74e128 mode = 0

KGLTRCLCK kgllkdl hd = 0x700000022595c38KGL Lock addr = 0x70000001f724d78 mode = N

KGLTRCLCK kgllkdl2 hd = 0x700000022595c38KGL Lock addr = 0x70000001f724d78 mode = 0

??????????? 這里mode=0應該是表示調用kgllkdl2所產生的library cache lock在調用完上述方法后已經釋放了。

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob where lower(kglhdadr)='07000000226ec4f8';

KGLHDADR KGLHDPAR KGLNAOBJ

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

07000000226EC4F80700000022595C38 select * from scott.emp

從上述trace文件中我們可以得出如下結論：

10.2.0.5中，sql軟解析時，針對cursor的librarycache pin確實已經不存在；

現在我們來觀察10.2.0.5下sql的硬解析時librarycache pin和library cache lock：

$ sqlplus '/ as sysdba';

SQL*Plus: Release 10.2.0.5.0 - Productionon Wed Jun 1 13:42:11 2011

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 2096032 bytes

Variable Size 96470112 bytes

Database Buffers 209715200 bytes

Redo Buffers 6291456 bytes

Database mounted.

Database opened.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace namecontext forever,level 2697732144

Statement processed.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30030

......省略顯示部分內容

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> oradebug tracefile_name

/u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc

$ cat/u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc

/u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc

Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

ORACLE_HOME =/u01/app/oracle/product/10.2.0

System name: AIX

Node name: P550_03_LD

Release: 3

Version: 5

Machine: 0001DA17D600

Instance name: testdb

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 1536246, image:oracle@P550_03_LD (TNS V1-V3)

*** ACTION NAME:() 2011-06-01 13:42:44.913

*** MODULE NAME:(sqlplus@P550_03_LD (TNSV1-V3)) 2011-06-01 13:42:44.913

*** SERVICE NAME:(SYS$USERS) 2011-06-0113:42:44.913

*** SESSION ID:(159.3) 2011-06-0113:42:44.913

DBRM(kskinitrm) cpu_count : old(0) ->new(2)

kwqmnich: current time:: 5: 42: 44

kwqmnich: instance no 0 check_only flag 1

kwqmnich: initialized job cache structure

*** 2011-06-01 13:44:13.657

KGLTRCLCK kgllkal hd= 0x7000000225ccfa8 KGL Lock addr = 0x70000001f725560 mode = N

KGLTRCLCK kglget hd = 0x7000000225ccfa8 KGLLock addr = 0x70000001f725560 mode = N

KGLTRCPIN kglpin hd = 0x7000000225ccfa8 KGL Pin addr = 0x70000001f726378 mode= X

KGLTRCPIN kglpndl hd = 0x7000000225ccfa8 KGL Pin addr = 0x70000001f726378mode = X

KGLTRCLCK kgllkal hd= 0x7000000225abf18 KGL Lock addr = 0x70000001f733120 mode = N

KGLTRCLCK kglget hd = 0x7000000225abf18 KGLLock addr = 0x70000001f733120 mode = N

KGLTRCPIN kglpin hd = 0x7000000225abf18 KGL Pin addr = 0x70000001f726840 mode= X

KGLTRCPIN kglpndl hd = 0x7000000225abf18 KGL Pin addr = 0x70000001f726840mode = X

KGLTRCLCK kgllkdl hd = 0x7000000225abf18KGL Lock addr = 0x70000001f733120 mode = N

KGLTRCLCK kgllkdl2 hd = 0x7000000225abf18KGL Lock addr = 0x70000001f733120 mode = 0

KGLTRCLCK kgllkdl hd = 0x7000000225ccfa8KGL Lock addr = 0x70000001f725560 mode = N

KGLTRCLCK kgllkdl2 hd = 0x7000000225ccfa8KGL Lock addr = 0x70000001f725560 mode = 0

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob where lower(kglhdadr)='07000000225abf18';

KGLHDADR KGLHDPAR KGLNAOBJ

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

07000000225ABF1807000000225CCFA8 select * from scott.emp

從上述trace文件中我們可以得出如下結論：

??????????? 1、10.2.0.5中，sql硬解析時，依然存在library cache pin；

??????????? 2、10.2.0.5中，sql硬解析時，針對cursor的library cachepin的lock mode始終是X；

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

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿);?? DBA2 群：62697977(滿)??DBA3 群：62697850(滿)??

DBA 超級群：63306533(滿);? DBA4 群： 83829929? DBA5群： 142216823???

DBA6 群：158654907? 聊天群：40132017?? 聊天2群：69087192

--加群需要在備注說明Oracle表空間和數據文件的關系，否則拒絕申請

轉載于:https://www.cnblogs.com/sqlite3/archive/2011/07/28/2568103.html

總結

以上是生活随笔為你收集整理的Oracle Library Cache 的 lock 与 pin 说明的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： C/C++笔试、面试题（上）
下一篇： android中使用startActiv