[20210507]dump library_cache.txt
[20210507]dump library_cache.txt
--//放假看了一些共享池方面的文档，重新学习一下这方面的知识。
--//学习alter session set events 'immediate trace name library_cache level N';
--//N 表示如下：
. Level=1，转储库缓存统计信息。
. Level=2，转储散列表概要。
. Level=4，转储库缓存对象，只包含基本信息。
. Level=8，转储库缓存对象，包含详细信息(包括 child references、pin waiters等)。
. Level=16，增加堆大小信息。
. Level=32，增加堆信息。
--//我看了N多文档，多少情况下选择10，我感觉选择8就可以。先测试选择1，2的情况。
--//你可以组合形成自己需要的输出。
--//比如6=4+2 表示转储散列表概要 和 转储库缓存对象，只包含基本信息。
1.环境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.测试：
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12044_0001.trc
SYS@book> alter session set events 'immediate trace name library_cache level 1';
Session altered.
--//查看转储：
Library Cache Dump
LIBRARY CACHE STATISTICS:
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
SQL AREA 50593 0.799 356372 0.928 6773 358
TABLE/PROCEDURE 54154 0.883 92671 0.734 10636 0
BODY 2937 0.818 6109 0.899 16 0
TRIGGER 609 0.801 609 0.800 0 0
INDEX 1773 0.851 1647 0.497 435 0
CLUSTER 1753 0.978 1797 0.979 0 0
KGL TESTING 0 0.000 0 0.000 0 0
PIPE 0 0.000 0 0.000 0 0
LOB 0 0.000 0 0.000 0 0
DIRECTORY 3 0.333 6 0.333 0 0
QUEUE 4 0.500 6 0.333 0 0
REPLICATION OBJECT GROUP 0 0.000 0 0.000 0 0
REPLICATION PROPAGATOR 0 0.000 0 0.000 0 0
JAVA SOURCE 0 0.000 0 0.000 0 0
JAVA RESOURCE 0 0.000 0 0.000 0 0
REPLICATED TABLE OBJECT 0 0.000 0 0.000 0 0
REPLICATION INTERNAL PACKAGE 0 0.000 0 0.000 0 0
CONTEXT POLICY 0 0.000 0 0.000 0 0
PUB SUB INTERNAL INFORMATION 454 0.874 583 0.854 27 0
SUMMARY 0 0.000 0 0.000 0 0
DIMENSION 0 0.000 0 0.000 0 0
APP CONTEXT 0 0.000 0 0.000 0 0
STORED OUTLINE 0 0.000 0 0.000 0 0
RULESET 1 0.000 3 0.667 0 0
RESOURCE MANAGER 275 0.796 294 0.776 0 0
XML SCHEMA 0 0.000 0 0.000 0 0
PENDING RESOURCE MANAGER PLAN 0 0.000 0 0.000 0 0
PENDING RESOURCE MANAGER CONSUMER GROUP 0 0.000 0 0.000 0 0
SUBSCRIPTION 0 0.000 0 0.000 0 0
LOCATION 0 0.000 0 0.000 0 0
REMOTE OBJECT INFORMATION 0 0.000 0 0.000 0 0
SNAPSHOT META DATA 0 0.000 0 0.000 0 0
JAVA DATA 0 0.000 0 0.000 0 0
SECURITY PROFILE 0 0.000 0 0.000 0 0
TRANSFORMATION 0 0.000 0 0.000 0 0
REPLICATION - LOG BASED CHILD 0 0.000 0 0.000 0 0
RULE 0 0.000 0 0.000 0 0
STREAM 0 0.000 0 0.000 0 0
RULE EVALUATION CONTEXT 1 0.000 0 0.000 0 0
STREAMS APPLY PROCESS 0 0.000 0 0.000 0 0
REPLICATION SOURCE 0 0.000 0 0.000 0 0
REPLICATION DESTINATION 0 0.000 0 0.000 0 0
IFS SCHEMA 0 0.000 0 0.000 0 0
XDB CONFIG 0 0.000 0 0.000 0 0
USER AGENT 0 0.000 0 0.000 0 0
MULTI-VERSION OBJECT FOR TABLE 672 0.812 666 0.817 0 0
SCHEDULER EVENT QUEUE INFORMATION 0 0.000 0 0.000 0 0
CHANGE SET 0 0.000 0 0.000 0 0
MULTI-VERSION OBJECT FOR INDEX 673 0.814 667 0.819 0 0
SQL TUNING BASE OBJECT 0 0.000 0 0.000 0 0
HINTSET OBJECT 0 0.000 0 0.000 0 0
SCHEDULER GLOBAL ATTRIBUTE 21897 0.996 21897 0.996 1 0
SCHEDULER EARLIEST START TIME 727 0.916 717 0.738 127 0
TEMPORARY TABLE 320 0.697 320 0.000 223 0
TEMPORARY CLUSTER 0 0.000 0 0.000 0 0
TEMPORARY INDEX 125 0.008 125 0.000 1 0
SCRATCH PAD 0 0.000 0 0.000 0 0
SCHEDULER JOB SLAVE 0 0.000 0 0.000 0 0
MINING MODELS 0 0.000 0 0.000 0 0
SYNC STREAMS CAPTURE 0 0.000 0 0.000 0 0
LIGHT WEIGHT SESSION 0 0.000 0 0.000 0 0
DATA SECURITY DOCUMENT 0 0.000 0 0.000 0 0
SECURITY CLASS 0 0.000 0 0.000 0 0
XDB ACL 0 0.000 0 0.000 0 0
EDITION 619 0.987 830 0.958 21 0
SCHEDULER LIGHTWEIGHT JOB 0 0.000 0 0.000 0 0
REMOTE OBJECT LOCAL SUMMARY REFERENCE 0 0.000 0 0.000 0 0
STREAMS PROPOGATION 0 0.000 0 0.000 0 0
FUSION XS PARAMETERS 0 0.000 0 0.000 0 0
DBLINK 94 0.723 0 0.000 0 0
XDB REPOSITORY 0 0.000 0 0.000 0 0
EV BASE TABLE 0 0.000 0 0.000 0 0
OBJECT ID 87 0.000 0 0.000 0 0
SCHEMA 9877 0.996 0 0.000 0 0
DBINSTANCE 1 0.000 0 0.000 0 0
SQL AREA STATS 9765 0.538 9765 0.538 1 0
CONTEXT MVDATA 0 0.000 0 0.000 0 0
CONTEXT MTABLE 0 0.000 0 0.000 0 0
CONTEXT FTABLE 0 0.000 0 0.000 0 0
ACCOUNT_STATUS 19 0.737 0 0.000 0 0
PCTABL 0 0.000 0 0.000 0 0
PCINDX 0 0.000 0 0.000 0 0
SQL AREA BUILD 10469 0.040 0 0.000 0 0
KGLNEDSYNC 0 0.000 0 0.000 0 0
KZP Exempt Access Policy list 0 0.000 0 0.000 0 0
SDO Index MetaData 0 0.000 0 0.000 0 0
SDO Geom MetaData 0 0.000 0 0.000 0 0
SDO SRID MetaData 0 0.000 0 0.000 0 0
SDO Product Usage 0 0.000 0 0.000 0 0
SDO SRID CRS DIM 0 0.000 0 0.000 0 0
SDO Row Cache 0 0.000 0 0.000 0 0
CUMULATIVE 167902 0.804 495084 0.884 18261 358
SGA:0x80a6fea8 Flags=ac3
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0
--//实际上这个基本与查询视图V$LIBRARYCACHE的输出类似，只不过视图V$LIBRARYCACHE的输出信息少一些。
SELECT inst_id
,kglstdsc
,kglstget
,kglstght
,DECODE (kglstget, 0, 1, kglstght / kglstget)
,kglstpin
,kglstpht
,DECODE (kglstpin, 0, 1, kglstpht / kglstpin)
,kglstrld
,kglstinv
,kglstlrq
,kglstprq
,kglstprq
,kglstmiv
,kglstmiv
FROM x$kglst
WHERE kglsttyp = 'NAMESPACE' AND kglstget != 0 AND LENGTH (kglstdsc) <= 15
--//只要去掉后面两个条件kglstget != 0 AND LENGTH (kglstdsc) <= 15,输出行数基本一致，还有转储多了一个汇总功能CUMULATIVE罢了。
3.继续：
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12044_0002.trc
SYS@book> alter session set events 'immediate trace name library_cache level 2';
Session altered.
Library Cache Dump
SGA:0x80a6fea8 Flags=ac3
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0
LIBRARY CACHE HASH TABLE: size=131072 count=3172
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 127939
1 3094
2 39
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
--//这个信息通过那个视图查看，我不知道，或者根本没有。
SYS@book> @ fcha 80a6fea8
Find in which heap (UGA, PGA or Shared Pool) the memory address 80a6fea8 resides...
WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention
in systems under load and with large shared pool. This may even completely hang
your instance until the query has finished! You probably do not want to run this in production!
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000080834000 1 1 permanent memor 3974984 perm 0 00
3.使用level 6转储看看：
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12044_0003.trc
SYS@book> alter session set events 'immediate trace name library_cache level 6';
Session altered.
$ grep "^Bucket:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12044_0003.trc|wc -l
3707
--//检查转储。
Library Cache Dump
SGA:0x80a6fea8 Flags=ac3
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0
LIBRARY CACHE HASH TABLE: size=131072 count=3764
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 127365
1 3650
2 57
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
--//我测试多次结果类似。Hash Chain Size >0 的Number of Buckets 相加 3650+57 = 3707，正好等于前面输出的Bucket:的数量。
--//全部相加 等于127365+3650+57 = 131072,正好等于HASH TABLE: size ＝ 131072.
--//是否可以这样理解,我的猜测:
Hash Chain Size=0,Number of Buckets=127365,表示这些Bucket还没有库缓存对象占用.
Hash Chain Size=1,Number of Buckets=3650 ,表示这些Bucket仅仅有1个库缓存对象占用.
Hash Chain Size=2,Number of Buckets=57 ,表示这些Bucket仅仅有2个库缓存对象占用.
--//一个简单的过滤就可以确定我的判断是否正确:
$ grep "^Bucket:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12044_0003.trc|wc -l
3707
$ grep -E "^Bucket:|^ LibraryHandle:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12044_0003.trc|wc -l
7471
$ grep -E "|^ LibraryHandle:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12044_0003.trc|wc -l
3764
--//7471-3764 = 3707,正好等于包含^Bucket:行的数量.
--//1*3650+2*57 = 3764,正好等于包含^ LibraryHandle:行的数量.也就是出现3764个LibraryHandle.相当于3764个库缓存对象.
--//^_^,这样正好验证我的判断,注意我的测试环境基本我在使用,没有任何人使用,整个测试是可控的,这样上下不会存在偏差.
--//另外可以从如下输出看出我的判断基本正确:
Buckets with more than 20 objects:
NONE
---//还有如下:
LIBRARY CACHE HASH TABLE: size=131072 count=3764
--//count=3764,正好对应库缓存对象.
--//如何写简单脚本实现1个Bucket下,有2行^ LibraryHandle:的输出呢?那位知道,期待...先放一下.
--//找一个生产系统验证看看，注意如果共享池很大，转储可能有点慢，特别是level更大的情况。
# grep "^Bucket:" /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_17434.trc|wc -l
80010
SGA:0x64f865b30 Flags=ae3
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0
LIBRARY CACHE HASH TABLE: size=131072 count=124771
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 51083
1 47674
2 22636
3 7397
4 1855
5 362
6 55
7 10
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
--//47674+22636+7397+1855+362+55+10 = 79989
--//51083+47674+22636+7397+1855+362+55+10 = 131072
--//我估计生产系统业务比较忙，这样的查询结果不确定，不过还是很接近grep过滤后的输出80010。而测试环境我做了几次都是对的。
--//0*51083+1*47674+2*22636+3*7397+4*1855+5*362+6*55+7*10 = 124767
--//与前面的输出LIBRARY CACHE HASH TABLE: size=131072 count=124771,的count存在一点点差异.
--//我自己有点意外的是生产系统存在这么高的hash冲突呢.也许这样的情况是正常的.
4.验证库缓存对象应该放入那个Bucket:
--//还可以验证放入那个Bucket是根据库缓存对象的hash values与131072取模确定的。我贴几个例子看看：
Bucket: #=24 Mutex=0x80a71318(0, 33, 0, 6)
LibraryHandle: Address=0x7d5cd780 Hash=d4f20018 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SYS.DBA_TYPES
FullHashValue=a9cc3db174ef4fdb7644abefd4f20018 Namespace=TABLE/PROCEDURE(01) Type=VIEW(04) Identifier=3693 OwnerIdn=0
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7d5cd830(0, 1, 0, 0) Mutex=0x7d5cd8c0(58, 26, 0, 6)
Flags=PIN/TIM/[00002801]
WaitersLists:
Lock=0x7d5cd810[0x7d5cd810,0x7d5cd810]
Pin=0x7d5cd7f0[0x7d5cd7f0,0x7d5cd7f0]
LoadLock=0x7d5cd868[0x7d5cd868,0x7d5cd868]
Timestamp: Current=08-24-2013 11:38:31
HandleReference: Address=0x7d5cd938 Handle=0x7ea4a118 Flags=OWN[200]
LibraryObject: Address=0x7cbac490 HeapMask=0000-0101-0101-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
--//Hash=d4f20018
--//d4f20018 = 3572629528
--//3572629528 % 131072 = 24
Bucket: #=131041 Mutex=0x8063e358(0, 197, 0, 6)
LibraryHandle: Address=0x7bc816d0 Hash=3abffe1 LockMode=0 PinMode=0 LoadLockMode=0 Status=0
ObjectName: Name=EXFSYS.RLM$ORDERCLSALS
FullHashValue=22e37cd8f2dabea34c6490bb03abffe1 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=70471 OwnerIdn=42
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=0 TotalLockCount=0 TotalPinCount=0
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=6 HandleInUse=6 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7bc81780(0, 13, 0, 0) Mutex=0x7bc81810(58, 87, 0, 6)
Flags=PIN/TIM/[00000800]
WaitersLists:
Lock=0x7bc81760[0x7bc81760,0x7bc81760]
Pin=0x7bc81740[0x7bc81740,0x7bc81740]
LoadLock=0x7bc817b8[0x7bc817b8,0x7bc817b8]
Timestamp: Current=08-24-2013 11:49:33
HandleReference: Address=0x7bc81890 Handle=(nil) Flags=[00]
--//Hash=3abffe1
--//3abffe1 = 61603809
--//61603809 % 131072 = 131041
--//可以发现取模后的计算结果正好对上。
--//再找一个放2个库缓存对象的bucket:
Bucket: #=2722 Mutex=0x80a8b8a8(0, 72, 0, 6)
LibraryHandle: Address=0x7d6449f8 Hash=ae0e0aa2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
LibraryHandle: Address=0x7c991c18 Hash=a8240aa2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
--//注输出太长,我仅仅截取LibraryHandle的行输出.
--//实际上从hash的后面4位一样基本可以验证.
--//131072 = 0x20000,相当于2进制移位 4*5+1=21位.
--//ae0e0aa2 = 2920155810
--//2920155810 % 131072 = 2722
--//a8240aa2 = 2820934306
--//2820934306 % 131072 = 2722
--//找一个出现hash 冲突的情况，看看转储的情况.写的有点长另外写一篇blog.
