library cache pin和library cache lock(一)

一、概述
一個例項中的library cache包括了不同型別物件的描述,如:遊標,索引,表,檢視,過程,等等.
這些物件不能在他們被使用的時候改變,他們在被使用的時候會被一種library locks and pins的機制鎖住.
一個會話中,需要使用一個物件,會在該物件上先得到一個library lock(null, shared or exclusive模式的)
這是為了,防止其他會話也訪問這個物件(例如:重編譯一個包或檢視的時候,會加上exclusive型別的鎖)或更改物件的定義.

總的來說，library cache pin和library cache lock都是用於share pool的併發控制的。pin和lock都可以看作是一種鎖。
locks/pins會在SQL語句執行期間一直保持,在結束的時候才釋放。

每個想使用或修改已經locked/pin的物件的SQL語句,將會等待事件'library cache pin'或'library cache lock'直到超時.
超時,通常發生在5分鐘後,然後SQL語句會出現ORA-4021的錯誤.如果發現死鎖,則會出現ORA-4020錯誤。

二、library cache pin和library cache lock成因
lock主要有三種模式: Null,share(2),Exclusive(3).
在讀取訪問物件時,通常需要獲取Null(空)模式以及share(共享)模式的鎖定.
在修改物件時,需要獲得Exclusive(排他)鎖定.

同樣pin有三種模式,Null,shared(2)和exclusive(3).
只讀模式時獲得共享pin,修改模式獲得排他pin.

模式為shared(2)的pin會阻塞任何exclusive(3)的pin請求。
模式為shared(3)的pin也會阻塞任何exclusive(2)的pin請求。

不同的操作會對物件請求不同的lock/pin
1、所有的DDL都會對被處理的物件請求排他型別的lock和pin
2、

當要對一個過程或者函式進行編譯時，需要在library cache中pin該物件。在pin該物件以前，需要獲得該物件handle的鎖定，如果獲取失敗，就會產生library cache lock等待。如果成功獲取handle的lock，則繼續在library cache中pin該物件，如果pin物件失敗，則會產生library cache pin等待。
如果是儲存過程或者函式，可以這樣認為：如果存在library cache lock等待，則一定存在library cache pin等待；反過來，如果存在library cache pin等待，不一定會存在library cache lock等待；
但如果是表引起的，則一般只有library cache lock等待，則不一定存在library cache pin。

可能發生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。

P2 = Pin address
自身的pin地址。一般用P2RAW(十六進位制)代替P2(十進位制)

P3 = Encoded Mode & Namespace