Shulou(Shulou.com)06/01 Report--

Originated from the systemdump analysis of the example, now take out the part of library cache dump and make a simple guess.

SYS@moe SQL > col KGLNAOBJ for a40SYS@moe SQL > set line 123SYS@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 000000009E287B88000000009BF74618 000000009BF74618 select * fromscott.dept 4720 000000009BECF350 000

KGLHDADR is the address of librarycache handle

KGLHDPAR is the parent address

KGLNAOBJ is librarycache object

KGLOBHD0 is the heap0 address

KGLOBHD6 is the heap6 address

SYS@moe SQL > select KSMCHPTR,KSMCHCOM,KSMCHCLS,KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009BECF350'; parent cursor hd0 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ- 000000009E287400 KGLH0 ^ 3658de8a recr 4096

KSMCHPTR is the chunk address

KSMCHPAR is the address of the heap queried in x$kglob

SYS@moe SQL > selectKSMCHPTR,KSMCHCOM,KSMCHCLS, KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009BED3100'; subcursor hd0 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ- 000000009E286400 KGLH0 ^ 3658de8a recr 4096 SYS@moe SQL > selectKSMCHPTR,KSMCHCOM,KSMCHCLS, KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009E287B88' Sub-cursor hd6 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ- 000000009C1826D8SQLA ^ 3658de8a recr 4096000000009C17E6D8SQL ^ 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_TEXTf6hhpzwv5jrna 911793802000000009BF74618 000000009BED2700select * from scott.dept SYS@moe SQL > alter session set events'immediate trace name library_cache level 16; Session altered.

Here is the trc section:

Bucket: # = 56970 Mutex=0xa3b59180 (0,23,0pd6)

LibraryHandle: Address=0x9bf74618 Hash=3658de8a LockMode=0 PinMode=0 LoadLockMode=0Status=VALD

The value of Address=0x9bf74618 is the value of x$kglob.KGLHDPAR

Hash=3658de8a this value is the value of x$ksmsp.KSMCHCOM, converted to the decimal system is 911793802, is v$sql. The value of HASH_VALUE

SYS@moe SQL > selectto_number ('3658de8a') from dual

TO_NUMBER ('3658DE8AAZ')

-

911793802

ObjectName: Name=select * from scott.dept here is the statement executed, which is the value of x$kglob.KGLNAOBJ, which is also v$sql. The value of SQL_TEXT

FullHashValue=66438da3ebbc48a3e34215ff3658de8a Namespace=SQL AREA (00) Type=CURSOR (00) Identifier=911793802 OwnerIdn=0

Identifier=911793802 is the value of 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: this datablocks refers to the red box in the picture below.

Block: # ='0' name= KGLH0 ^ 3658de8a pins=0Change=NONE name= KGLH0 ^ 3658de8a here refers to heap0

Heap=0x9becf350 Pointer=0x9e287550 Extent=0x9e287430 Flags=I/-/P/A/-/-

Heap=0x9becf350 refers to the hd0 of the parent cursor, corresponding to 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 refers to the subcursor handle corresponding to x$kglob. KGLHDADR

Children:

Child: childNum='0'

LibraryHandle: Address=0x9bed2700 Hash=0 LockMode=0 PinMode=0LoadLockMode=0 Status=VALD

Address=0x9bed2700 refers to the handle address of the subcursor, which corresponds to 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 this is the referenced table name

Handle=0x9bf4e5f8 this is the handle address of the referenced object, and Parent=SCOTT.DEPT this is the referenced table name.

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 this is the address of the subcursor heap0, corresponding to x$kglob. KGLOBHD0

FreedLocation=0 Alloc=2.078125 Size=3.937500 LoadTime=39359924950

Block: # ='6' name= SQL ^ 3658de8a pins=0 Change=NONE

Heap=0x9e287b88 Pointer=0x9c183348 Extent=0x9c182708 Flags=I/-/-/A/-/E

Heap=0x9e287b88 this is the address of the subcursor heap6, corresponding to 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 LoadTime=08-04-2016 13:55:02

NamespaceDump:

ParentCursor: sql_id=f6hhpzwv5jrna parent=0x9e287550 maxchild=1 plk=n ppn=n

Sql_id=f6hhpzwv5jrna this is the sql_id of the parent cursor, corresponding to 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 this is the handle address of the referenced object. You can see the following lockmode and pinmode

ObjectName: Name=SCOTT.DEPT this is the name of the object

FullHashValue=1383925607dd84fd07c34017804f0d17 Namespace=TABLE/PROCEDURE (01) Type=TABLE (02) Identifier=87106 OwnerIdn=83

The object of Type=TABLE (02) is the table, which represents the table Identifier=87106 by 02. This is object_id OwnerIdn=83 and this is 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

-

eighty-three

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]

So when you dump, you combine the parent cursor with the child cursor dump, and use the parent cursor to locate all the child cursors below it, where block:#=0 is heap0,block:#=6 and heap6.

The Name in the dump of the parent cursor points to the sql text, while the Name of the child cursor points to SQL AREA (00) Type=CURSOR (00)

Both parent and child cursors have handle and heap0, but only child cursors have heap6

Lock and pin are not only on sql or cursors, but also on objects referenced by facets. Here, some of the objects related to user will be reflected.

At present, I haven't seen what it is. This is a bit difficult to guess, and the information in this area is not easy to find. Of course, some of them may be wrong. Welcome to point out.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.