ITPub博客

首页 > 数据库 > Oracle > 深入理解shared pool共享池之library cache系列二

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

原创 Oracle 作者:wisdomone1 时间:2015-11-24 16:43:09 0 删除 编辑

背景

   继续上文:深入理解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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2149
  • 访问量
    11893197