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

时间：2015-11-25 02:14:40 阅读：851 评论：0 收藏：0 [点我收藏+]

背景

为了更为直观理解library cache,贴2张相关图，如下：

bubuko.com,布布扣

结论

1,oradebug dump library_cache不同级别dump的library cache内容及粒度会有所区别，具体见测试开始部分
2,本文测示基于oradebug dump library_cache 32，即产生最为详细的library cachem内容，进而分析library cache数据结构
3,关于创建表的DDL,表，以及基于表的SQL或DML，皆会存储到不同的bucket的library object handle
4,大小写不同的SQL会存储到不同的bucket的library object handle
5,而基于不同用户的相同SQL，会存储到同一个bucket的library object handle
不过在library object handle的结构
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 9da2a040 9da14758 a3552c48 ---同下，这个就是sql的多个子游标，即VERSION_COUNT
1 9da2a040 9d9cf240 a3541fc0 ---这个就是上述基于用户TEST生成的子游标,handle又会指向ANONYMOUS LIST: 中的对应的bucket

进而真正指向
对应不同的 ANONYMOUS LIST: 中的对应的不同的library object handle，所以你从这儿就可以想想，如果一个SQL的子游标过多，它就会花费更多的时间去扫描这些数据结构，哪消耗的LATCH或MUTEX就会更严重
ANONYMOUS LIST:
LIBRARY OBJECT HANDLE: handle=a3552c48 mtx=0xa3552d78(0) lct=2 pct=3 cdp=0 --即对应child#=0的上述子游标的SQL
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=6 hpc=fffe hlc=fffe
lwt=0xa3552cf0[0xa3552cf0,0xa3552cf0] ltm=0xa3552d00[0xa3552d00,0xa3552d00]
pwt=0xa3552cb8[0xa3552cb8,0xa3552cb8] ptm=0xa3552cc8[0xa3552cc8,0xa3552cc8]
ref=0xa3552d20[0x9da14758,0x9da14758] lnd=0xa3552d38[0xa3552d38,0xa3552d38]
CHILD REFERENCES:
reference latch flags
--------- ----- -------------------
9da14758 5 CHL[02]
LIBRARY OBJECT: object=9da13ff0
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=2 size=16 --可见dependencies table结构仅会在ANONYMOUS LIST: 的library object handle中出现
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9da13ab8 9da137f8 a355af28 16 DEP[01]
1 9da13ab8 9da138f8 a3552868 0 DEP[01]
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
a355af28 a355af28
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3552b88 9da14108 I/-/A/-/- 0 NONE 00 2.99 3.12
6 9da14528 9bb9f1d8 I/-/A/-/E 0 NONE 00 5.51 7.90

6，通过反向思维，可见如下的bucket对应ANONYMOUS LIST:的LIBRARY OBJECT HANDLE中的dependencies table
这里存储的是SQL的基表，即dependencies table结构存储SQL的基表
BUCKET 6797:
LIBRARY OBJECT HANDLE: handle=a355af28 mtx=0xa355b058(0) lct=5 pct=5 cdp=0
name=SCOTT.T_LIBRARYCACHE_TEST
hash=05c45c99776e0d8b6ac751c65d6a1a8d timestamp=11-23-2015 12:13:49
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0201 lock=N pin=0 latch#=1 hpc=0004 hlc=0004
lwt=0xa355afd0[0xa355afd0,0xa355afd0] ltm=0xa355afe0[0xa355afe0,0xa355afe0]
pwt=0xa355af98[0xa355af98,0xa355af98] ptm=0xa355afa8[0xa355afa8,0xa355afa8]
ref=0xa355b000[0xa355b000,0xa355b000] lnd=0xa355b018[0xa056f858,0xa3561368]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
9da05028 7 DEP[01] whr=0 timestamp=11-23-2015 12:13:49
9da137f8 5 DEP[01] whr=0 timestamp=11-23-2015 12:13:49
9da03618 1 DEP[01] whr=0 timestamp=11-23-2015 12:13:49
9d9c3340 1 DEP[01] whr=0 timestamp=11-23-2015 12:13:49
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
9eb68b78 a4726cc0 a4726cc0 0 N [4044]
LIBRARY OBJECT: object=9da24a60
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 a355ae68 9da24bb8 I/-/A/-/- 0 NONE 00 0.43 1.09
8 9da1baf0 9bb7be98 I/-/A/-/- 0 NONE 00 0.84 1.09
BUCKET 6797 total object count=1

真白说就是dependency table存储SQL的基表信息

