建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
源起于對(duì)實(shí)例的systemdump分析，現(xiàn)把library cache dump的部分單拿出來(lái)，做個(gè)簡(jiǎn)單的猜想。
創(chuàng)新互聯(lián)公司是一家集成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、網(wǎng)站頁(yè)面設(shè)計(jì)、網(wǎng)站優(yōu)化SEO優(yōu)化為一體的專(zhuān)業(yè)的建站公司，已為成都等多地近百家企業(yè)提供網(wǎng)站建設(shè)服務(wù)。追求良好的瀏覽體驗(yàn),以探求精品塑造與理念升華,設(shè)計(jì)最適合用戶(hù)的網(wǎng)站頁(yè)面。 合作只是第一步，服務(wù)才是根本,我們始終堅(jiān)持講誠(chéng)信，負(fù)責(zé)任的原則，為您進(jìn)行細(xì)心、貼心、認(rèn)真的服務(wù),與眾多客戶(hù)在蓬勃發(fā)展的市場(chǎng)環(huán)境中,互促共生。
SYS@moe SQL>col KGLNAOBJ for a40 SYS@moe SQL>set line 123 SYS@moe SQL>select * from scott.dept; DEPTNODNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SYS@moe SQL>selectkglhdadr,kglhdpar,kglnaobj,KGLOBHS0,kglobhd0,KGLOBHS6,KGLOBHD6 from x$kglobwhere kglnaobj like 'select * from scott.dept%'; KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHS0 KGLOBHD0 KGLOBHS6 KGLOBHD6 ---------------- ---------------------------------------------- ---------- ---------------- -------------------------- 000000009BED2700 000000009BF74618 select *fromscott.dept 4488 000000009BED3100 8088 000000009E287B88 000000009BF74618 000000009BF74618 select *fromscott.dept 4720 000000009BECF350 000
KGLHDADR為librarycache handle的地址
KGLHDPAR為父地址
KGLNAOBJ為librarycache object
KGLOBHD0為heap0地址
KGLOBHD6為heap6地址
SYS@moe SQL>select KSMCHPTR,KSMCHCOM,KSMCHCLS,KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009BECF350'; 父游標(biāo)hd0 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ ---------------- ---------------- ------------------ 000000009E287400 KGLH0^3658de8a recr 4096
KSMCHPTR為chunk地址
KSMCHPAR為x$kglob中查詢(xún)出來(lái)的heap的地址
SYS@moe SQL>selectKSMCHPTR,KSMCHCOM,KSMCHCLS ,KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009BED3100'; 子游標(biāo)hd0 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ ---------------- ---------------- ------------------ 000000009E286400 KGLH0^3658de8a recr 4096 SYS@moe SQL>selectKSMCHPTR,KSMCHCOM,KSMCHCLS ,KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009E287B88'; 子游標(biāo)hd6 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ ---------------- ---------------- ------------------ 000000009C1826D8SQLA^3658de8a recr 4096 000000009C17E6D8SQLA^3658de8a freeabl 4096 SYS@moe SQL>selectsql_id,hash_value,address,child_address,sql_text from v$sql where sql_text like'select * from scott.dept%'; SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS ------------- ---------- -------------------------------- SQL_TEXT f6hhpzwv5jrna 911793802000000009BF74618 000000009BED2700 select * from scott.dept SYS@moe SQL>alter session set events'immediate trace name library_cache level 16'; Session altered.
下面是trc部分：
Bucket: #=56970 Mutex=0xa3b59180(0, 23, 0,6)
LibraryHandle: Address=0x9bf74618 Hash=3658de8a LockMode=0 PinMode=0 LoadLockMode=0Status=VALD
Address=0x9bf74618這個(gè)值是x$kglob.KGLHDPAR的值
Hash=3658de8a這個(gè)值是x$ksmsp.KSMCHCOM的值，轉(zhuǎn)成10進(jìn)制是911793802，是v$sql. HASH_VALUE的值
SYS@moe SQL>selectto_number('3658de8a','xxxxxxxxxxx') from dual;
TO_NUMBER('3658DE8A','XXXXXXXXXXX')
-----------------------------------
911793802
ObjectName: Name=select * from scott.dept 這里是執(zhí)行的語(yǔ)句，是x$kglob.KGLNAOBJ的值，也是v$sql. SQL_TEXT的值
FullHashValue=66438da3ebbc48a3e34215ff3658de8a Namespace=SQL AREA(00)Type=CURSOR(00) Identifier=911793802 OwnerIdn=0
Identifier=911793802是v$sql.HASH_VALUE的值
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x9bf746c8(0, 3, 0, 0) Mutex=0x9bf74758(492, 58, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x9bf746a8[0x9bf746a8,0x9bf746a8]
Pin=0x9bf74688[0x9bf74688,0x9bf74688]
LoadLock=0x9bf74700[0x9bf74700,0x9bf74700]
Timestamp: Current=08-04-2016 13:55:02
HandleReference: Address=0x9bf747e0 Handle=(nil) Flags=[00]
ReferenceList:
Reference: Address=0x9e2857c0 Handle=0x9bf4e818 Flags=ROD[21]
LibraryObject: Address=0x9e2874b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000]PublicFlags=[0000]
DataBlocks: 這個(gè)datablocks指的是如下圖中的紅框
Block: #='0' name=KGLH0^3658de8a pins=0Change=NONE 這里的name=KGLH0^3658de8a指的是heap0
Heap=0x9becf350 Pointer=0x9e287550 Extent=0x9e287430 Flags=I/-/P/A/-/-
Heap=0x9becf350指的是父游標(biāo)的hd0，對(duì)應(yīng)x$kglob. KGLOBHD0
FreedLocation=0 Alloc=2.460938 Size=3.976562 LoadTime=39359924950
ChildTable: size='16'
Child: id='0' Table=0x9e288360 Reference=0x9e287db8Handle=0x9bed2700
Handle=0x9bed2700這個(gè)指的是子游標(biāo)handle對(duì)應(yīng)x$kglob. KGLHDADR
Children:
Child: childNum='0'
LibraryHandle: Address=0x9bed2700 Hash=0 LockMode=0 PinMode=0LoadLockMode=0 Status=VALD
Address=0x9bed2700這個(gè)指的是子游標(biāo)handle地址，對(duì)應(yīng)x$kglob. KGLHDADR
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1ActiveLocks=0 TotalLockCount=1 TotalPinCount=2
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x9bed27b0(0, 0, 0, 0) Mutex=0x9bf74758(492,58, 0, 6)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x9bed2790[0x9bed2790,0x9bed2790]
Pin=0x9bed2770[0x9bed2770,0x9bed2770]
LoadLock=0x9bed27e8[0x9bed27e8,0x9bed27e8]
ReferenceList:
Reference: Address=0x9e287db8 Handle=0x9bf74618 Flags=CHL[02]
LibraryObject: Address=0x9e2864b0 HeapMask=0000-0001-0001-0000Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
Dependencies: count='1' size='16' table='0x9e2872e8'
Dependency: num='0'
Reference=0x9e286a70 Position=20 Flags=DEP[0001]
Handle=0x9bf4e5f8 Type=TABLE(02) Parent=SCOTT.DEPT這個(gè)是引用的表名
Handle=0x9bf4e5f8這個(gè)是引用的對(duì)象的handle地址，Parent=SCOTT.DEPT這個(gè)是引用的表名
ReadOnlyDependencies: count='1' size='16'
ReadDependency: num='0' Table=0x9e287380 Reference=0x9e286968Handle=0x9bf4e818 Flags=DEP/ROD/KPP[61]
Accesses: count='1' size='16'
Dependency: num='0' Type=0009
DataBlocks:
Block: #='0' name=KGLH0^3658de8a pins=0 Change=NONE
Heap=0x9bed3100 Pointer=0x9e286550 Extent=0x9e286430 Flags=I/-/-/A/-/-
Heap=0x9bed3100這個(gè)是子游標(biāo)heap0的地址，對(duì)應(yīng)x$kglob. KGLOBHD0
FreedLocation=0 Alloc=2.078125 Size=3.937500 LoadTime=39359924950
Block: #='6' name=SQLA^3658de8a pins=0 Change=NONE
Heap=0x9e287b88 Pointer=0x9c183348 Extent=0x9c182708 Flags=I/-/-/A/-/E
Heap=0x9e287b88這個(gè)是子游標(biāo)heap6的地址，對(duì)應(yīng)x$kglob. KGLOBHD6
FreedLocation=0 Alloc=6.492188 Size=7.898438 LoadTime=0
NamespaceDump:
Child Cursor: Heap0=0x9e286550 Heap6=0x9c183348 Heap0 LoadTime=08-04-2016 13:55:02 Heap6 Load Time=08-04-2016 13:55:02
NamespaceDump:
ParentCursor: sql_id=f6hhpzwv5jrna parent=0x9e287550 maxchild=1 plk=n ppn=n
sql_id=f6hhpzwv5jrna這個(gè)是父游標(biāo)的sql_id，對(duì)應(yīng)v$sql. SQL_ID
Bucket: #=68887Mutex=0xa3bcd788(0, 23, 0, 6)
LibraryHandle: Address=0x9bf4e5f8 Hash=804f0d17 LockMode=0 PinMode=0LoadLockMode=0 Status=VALD
Address=0x9bf4e5f8這個(gè)是引用的對(duì)象的handle地址，可以看到后面的lockmode與pinmode
ObjectName: Name=SCOTT.DEPT 這個(gè)是對(duì)象的名稱(chēng)
FullHashValue=1383925607dd84fd07c34017804f0d17 Namespace=TABLE/PROCEDURE(01)Type=TABLE(02) Identifier=87106 OwnerIdn=83
Type=TABLE(02)對(duì)象是表，以02代表表 Identifier=87106這個(gè)是object_id OwnerIdn=83這個(gè)是user_id
SYS@moe SQL>selectobject_id from dba_objects where owner='SCOTT' and object_name='DEPT';
OBJECT_ID
----------
87106
SYS@moe SQL>selectuser_id from dba_users where username='SCOTT';
USER_ID
----------
83
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x9bf4e6a8(0, 1, 0, 0) Mutex=0x9bf4e738(492,47, 0, 6)
Flags=PIN/TIM/[00002801]
WaitersLists:
Lock=0x9bf4e688[0x9bf4e688,0x9bf4e688]
Pin=0x9bf4e668[0x9bf4e668,0x9bf4e668]
LoadLock=0x9bf4e6e0[0x9bf4e6e0,0x9bf4e6e0]
Timestamp: Current=08-24-2013 12:04:21
HandleReference: Address=0x9bf4e7b0 Handle=0x9bf386a0 Flags=OWN[200]
ReferenceList:
Reference: Address=0x9e286a70 Handle=0x9bed2700 Flags=DEP[01]
Timestamp=08-24-2013 12:04:21 InvalidatedFrom=0
LibraryObject: Address=0x9e2844b0 HeapMask=0000-0701-0701-0000Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
所以dump時(shí)，是把父游標(biāo)與子游標(biāo)dump在一起的，通過(guò)父游標(biāo)去定位它下面的所有子游標(biāo)，其中block:#=0是heap0，block:#=6是heap6
父游標(biāo)的dump中Name指向sql文本，而子游標(biāo)的Name指向SQL AREA(00) Type=CURSOR(00)
父游標(biāo)與子游標(biāo)都存在handle、heap0，但是只有子游標(biāo)有heap6
Lock與pin除了在sql或是游標(biāo)上，也發(fā)生面引用的對(duì)象上，在這里會(huì)把對(duì)象與user相關(guān)的東東體現(xiàn)一部分出來(lái)
其他的目前還沒(méi)看出來(lái)是什么，這個(gè)有點(diǎn)難猜，而且這方面的資料也不太好找，當(dāng)然有些可能是我猜錯(cuò)的，歡迎大家指出。
網(wǎng)站名稱(chēng)：如何簡(jiǎn)單閱讀librarycachedump
轉(zhuǎn)載注明：http://fisionsoft.com.cn/article/ieodos.html
