oracle實驗記錄 （oracle 分析shared pool(2)）

分析shared pool結構與控制

dictionary cache:
當一條SQL語句 比如select * from t1進入 shared pool library cache前，oracle服務程式會到dictionary cache（row cache）找與t1 table相關的 資料字典資訊（表名，列名，許可權等）如果沒找到從system tablespace資料字典裡讀入buffer cache（從disk讀入），然後將這些資料字典資訊按行的方式 放入shared pool dictionary cache，然後再從dictionary cache取資訊放入library cache

library cache：存SQL語句，SQL語句解析樹，SQL語句執行計劃，還存放控制結構(lock、pin、dependency table等)，也存放從dictionary cache獲得的物件資訊.
library cache使用hash 演算法得到一個值 就是 hash bucket SQL語句應用hash演算法計算出所在的hash bucket 進入該hash buckt進行掃描確定是否存在相同的語句 存在，當該bucket上 此物件的library cache handle不存在 就是第一次執行此時程式會構建一個library cache handle掛到該bucket上 裝載物件 ，如果該物件的library cache handle存在但該handle指向的物件已經換出記憶體，此時物件將重新裝載reload。
 library cache使用多個hash bucket管理 每個hash bucket上串聯多個 library cache handle（存放物件name,namespace,物件標記,heap0物件的指標,heap0 存物件型別，相關的 表 ，實際 執行計劃，執行pl/sql的機器碼等,heap是由一個或多個chunk組成，這些chunk分佈在library cache中 不需要連續）形成一個雙向連結串列

 

libarary cache
hash bucket -------library cache handle-library cache handle(LCO(LIBRARY CACHE object))
hash bucket
.........

SQL> select table_name ,owner from all_tables where table_name='T1';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
T1                             TR
T1                             XH

 

SQL> show user
USER 為 "XH"
SQL> select * from t1;

         A
----------
         1

SQL> show user
USER 為 "TR"
SQL> select * from t1;

         A
----------
         2

SQL> select sid,PREV_HASH_VALUE,SQL_HASH_VALUE from v$session where username='XH
';

       SID PREV_HASH_VALUE SQL_HASH_VALUE
---------- --------------- --------------
       120      2245880055       85427053

SQL> select sid,PREV_HASH_VALUE,SQL_HASH_VALUE from v$session where username='TR
';

       SID PREV_HASH_VALUE SQL_HASH_VALUE
---------- --------------- --------------
       123      2245880055              0

 

SQL> col sql_text format a40
SQL> select sql_text,executions,PARSE_CALLS,child_number from v$sql where hash_v
alue='2245880055';

SQL_TEXT                                 EXECUTIONS PARSE_CALLS CHILD_NUMBER
---------------------------------------- ---------- ----------- ------------
select * from t1                                  1           1            0
select * from t1                                  1           1            1

 

SQL> col sql_text format a30
SQL> select sql_text,version_count,executions,PARSE_CALLS,kept_versions from v$s
qlarea where hash_value='2245880055';

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
KEPT_VERSIONS
-------------
select * from t1                           2          2           2
            0

SQL> desc v$object_dependency;
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------

 FROM_ADDRESS                                       RAW(4)
 FROM_HASH                                          NUMBER
 TO_OWNER                                           VARCHAR2(64)
 TO_NAME                                            VARCHAR2(1000)
 TO_ADDRESS                                         RAW(4)
 TO_HASH                                            NUMBER
 TO_TYPE 
                                          NUMBER
 FROM_ADDRESS  :一個包過程遊標裝載到shared pool中 指向語句的地址
 FROM_HASH:一個包過程遊標在 shared pool中hash value
 TO_OWNER:物件依賴擁有者
 TO_NAME:依賴物件名
 TO_ADDRESS:依賴物件的handle
 TO_HASH：依賴物件的 hash 值
 TO_TYPE:依賴物件的 型別