7，可见authorization table结构仅存在于ANONYMOUS LIST中的library object handle
ANONYMOUS LIST:
LIBRARY OBJECT HANDLE: handle=a354e960 mtx=0xa354ea90(0) lct=4 pct=22 cdp=0
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=11 hpc=fffa hlc=fffa
lwt=0xa354ea08[0xa354ea08,0xa354ea08] ltm=0xa354ea18[0xa354ea18,0xa354ea18]
pwt=0xa354e9d0[0xa354e9d0,0xa354e9d0] ptm=0xa354e9e0[0xa354e9e0,0xa354e9e0]
ref=0xa354ea38[0x9da0a3a8,0x9da0a3a8] lnd=0xa354ea50[0xa354ea50,0xa354ea50]
CHILD REFERENCES:
reference latch flags
--------- ----- -------------------
9da0a3a8 0 CHL[02]
LIBRARY OBJECT: object=9da09c40
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9da09708 9da09448 a39b2420 18 DEP[01]
AUTHORIZATIONS: count=1 size=16 minimum entrysize=18
0000 00000000 00004000 00000000 00000000
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0006
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a354e8a0 9da09d58 I/-/A/-/- 0 NONE 00 3.63 4.16
6 9da0a178 9bb516e0 I/-/A/-/E 0 NONE 00 20.03 23.74

直白的说就是：存储SQL查询相关的授权信息，当然这块内容我的理解还不够，还要研究

8，可见SQL的子游标信息是存储在ANONYMOUS LIST:中，而其没有bucket的概念，仅有library object handle的概念

9，关于lco的数据结构data block还没有研究，下文进行测试

测试

----基于之前的文章：oracle library cache之trace小记，了解下DUMP LIBRARY CACHE的命令区别
http://blog.itpub.net/9240380/viewspace-759136/

如下命令含义：
ALTER SESSION SET EVENTS ‘immediate trace name LIBRARY_CACHE level LL‘;(也适用于oradebug dump library_cache，只是2种命令形式)

其中LL代表Level级别,对于9.2.0及以后版本,不同Level含义如下:
Level =1 ,转储Library cache统计信息
Level =2 ,转储hash table概要
Level =4 ,转储Library cache对象，只包含基本信息
Level =8 ,转储Library cache对象，包含详细信息(包括child references,pin waiters等)
Level =16,增加heap sizes信息
Level =32,增加heap信息

---为了针对性分析library cache的结构，生成测试用例
SQL> show user
USER is "SCOTT"

SQL> create table t_librarycache_test(a int,b int);

Table created.

SQL> select a,b from t_librarycache_test;

no rows selected

---转储LIBRARY_CACHE
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump library_cache 32
Statement processed.
SQL> oradebug tracefile_name
/home/ora10g/admin/ora10g/udump/ora10g_ora_12595.trc

----这就是上述测试示例中查询SQL对应的bucket
BUCKET 103856:
LIBRARY OBJECT HANDLE: handle=a3552e78 mtx=0xa3552fa8(1) lct=1 pct=1 cdp=1
name=select a,b from t_librarycache_test
hash=af65d142f6d90c766be1ab2d410795b0 timestamp=11-23-2015 12:14:05
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=6 hpc=0002 hlc=0002
lwt=0xa3552f20[0xa3552f20,0xa3552f20] ltm=0xa3552f30[0xa3552f30,0xa3552f30]
pwt=0xa3552ee8[0xa3552ee8,0xa3552ee8] ptm=0xa3552ef8[0xa3552ef8,0xa3552ef8]
ref=0xa3552f50[0xa3552f50,0xa3552f50] lnd=0xa3552f68[0xa3552f68,0xa3552f68]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
9ebce080 a4726cc0 a4726cc0 1 N [00]
LIBRARY OBJECT: object=9da2a140
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 9da2a040 9da14758 a3552c48
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3552db8 9da2a258 I/P/A/-/- 0 NONE 00 1.59 2.17
BUCKET 103856 total object count=1

---可见与测试示例中查询SQL所依赖的表也对应一个BUCKET
BUCKET 6797:
LIBRARY OBJECT HANDLE: handle=a355af28 mtx=0xa355b058(0) lct=2 pct=2 cdp=0
name=SCOTT.T_LIBRARYCACHE_TEST
略
BUCKET 6797 total object count=1

---同上，可见相关表的DDL及DML及基表皆会归属到不同的BUCKET中
BUCKET 92409:
LIBRARY OBJECT HANDLE: handle=a3560e98 mtx=0xa3560fc8(1) lct=1 pct=1 cdp=0
name=create table t_librarycache_test(a int,b int)
略
BUCKET 92409 total object count=1

----继续测试用例
SQL> select a,b from t_librarycache_test where rownum<1;

no rows selected

SQL> select a,b from t_librarycache_test where rownum<2;

