深入理解shared pool共享池之library cache系列二

背景

   繼續上文：深入理解shared pool共享池之library cache系列一，http://blog.itpub.net/9240380/viewspace-1844964/，學習library cache資料結構，本文主要學習library cache object(lco)的資料結構：data blocks


為了直觀理解，先放一張網上關於DATA BLOCK中儲存不同HEAP的結構圖：


    

結論

1,data block是儲存不同HEAP的一個結構，每個HEAP儲存的內容不同
2,分析heap請最終檢視ANONYMOUS LIST:對應的library object handle中的data blocks包括的heap
3,data blocks的結構：
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 a353a238 9d9b7788 I/P/A/-/-    0 NONE   00      2.99     3.12  --heap 0
        6 9d9b7ba8 9bb1f118 I/-/A/-/E    0 NONE   00      5.06     7.90  --heap 6


4，dump特定的data blocks 中對應的heap,其命令：
oradebug setmypid
oradebug dump heapdump_addr 2 0xa353a238  ---0x後面對應上述data blocks中對應的heap addr        
oradebug tracefile_name


5,上述data blocks不同heap可以在v$sgastat中進行對應起來


測試


---基於應用測試示例，便於直觀理解
SQL> show user
USER is "SCOTT"
SQL> create table t_data_block(a int);


Table created.


SQL> select * from t_data_block;


no rows selected


--dump LIBRARY CACHE，僅列出關注部分的內容


BUCKET 36417:
  LIBRARY OBJECT HANDLE: handle=a353a480 mtx=0xa353a5b0(1) lct=1 pct=1 cdp=1
  name=select * from t_data_block
  hash=36f9d01e6a12a3ab7b92ad2ca24a8e41 timestamp=11-23-2015 19:12:41
  namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
  kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=4 hpc=0002 hlc=0002
  lwt=0xa353a528[0xa353a528,0xa353a528] ltm=0xa353a538[0xa353a538,0xa353a538]
  pwt=0xa353a4f0[0xa353a4f0,0xa353a4f0] ptm=0xa353a500[0xa353a500,0xa353a500]
  ref=0xa353a558[0xa353a558,0xa353a558] lnd=0xa353a570[0xa353a570,0xa353a570]
    LOCK OWNERS:
        lock     user  session count mode flags
    -------- -------- -------- ----- ---- ------------------------
    9eb51a40 a4726cc0 a4726cc0     1 N    [00]
    LIBRARY OBJECT: object=9d9b9c28
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 9d9b9b28  9d9b7dd8 a353a2f8  --通過child handle定位到下面的anonymous list中對應的library object handle
    DATA BLOCKS:       
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 a35a4038 9d9b9d40 I/P/A/-/-    0 NONE   00      1.59     2.17
  BUCKET 36417 total object count=1




ANONYMOUS LIST:
LIBRARY OBJECT HANDLE: handle=a353a2f8 mtx=0xa353a428(0) lct=1 pct=2 cdp=0
  namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
  kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=4 hpc=0002 hlc=0002
  lwt=0xa353a3a0[0xa353a3a0,0xa353a3a0] ltm=0xa353a3b0[0xa353a3b0,0xa353a3b0]
  pwt=0xa353a368[0xa353a368,0xa353a368] ptm=0xa353a378[0xa353a378,0xa353a378]
  ref=0xa353a3d0[0x9d9b7dd8,0x9d9b7dd8] lnd=0xa353a3e8[0xa353a3e8,0xa353a3e8]
    CHILD REFERENCES:
    reference latch flags
    --------- ----- -------------------
     9d9b7dd8     7 CHL[02]
    LOCK OWNERS:
        lock     user  session count mode flags
    -------- -------- -------- ----- ---- ------------------------
    9eb1fa90 a4726cc0 a4726cc0     1 N    [00]
    LIBRARY OBJECT: object=9d9b7670
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    DEPENDENCIES: count=2 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 9d9b7138  9d9b6e78 a353b358       14 DEP[01]  
              1 9d9b7138  9d9b6f78 a3539f18        0 DEP[01]  
    ACCESSES: count=1 size=16
    dependency# types
    ----------- -----
              0 0009
    TRANSLATIONS: count=1 size=16
    original    final
    -------- --------
    a353b358 a353b358
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 a353a238 9d9b7788 I/P/A/-/-    0 NONE   00      2.99     3.12  --heap 0
        6 9d9b7ba8 9bb1f118 I/-/A/-/E    0 NONE   00      5.06     7.90  --heap 6


  --dump heap 0
  SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump_addr 2 0xa353a238
Statement processed.
SQL> oradebug tracefile_name
/home/ora10g/admin/ora10g/udump/ora10g_ora_22177.trc
--trc檔案內容，可見HEAP 0對應的堆名為ccursor，儲存SQL執行計劃之類我感覺
HEAP DUMP heap name="CCursor"  desc=0xa353a238


SQL> select pool,name,bytes from v$sgastat where lower(name) like '%ccursor%';


POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  CCursor                       3062792




--dump heap 6 
可見heap 6為儲存SQL文字
HEAP DUMP heap name="sql area"  desc=0x9d9b7ba8   

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