SQL> select from_address,from_hash,to_owner,to_name,to_address,to_char(to_hash,'
xxxxxxxx') from v$object_dependency where to_name='T1';

FROM_ADD  FROM_HASH TO_OWNER   TO_NAME    TO_ADDRE TO_CHAR(T
-------- ---------- ---------- ---------- -------- ---------
1C6B7124 2245880055 XH         T1         18F71378  bbf0d9e5 ~~~~~~~來自不同的物件,所以有2個子遊標,2個執行計劃
1C6B7124 2245880055 TR         T1         2092EE18  3d0f9e97

ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';

使用 name既SQL語句本身 or 1C6B7124(v$object_dependency.from_address一個包過程遊標裝載到shared pool中 指向語句的地址) 在trace中找
BUCKET 92407:
  LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2)
  name=select * from t1
  hash=cac0083a10f7079b23ea1a1585dd68f7 timestamp=10-08-2009 23:50:22
  namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
  kkkk-dddd-llll=0001-0001-0001 lock=0 pin=0 latch#=2 hpc=0000 hlc=0000
  lwt=1C6B7180[1C6B7180,1C6B7180] ltm=1C6B7188[1C6B7188,1C6B7188]
  pwt=1C6B7164[1C6B7164,1C6B7164] ptm=1C6B716C[1C6B716C,1C6B716C]
  ref=1C6B71A0[1C6B71A0,1C6B71A0] lnd=1C6B71AC[20A78204,18D08B00]
    LIBRARY OBJECT: bject=1de670c0
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 1de6704c  1de66d00 1c50ed24~~~~這個handle 就是不同子游標指向各自的heap的指標
         1 1de6704c  1de66e60 20906790
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 18eaf050 1de67158 I/P/A/-/-    0 NONE   00      0.67     1.05
    HEAP DUMP OF DATA BLOCK 0:
******************************************************
HEAP DUMP heap name="PCursor"  desc=18EAF050
 extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=0
 parent=03C38510 wner=00000000 nex=00000000 xsz=0x424
EXTENT 0 addr=1DE66C68
  Chunk 1de66c70 sz=      596    perm      "perm           "  alo=520
Dump of memory from 0x1DE66C70 to 0x1DE66EC4
1DE66C70 40000255 00000000 1DE670B0 00000208  [U..@.....p......]
1DE66C80 00000000 00000000 00000000 00000000  [................]
1DE66C90 1DE66CF0 00000010 00000004 00000000  [.l..............]

 

 

查詢heap 0,用 子游標的handle  1c50ed24 需要共享到這個heap0才算 真正共享執行計劃~~~否則即使SQL語句一樣也會產生不同執行計劃,產生子游標,並且第一次產生的話是hard parse.

LIBRARY OBJECT HANDLE: handle=1c50ed24 mutex=1C50EDD8(0)
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
  kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=2 hpc=0000 hlc=0000
  lwt=1C50ED80[1C50ED80,1C50ED80] ltm=1C50ED88[1C50ED88,1C50ED88]
  pwt=1C50ED64[1C50ED64,1C50ED64] ptm=1C50ED6C[1C50ED6C,1C50ED6C]
  ref=1C50EDA0[1DE66D00,1DE66D00] lnd=1C50EDAC[1C50EDAC,1C50EDAC]
    LIBRARY OBJECT: bject=1de66860
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    DEPENDENCIES: count=1 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 1de823cc  1de820cc 20a7817c       14 DEP[01]
    READ ONLY DEPENDENCIES: count=1 size=16
    dependency#    table reference   handle flags
    ----------- -------- --------- -------- -------------------
              0 1de66c1c  1de6699c 1c6b7124 /ROD/KPP[60]
    ACCESSES: count=1 size=16
    dependency# types
    ----------- -----
              0 0009
    TRANSLATIONS: count=1 size=16
    original    final
    -------- --------
    20a7817c 20a7817c
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 20af3c38 1de669b0 I/-/A/-/-    0 NONE   00      2.27     3.14
        6 1de82000 1d19bec8 I/-/A/-/-    0 NONE   00      2.88     4.00

看到data#部分0 就是heap 0, 6就是heap 6,其中heap 列就是在記憶體中的實際地址
    HEAP DUMP OF DATA BLOCK 0:
******************************************************
HEAP DUMP heap name="CCursor"  desc=20AF3C38
 extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=3
 parent=03C38510 wner=1DE66860 nex=00000000 xsz=0x424
EXTENT 0 addr=1DE81FE8
  Chunk 1de81ff0 sz=      636    perm      "perm           "  alo=392
Dump of memory from 0x1DE81FF0 to 0x1DE8226C
1DE81FF0 4000027D 00000000 1DE66BA8 00000188  [}..@.....k......]
1DE82000 03C38510 00000FF4 1DE66860 1D19B08C  [........`h......]
1DE82010 00000000 00000000 00000000 020A0200  [................]

******************************************************
    HEAP DUMP OF DATA BLOCK 6:
******************************************************
HEAP DUMP heap name="sql area"  desc=1DE82000
 extent sz=0xff4 alt=32767 het=16 rec=0 flg=2 pc=2
 parent=03C38510 wner=1DE66860 nex=00000000 xsz=0xfe4
EXTENT 0 addr=1D19B08C
  Chunk 1d19b094 sz=     1116    free      "               "
Dump of memory from 0x1D19B094 to 0x1D19B4F0
1D19B090          C000045D 00000000 1DE8206C      [].......l ..]
1D19B0A0 1DE8206C 1D19A26C 1D1BA114 00008100  [l ..l...........]
1D19B0B0 0000001D 1D19B098 607D0318 00000A5C  [..........}`\...]
1D19B0C0 534B0008 46455355 0000474C 00000015  [..KSUSEFLG......]
1D19B0D0 1D19B0B0 607D0300 1D19B160 1D1FC834  [......}``...4...]
1D19B0E0 00000075 1D19B0CC 607B796C 00080000  [u.......ly{`....]

 

以下解釋(來自IT168)

    Heap是通過呼叫伺服器程式進行分配的，任何物件都具有heap 0，至於還應該分配哪些其他的heap則是由物件的型別決定的，比如SQL遊標具有heap 1和 6，而PL/SQL程式包則具有heap 1、2、3和4。按照heap的使用情況，oracle會在SGA（library cache）、PGA或UGA中分配heap，但是heap 0始終都是在library cache中進行分配的。如果所請求的heap已經在SGA中分配了，則不會在PGA中再次分配heap。Heap是由一個或多個chunk組成的，這些 chunk可以是分散的分佈在library cache中的，不需要連續分佈。

1) object type：library cache中的物件型別包括：表、檢視、索引、同名詞等等。每個物件只能有一個object type，根據object type將物件歸類到不同的namespace裡。一個object type對應一個namespace，但是一個namespace可能對應多個object type。這樣的話，查詢一個物件時，只要在該物件所屬的namespace中去找就可以了。比較常見的namespace包括：
a) SQL AREA：也可以叫做CRSR，表示shared cursor，存放共享的SQL語句。
b) TABLE/PROCEDURE：存放的object type包括：table、view、sequence、synonym、 procedure的定義、function的定義以及package的定義。
c) BODY：存放procedure的實際程式碼、function的實際程式碼以及package的實際程式碼。
d) TRIGGER：存放的object type為trigger。
e) INDEX：存放的object type為index。
2) object name：物件名稱由三部分組成：
a) Schema的名稱，對於共享遊標（SQL語句或PL/SQL程式塊）來說為空。
b) 物件名稱。分為兩種情況：對於共享遊標（SQL語句或PL/SQL程式塊）來說，其物件名稱就是SQL的語句本身；而對於其他物件（比如表、檢視、索引等）就是其在資料字典中的名稱。
c) Database link的名稱。這是可選的，如果是本地物件，則為空。
這樣，物件的名稱的格式為：SCHEMA.NAME@DBLINK。比如，可以為hr.employees@apac.com，也可以為hr.employees等。
3) flags：flags主要用來描述物件是否已經被鎖定。物件具有三種型別的flag：
a) public flag：表示物件上沒有鎖定（pin）或者latch。
b) status flag：表示物件上存在鎖定（pin），說明物件正在被建立或刪除或修改等。
c) specitial flag：表示物件上存在library cache latch。
4) tables：對每個物件，都會維護以下一串tables中的若干個：
a) dependency table：含有當前物件所依賴的其他物件。比如一個檢視可能會依賴其組成的多個表、一個儲存過程可能依賴其中所呼叫的其他儲存過程、一個遊標可能依賴其中所涉及到的多個表等。Dependency table中的每個條目都指向一塊實體記憶體，該實體記憶體中含有當前物件所依賴的物件的控制程式碼。
b) child table：含有當前物件的子物件，只有遊標具有child table。Child table中的每個條目都指向一個可執行的SQL命令所對應的控制程式碼。
c) translation table：包含當前物件所引用的名稱是如何解釋為oracle底層物件的名稱，只有遊標具有translation table。
d) authorization table：包含該物件上所對應的許可權，一個條目對應一個許可權。
e) access table：對於dependency table中的每一個條目，都會在access table中存在對應的一個或多個條目。比如，假設物件A依賴物件B，那麼在A的dependency table和access table中都會存在一個條目指向B。位於access table中的指向B的條目說明了對B具有什麼樣的訪問型別，從而也就說明了使用者要執行A則必須具有對B的許可權。
f) read-only dependency table：類似於dependency table，但是存放只讀的物件。
g) schema name table：包含authorization table中的條目所屬的schema。
5) data blocks：物件的其他資訊會存放在不同的heap中，為了找到這些heap，會在heap 0中存放多個（最多16個，但是這16個data block不會都用到）data blocks結構，每個data block含有指向這些實際heap記憶體塊的指標。
除了heap 0以外，還有11個heap，根據物件的不同進行分配，並存放了不同的內容：
1) Heap 1：存放PL/SQL物件的原始碼。
2) Heap 2：存放PL/SQL物件的解析樹，這有個好聽的名字： DIANA。
3) Heap 3：存放PL/SQL物件的虛擬碼。
4) Heap 4：存放PL/SQL物件的基於硬體的虛擬碼。
5) Heap 5：存放了編譯時的錯誤資訊。
6) Heap 6：存放了共享遊標物件的SQL文字。
7) Heap 7：可用空間。
8) Heaps 8–11：根據物件的不同而使用的子heap。

 

 

用heap0中 依賴表部分 這個handle 20a7817c 找到實際依賴物件為tr.t1
 LIBRARY OBJECT HANDLE: handle=20a7817c mutex=20A78230(0)
  name=TR.T1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  hash=e45950492b8f05a2c7475e953d0f9e97 timestamp=10-08-2009 22:37:28
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-0701-0701 lock=N pin=0 latch#=2 hpc=0002 hlc=0002
  lwt=20A781D8[20A781D8,20A781D8] ltm=20A781E0[20A781E0,20A781E0]
  pwt=20A781BC[20A781BC,20A781BC] ptm=20A781C4[20A781C4,20A781C4]
  ref=20A781F8[20A781F8,20A781F8] lnd=20A78204[1C4AF434,1C6B71AC]
    LIBRARY OBJECT: bject=1de81be0
    type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 1c5f5928 1de81c78 I/-/A/-/-    0 NONE   00      0.52     0.00
        8 1de81e08 1dd61214 I/-/A/-/-    0 NONE   00      0.39     1.05
        9 1de81ea0 1dd60a24 I/-/A/-/-    0 NONE   00      0.17     1.05
       10 1de81ef0 1df09b30 I/-/A/-/-    0 NONE   00      0.11     1.05
    HEAP DUMP OF DATA BLOCK 0:
******************************************************
HEAP DUMP heap name="Heap0: KGL"  desc=1C5F5928
 extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=0
 parent=03C38510 wner=1DE81BE0 nex=00000000 xsz=0x414
EXTENT 0 addr=1DE81BC8
  Chunk 1de81bd0 sz=     1036    perm      "perm           "  alo=880
Dump of memory from 0x1DE81BD0 to 0x1DE81FDC
1DE81BD0 5000040D 00000000 00000000 00000370  [...P........p...]
1DE81BE0 20A7817C 1DE81BE4 1DE81BE4 1DE81BEC  [|.. ............]
1DE81BF0 1DE81BEC 00000000 00000000 02010005  [................]

 

關於library cache的控制:
oracle 用lock,pin,library cache latch(修改時用) 控制shared pool library cache結構,如果oracle程式需要修改library cache中資訊 ,那麼再對應的bucket上,先要獲得library cache latch,再接著在相應的handle(handle 可以理解為library cache object 的buffer header)上獲得lock,再在heap上獲得pin
修改完後釋放 PIN,LOCK ,LIBRARY CACHE LATCH

比如user 1 程式 向library cache 填加一個子遊標,此時user 2程式也填加 它們倆所填加的子游標完全一樣 這樣就浪費shared pool空間,還需要防止heap 記憶體被多個程式同時寫入

當使用者 比如實驗中 user xh,執行select * from t1時 對這條SQL語句進行hash運算,找到了bucket  92407 中對應的library cache  handle(bucket 就是由 多個library cache handle 串聯的 雙項鍊表),但發現不能共享以前的執行計劃因為T1來自不同的schema,這時候 需要產生新的執行計劃,新的子游標 向bucket 92407 中對應的library cache object handle 的 CHILDREN中新增一個新handle指向heap0 新的執行計劃,解析樹,物件資訊等,此時這個程式 必須獲得 這個library cache  handle上的lock,其他的程式要等待(library cache lock 等待時間 就是這麼產生的,方式併發 若另一個程式也新增 同樣的children資訊 那浪費空間了)

另外 如果根據SQL語句HASH 找到了bucket找到了 library cache handle 但裡面的子游標handle(指向heap 0)不存在了 表明可能語句執行過,但換了出去,那麼將該sql語句的handle(遊標handle)重新載入到shared pool (v$librarycache.reloads)

 

SQL> select event,total_waits from v$system_event where event='library cache lock';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
library cache lock                                                       4

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
handle address Address of the object being loaded
lock address Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object.
mode Indicates the data pieces of the object which need to be loaded
namespace See "namespace"

 

lock 分3類:null,shared,exclusive,讀取取時一般是獲取null,shared,此時其他程式也可以對相同的handle 加null,shared 的lock,修改時,此時要exclusive 的lock,此時其他程式不能再加exclusived的lock,只能加null 的lock

關於pin:pin是針對heap,主要是防止多個程式對一個heap(記憶體)進行更新操作,獲取PIN 前必須獲取lock,實驗中當user xh執行SQL語句時,在bucket bucket  92407中先獲得library cache lock 檢視發現子游標不能共享(來自不同的SCHEMA) 此時將產生一個hard parse,生成新的執行計劃,新子游標 要寫到這個bucket 的library cache handle中,shared pool會分配chunk(連續記憶體)做為heap 0,heap 6等,獲得該heap上的 PIN ,並在該bucket的 對應 library cache handle 中的children部分加一條子游標記錄 其handle 指向heap 0 然後 向PIN主的HEAP上寫記錄,然後釋放PIN,然後釋放lock ,PIN不住就是等待事件library cache pin,如果heap不在 將heap載入到library cache 同時pin住

PIN有2種模式:shared ,exclusive,讀時 shared pin (此時其它程式不能 exclusive pin),修改時 先shared pin heap進行錯誤安全檢查,如果沒錯就是exclusive pin heap,然後修改

********************
總結:整體shared pool 操作過程 基本是這樣的:
oracle先 將sql語句進行hash 運算,獲取library cahe latch(保護記憶體中執行計劃等,解析時候向library cache中加新執行計劃時候需要獲取該latch)根據sql hash_value在shared pool=>library cache=>bucket=>library cache handle 上先獲得library cache lock(shared ,null 獲取lock防止併發修改handle) 然後查詢該handle子游標資訊 其中子游標資訊handle 指向heap 0,獲得這個HEAP 0的 PIN(SHARED),如果可以共享那麼就使用這個執行計劃物件資訊 解析樹等 是一次soft parse,如果不能共享 比如來自不同schema,SQL語句引用列的 資料型別不一樣等

如果不是這樣的話oracle將釋放library cache latch 獲得shared pool latch(負責分配shared pool空間的latch)分配自由空間chunk 為heap (heap 0,heap 6等)分配後 釋放shared pool latch 再獲得library cache latch(解析過程插入新執行計劃時需要它)並且獲得 該library cache handle的 lock(exclusive因為要修改handle加入新的子游標資訊)然後獲得 分配的chunk 既這些chunk組成的heap 0,6等 獲得這些heap上的 pin(exclusive),並在該bucket的 對應 library cache handle ,中的children部分加一條子游標記錄 其handle 指向heap 0 (新分配的)然後 向PIN主的HEAP上寫記錄,然後釋放PIN ,SQL及其執行計劃寫入library cahce 後,釋放library cache latch 保持null模式的library cache lock,這是一個hard prase.

從實驗中看
當user xh執行時
先用hash value 找到  BUCKET 92407:  持有library cache latch
  LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2)
  name=select * from t1
獲得一個null的lock 檢視 chidren資訊

    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 1de6704c  1de66d00 1c50ed24~~~~這個handle 就是不同子游標指向各自的heap的指標(不是user xh執行的)
         1 1de6704c  1de66e60 20906790(先忽略這個)

根據 1c50ed24找到
LIBRARY OBJECT HANDLE: handle=1c50ed24 mutex=1C50EDD8(0)
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
獲取上面的lock (NULL,SHARED) PIN(SHARED)進行檢視
從依賴表部分發現來自不同的SCHEMA ,需要產生新執行計劃,此時候釋放library cache latch,獲得shared pool latch 分配chunk為heap,釋放shared pool latch 後 再獲得library cache latch 並且獲得lock(exclusive) 因為(要往 BUCKET 92407:  LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2) 這個handle上新增 子游標資訊了)

  1 1de6704c  1de66e60 20906790 新增了這條,然後到新分配的heap 所在的bucket上 獲得lock(exclusive)

BUCKET 92501
 LIBRARY OBJECT HANDLE: handle=20a7817c mutex=20A78230(0)   在這個 handle中新增資訊 (lock(exclusive) )
  name=XH.T1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
並在heap上獲得pin(exclusive)新增新執行計劃SQL程式碼,解析樹等

釋放library cache latch,釋放PIN ,LOCK

************************

關於library cache latch:保護library cache中的 SQL及執行計劃,當oracle搜尋library cache查詢匹配的SQL及執行計劃,若沒有oracle將進行hard parse,獲得library cache latch向library cache中加 新執行計劃SQL程式碼等，搜尋bucket時也要持有library cache latch.
另外 LIBRARY CACHE  LATCH 也管理LOCK 獲得lock前必須持有library cache latch，獲得不到latch那麼將等待,lock結束 釋放library cache latch

關於library cache latch的數量由  _kgl_latch_count 控制

NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ

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

KSPPDESC
-----------------------------------------------------------------------------------
-------------------------------------------------
_kgl_latch_count               0                         TRUE      FALSE      FALSE

number of library cache latches

我的是 10GR2

0 simply means it's default. Actual number of latches is determined by cpu_count. Manually setting cpu_count can change the number of library cache child latches (check in v$latch_children).

該引數預設值為大於等於系統中CPU個數的最小的素數

SQL> select name,gets,misses,sleeps from v$latch_children where name='library cache
';

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
    SLEEPS
----------
library cache                                          212571        102
         5

library cache                                          192186         35
         1

library cache                                          232298         53
         3

 

SQL> col name format a30
SQL> select name,gets,misses,sleeps from v$latch_children where name='library cache
';

NAME                                 GETS     MISSES     SLEEPS
------------------------------ ---------- ---------- ----------
library cache                      213125        102          5
library cache                      192499         35          1
library cache                      232640         53          3

library cache latch負責哪個bucket ,latch號=mod(bucket號,latch的數量)

 

SQL> select latch#,name,gets,misses,sleeps from v$latch_children where name='shared
 pool';

    LATCH# NAME                                 GETS     MISSES     SLEEPS
---------- ------------------------------ ---------- ---------- ----------
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                        308211        227         43

已選擇7行。 只有一個shared pool latch,受sub pool影響每個sub pool都有一個shared pool latch ,_kghdsidx_count控制sub pool數 既控制shared pool latch數量

 

EYGEL大牛有一個比較經典針對library cache pin的等待 用grant 製造的等待,不過我現在的版本模擬不出來了,10R2 PIN 基本要被mutex 一種互斥機制取代了
但還是採用重新編譯或drop 也可以做出來 library cache pin  並針對這個PIN進行分析跟蹤解決

 

SQL> execute calling;~~hang

SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';

會話已更改。

 

BUCKET 51272:
  LIBRARY OBJECT HANDLE: handle=2c93d46c mutex=2C93D520(1)
  name=BEGIN calling; END;
  hash=55ee1c8381a7913b5e42b9d8e98cc848 timestamp=10-10-2009 15:45:23
  namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
  kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=1 hpc=0004 hlc=0004
  lwt=2C93D4C8[2C93D4C8,2C93D4C8] ltm=2C93D4D0[2C93D4D0,2C93D4D0]
  pwt=2C93D4AC[2C93D4AC,2C93D4AC] ptm=2C93D4B4[2C93D4B4,2C93D4B4]
  ref=2C93D4E8[2C93D4E8,2C93D4E8] lnd=2C93D4F4[2C94A9C8,2C93E2C0]
    LIBRARY OBJECT: bject=2ef6165c
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 2ef615e8  2ef6129c 2c93d388
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 20679280 2ef616f4 I/P/A/-/-    0 NONE   00      0.67     1.05
  BUCKET 51272 total object count=1

 

 

BUCKET 47958:
  LIBRARY OBJECT HANDLE: handle=2c93dcfc mutex=2C93DDB0(0)
  name=SYS.CALLING
  hash=eeae667a13b1f1e561f384071b10bb56 timestamp=10-10-2009 15:45:05
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-001d-20bf lock=N pin=S latch#=3 hpc=0006 hlc=0006
  lwt=2C93DD58[2C93DD58,2C93DD58] ltm=2C93DD60[2C93DD60,2C93DD60]
  pwt=2C93DD3C[2C93DD3C,2C93DD3C] ptm=2C93DD44[2C93DD44,2C93DD44]
  ref=2C93DD78[2C93DD78,2C93DD78] lnd=2C93DD84[2C95249C,2C93DFC0]
    LIBRARY OBJECT: bject=3126ab60
    type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
    DEPENDENCIES: count=4 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 3126af1c  3126acb4 2c946f38        0 DEP[01]
              1 3126af1c  3126ace8 33e94310        0 DEP[01]
              2 3126af1c  3126ad1c 33e70f94        0 DEP[01]
              3 3126af1c  3126ad50 33e14d10        0 DEP[01]
    ACCESSES: count=2 size=16
    dependency# types
    ----------- -----
              0 000c
              2 000c
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 33e810ec 3126abf8 I/P/A/-/-    0 NONE   00      0.75     0.00
        2 3126a720 2b9a1b58 I/-/A/-/-    0 NONE   00      4.93     8.00
        4 3126a770 2b99db58 I/P/A/-/-    2 NONE   00      0.63     4.00
  BUCKET 47958 total object count=1

可以看到這時 訪問這個HEAP0 那麼lock為 NULL,PIN為Shared 不許新增exclusive pin

 

SQL> alter procedure calling compile;~~hang

SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';

會話已更改。

 

 

BUCKET 47958:
  LIBRARY OBJECT HANDLE: handle=2c93dcfc mutex=2C93DDB0(0)
  name=SYS.CALLING
  hash=eeae667a13b1f1e561f384071b10bb56 timestamp=10-10-2009 15:45:05
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-001d-20bf lock=X pin=S latch#=3 hpc=0006 hlc=0006
  lwt=2C93DD58[2C93DD58,2C93DD58] ltm=2C93DD60[2C93DD60,2C93DD60]
  pwt=2C93DD3C[31B1C61C,31B1C61C] ptm=2C93DD44[2C93DD44,2C93DD44]
  ref=2C93DD78[2C93DD78,2C93DD78] lnd=2C93DD84[2C95249C,2C93DFC0]
    LIBRARY OBJECT: bject=3126ab60
    type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
    DEPENDENCIES: count=4 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 3126af1c  3126acb4 2c946f38        0 DEP[01]
              1 3126af1c  3126ace8 33e94310        0 DEP[01]
              2 3126af1c  3126ad1c 33e70f94        0 DEP[01]
              3 3126af1c  3126ad50 33e14d10        0 DEP[01]
    ACCESSES: count=2 size=16
    dependency# types
    ----------- -----
              0 000c
              2 000c
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 33e810ec 3126abf8 I/P/A/-/-    0 NONE   00      0.75     0.00
        2 3126a720 2b9a1b58 I/-/A/-/-    0 NONE   00      4.93     8.00
        4 3126a770 2b99db58 I/P/A/-/-    2 NONE   00      0.63     4.00
  BUCKET 47958 total object count=1

此時執行修改,那麼lock 為exclusive,以前是NULL 所以現在可以加上,但PIN 已經是shared 所以 EXCLUSIVE不可以加上,所以等待 產生library cache pin

 

 

 

具體抓到 堵塞SQL 及其 執行SEESION
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait whe
re event like 'library%'
  2  ;

       SID       SEQ#
---------- ----------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
P1RAW            P2 P2RAW            P3 P3RAW    STATE
-------- ---------- -------- ---------- -------- -------------------
       139         53
library cache pin                                                 747887868
2C93DCFC  833734144 31B1C600        301 0000012D WAITING

檢視等待 發現,有library cache pin ,關於裡面p1,p2,p3值的意義 可以檢視 p1text,p2text,p3text, 檢視後p1text: handle address
表示這個HANDLE的地址 trace中BUCKET 47958 上有這個handle

 

SQL> col KGLNAOWN for a10
SQL> col KGLNAOBJ for a20
SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
  2  from X$KGLOB
  3  where KGLHDADR ='2C93DCFC';

ADDR     KGLHDADR KGLHDPAR KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
0805F118 2C93DCFC 2C93DCFC SYS        CALLING               454081366 3126AB60

檢視 誰持有這個handle 可以看到owner sys, 物件是calling ,KGLNAHSH:object hash value

 

SQL> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KG
LPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ from v$session a,x$kglpn b where a.saddr=
b.kglpnuse and b.kglpnhdl = '2C93DCFC' and b.KGLPNMOD<>0;

       SID USERNAME
---------- ------------------------------
PROGRAM                                                          ADDR
---------------------------------------------------------------- --------
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ
-------- -------- -------- -------- -------- ---------- ----------

       133 SYS
sqlplus.exe                                                      0805FB98
31BE1B5C 343209D4 343209D4 2C93DCFC 31BD5544          2          0

查到 持有這個HANDLE的SESSION SID

SQL> select sid,seq#,event,p1text,state from v$session_wait where sid=133;

       SID       SEQ#
---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT
----------------------------------------------------------------
STATE
-------------------
       133          9
PL/SQL lock timer
duration
WAITING

檢視這個佔有handle的使用者在做什麼

用sid 查到hash_value 查到他的 sql_text

SQL> select PREV_HASH_VALUE,sql_hash_value from v$session where sid=133;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
              0     3918317640

trace中  BUCKET 51272

SQL> select sql_text from v$sqlarea where hash_value='3918317640';

SQL_TEXT
--------------------------------------------------------------------------------
BEGIN calling; END;

 

 

 

另一個SESSION
SQL> alter procedure calling compile; hang

這會產生lock因為handle上已經有了 EXCLUSIVE的lock 所以加不上了 ,library cache lock出現

具體的抓SQL方法跟上面一樣

SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait whe
re event like 'library%';

       SID       SEQ#
---------- ----------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
P1RAW            P2 P2RAW            P3 P3RAW    STATE
-------- ---------- -------- ---------- -------- -------------------
       139         53
library cache pin                                                 747887868
2C93DCFC  833734144 31B1C600        301 0000012D WAITING

       159          9
library cache lock                                                747887868
2C93DCFC  834493284 31BD5B64        301 0000012D WAITING

       SID       SEQ#
---------- ----------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
P1RAW            P2 P2RAW            P3 P3RAW    STATE
-------- ---------- -------- ---------- -------- -------------------

 

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-616237/，如需轉載，請註明出處，否則將追究法律責任。