no rows selected

可见上述2个测试SQL示例，是会对应2个不同的BUCKET中的LIBRARY OBJECT HANDLE
BUCKET 48737:
LIBRARY OBJECT HANDLE: handle=a35c5208 mtx=0xa35c5338(1) lct=1 pct=1 cdp=1
name=select a,b from t_librarycache_test where rownum<1

BUCKET 94348:
LIBRARY OBJECT HANDLE: handle=a357b328 mtx=0xa357b458(1) lct=1 pct=1 cdp=1
name=select a,b from t_librarycache_test where rownum<2

---继续测试，在新的用户创建上述相同的测试用例（即相同表且相同查询SQL）
SQL> conn test/system
Connected.
SQL> create table t_librarycache_test(a int,b int);

Table created.

SQL> select a,b from t_librarycache_test;

no rows selected

可见如果基于不同用户但查询SQL相同，会生成多少子游标的SQL
BUCKET 103856:
LIBRARY OBJECT HANDLE: handle=a3552e78 mtx=0xa3552fa8(2) lct=2 pct=1 cdp=2
name=select a,b from t_librarycache_test
hash=af65d142f6d90c766be1ab2d410795b0 timestamp=11-23-2015 12:14:05
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=6 hpc=0000 hlc=0000
lwt=0xa3552f20[0xa3552f20,0xa3552f20] ltm=0xa3552f30[0xa3552f30,0xa3552f30]
pwt=0xa3552ee8[0xa3552ee8,0xa3552ee8] ptm=0xa3552ef8[0xa3552ef8,0xa3552ef8]
ref=0xa3552f50[0xa3552f50,0xa3552f50] lnd=0xa3552f68[0xa3552f68,0xa3552f68]
LIBRARY OBJECT: object=9da2a140
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 9da2a040 9da14758 a3552c48 ---同下，这个就是sql的多个子游标，即VERSION_COUNT
1 9da2a040 9d9cf240 a3541fc0 ---这个就是上述基于用户TEST生成的子游标,handle又会指向ANONYMOUS LIST: 中的对应的bucket
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3552db8 9da2a258 I/P/A/-/- 0 NONE 00 1.59 2.17
BUCKET 103856 total object count=1

--继续测试，相同SQL，但大小写不同
SQL> select a,b from t_librarycache_TEST;

no rows selected

可见相同SQL，但大小写相同，仍会创建新的BUCKET及所属的library object handle
BUCKET 72150:
LIBRARY OBJECT HANDLE: handle=a3546fa0 mtx=0xa35470d0(1) lct=1 pct=1 cdp=1
name=select a,b from t_librarycache_TEST
hash=d1d69941caf41b2804ac6df2538519d6 timestamp=11-23-2015 12:47:40
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=10 hpc=0002 hlc=0002
lwt=0xa3547048[0xa3547048,0xa3547048] ltm=0xa3547058[0xa3547058,0xa3547058]
pwt=0xa3547010[0xa3547010,0xa3547010] ptm=0xa3547020[0xa3547020,0xa3547020]
ref=0xa3547078[0xa3547078,0xa3547078] lnd=0xa3547090[0xa3547090,0xa3547090]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
9ebce080 a4726cc0 a4726cc0 1 N [00]
LIBRARY OBJECT: object=9d9c4840
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 9d9c4740 9d9c42a0 a3546d60
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3546ee0 9d9c4958 I/P/A/-/- 0 NONE 00 1.59 2.17
BUCKET 72150 total object count=1

由下可见相同SQL的不同子游标存储到ANONYMOUS LIST的不同的library object handle
ANONYMOUS LIST:
LIBRARY OBJECT HANDLE: handle=a3552c48 mtx=0xa3552d78(0) lct=2 pct=3 cdp=0 --即对应child#=0的上述子游标的SQL
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=6 hpc=fffe hlc=fffe
lwt=0xa3552cf0[0xa3552cf0,0xa3552cf0] ltm=0xa3552d00[0xa3552d00,0xa3552d00]
pwt=0xa3552cb8[0xa3552cb8,0xa3552cb8] ptm=0xa3552cc8[0xa3552cc8,0xa3552cc8]
ref=0xa3552d20[0x9da14758,0x9da14758] lnd=0xa3552d38[0xa3552d38,0xa3552d38]
CHILD REFERENCES:
reference latch flags
--------- ----- -------------------
9da14758 5 CHL[02]
LIBRARY OBJECT: object=9da13ff0
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=2 size=16 --可见dependencies table结构仅会在ANONYMOUS LIST: 的library object handle中出现
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9da13ab8 9da137f8 a355af28 16 DEP[01]
1 9da13ab8 9da138f8 a3552868 0 DEP[01]
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
a355af28 a355af28
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3552b88 9da14108 I/-/A/-/- 0 NONE 00 2.99 3.12
6 9da14528 9bb9f1d8 I/-/A/-/E 0 NONE 00 5.51 7.90

LIBRARY OBJECT HANDLE: handle=a3541fc0 mtx=0xa35420f0(0) lct=1 pct=2 cdp=0 --即对应child#=1的上述子游标的SQL
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=6 hpc=0000 hlc=0000
lwt=0xa3542068[0xa3542068,0xa3542068] ltm=0xa3542078[0xa3542078,0xa3542078]
pwt=0xa3542030[0xa3542030,0xa3542030] ptm=0xa3542040[0xa3542040,0xa3542040]
ref=0xa3542098[0x9d9cf240,0x9d9cf240] lnd=0xa35420b0[0xa35420b0,0xa35420b0]
CHILD REFERENCES:
reference latch flags
--------- ----- -------------------
9d9cf240 5 CHL[02]
LIBRARY OBJECT: object=9d9ceba8
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=2 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9d9c58c0 9d9c5600 a3542c08 16 DEP[01]
1 9d9c58c0 9d9c5700 a3552868 0 DEP[01]
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
a3542c08 a3542c08
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3541f00 9d9cecc0 I/-/A/-/- 0 NONE 00 2.93 3.12
6 9d9cf0e0 9bb2c580 I/-/A/-/E 0 NONE 00 5.05 7.90

通过反向思维，可见如下的bucket对应ANONYMOUS LIST:的LIBRARY OBJECT HANDLE中的dependencies table
这里存储的是SQL的基表，即dependencies table结构存储SQL的基表
BUCKET 6797:
LIBRARY OBJECT HANDLE: handle=a355af28 mtx=0xa355b058(0) lct=5 pct=5 cdp=0
name=SCOTT.T_LIBRARYCACHE_TEST
hash=05c45c99776e0d8b6ac751c65d6a1a8d timestamp=11-23-2015 12:13:49
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0201 lock=N pin=0 latch#=1 hpc=0004 hlc=0004
lwt=0xa355afd0[0xa355afd0,0xa355afd0] ltm=0xa355afe0[0xa355afe0,0xa355afe0]
pwt=0xa355af98[0xa355af98,0xa355af98] ptm=0xa355afa8[0xa355afa8,0xa355afa8]
ref=0xa355b000[0xa355b000,0xa355b000] lnd=0xa355b018[0xa056f858,0xa3561368]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
9da05028 7 DEP[01] whr=0 timestamp=11-23-2015 12:13:49
9da137f8 5 DEP[01] whr=0 timestamp=11-23-2015 12:13:49
9da03618 1 DEP[01] whr=0 timestamp=11-23-2015 12:13:49
9d9c3340 1 DEP[01] whr=0 timestamp=11-23-2015 12:13:49
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
9eb68b78 a4726cc0 a4726cc0 0 N [4044]
LIBRARY OBJECT: object=9da24a60
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 a355ae68 9da24bb8 I/-/A/-/- 0 NONE 00 0.43 1.09
8 9da1baf0 9bb7be98 I/-/A/-/- 0 NONE 00 0.84 1.09
BUCKET 6797 total object count=1

可见authorization table结构仅存在于ANONYMOUS LIST中的library object handle
ANONYMOUS LIST:
LIBRARY OBJECT HANDLE: handle=a354e960 mtx=0xa354ea90(0) lct=4 pct=22 cdp=0
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=11 hpc=fffa hlc=fffa
lwt=0xa354ea08[0xa354ea08,0xa354ea08] ltm=0xa354ea18[0xa354ea18,0xa354ea18]
pwt=0xa354e9d0[0xa354e9d0,0xa354e9d0] ptm=0xa354e9e0[0xa354e9e0,0xa354e9e0]
ref=0xa354ea38[0x9da0a3a8,0x9da0a3a8] lnd=0xa354ea50[0xa354ea50,0xa354ea50]
CHILD REFERENCES:
reference latch flags
--------- ----- -------------------
9da0a3a8 0 CHL[02]
LIBRARY OBJECT: object=9da09c40
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9da09708 9da09448 a39b2420 18 DEP[01]
AUTHORIZATIONS: count=1 size=16 minimum entrysize=18
0000 00000000 00004000 00000000 00000000
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0006
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a354e8a0 9da09d58 I/-/A/-/- 0 NONE 00 3.63 4.16
6 9da0a178 9bb516e0 I/-/A/-/E 0 NONE 00 20.03 23.74

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

原文：http://blog.itpub.net/9240380/viewspace-1844964/

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)