oracle sub share pool简介以及共享sql实现实质(七)

Accesses: count='1' size='16'

Dependency: num='1' Type=0009

Translations: count='1' size='16'

Translation: num='0' Original=0x6d688bb8 Final=0x6d688bb8

DataBlocks:

Block: #='0' name=KGLH0^680d47a7 pins=0 Change=NONE

Heap=0x6d774d00 Pointer=0x6cf1d150 Extent=0x6cf1d030 Flags=I/-/-/A/-/-

FreedLocation=0 Alloc=2.421875 Size=3.937500 LoadTime=4294578230

Block: #='6' name=SQLA^680d47a7 pins=0 Change=NONE

Heap=0x6add2b88 Pointer=0x6b2f46d0 Extent=0x6b2f3a90 Flags=I/-/-/A/-/E

FreedLocation=0 Alloc=9.132812 Size=11.859375 LoadTime=0

NamespaceDump:

Child Cursor: Heap0=0x6cf1d150 Heap6=0x6b2f46d0 Heap0 Load Time=10-22-2013 00:17:55 Heap6 Load Time=10-22-2013 00:17:55

Child: childNum='2'

LibraryHandle: Address=0x7d0899f8 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=INVL

Name: Namespace=SQL AREA(00) Type=CURSOR(00)

Statistics: InvalidationCount=1 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=2

Counters: BrokenCount=2 RevocablePointer=2 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0

Concurrency: DependencyMutex=0x7d089aa8(0, 1, 0, 0) Mutex=0x7d188468(1, 126, 0, 6)

Flags=RON/PIN/PN0/EXP/CHD/[10010111]

WaitersLists:

Lock=0x7d089a88[0x7d089a88,0x7d089a88]

Pin=0x7d089a68[0x7d089a68,0x7d089a68]

LoadLock=0x7d089ae0[0x7d089ae0,0x7d089ae0]

ReferenceList:

Reference: Address=0x6ad6bea8 Handle=0x7d188328 Flags=CHL[02]

ObjectFreed=last freed from LKDL addn data INV

NamespaceDump:

Child Cursor: Heap0=0xc0cc9e0 Heap6=0x7d0899f8 Heap0 Load Time=250-255-07-65 254:-1:-1 Heap6 Load Time=00-00--100-100 -1:-1:-1

NamespaceDump:

Parent Cursor: sql_id=a2dk8bdn0ujx7 parent=0x6add2150 maxchild=3 plk=n ppn=n

CursorDiagnosticsNodes:

ChildNode: ChildNumber=0 ID=37 reason=Authorization Check failed(4) size=5x4 translation_table_position=0 original_handle=1835568056 temp_handle=1836716936 schema=92 synonym_object_number=0

AgedOutCursorDiagnosticNodes:

ChildNode: ChildNumber=1 ID=37 reason=Authorization Check failed(4) size=5x4 translation_table_position=0 original_handle=1835019120 temp_handle=1835568056 schema=0 synonym_object_number=0

ChildNode: ChildNumber=0 ID=37 reason=Authorization Check failed(4) size=5x4 translation_table_position=0 original_handle=1835568056 temp_handle=1836716936 schema=92 synonym_object_number=0

通过v$sql中的hash_value找到该sql在library cache中的hash value(sql在解析的时候会把sql转换成ascii，然后再按照ascii进行hash计算得到的hash value）。然后我们可以知道hash值就是我们查看的v$sqlarea值680d47a7，状态为vald（有效的），对象是执行了select * from emp 语句，在该 library handle 中有namespace 为sql area，类型为cursor，标示符为1745700775正式我们在v$sqlarea查看的hash_value,并且还有一个owneridn号为83，那么通过这个号，可以知道该用户为：scott，正是第一次进行sql执行的用户。

SQL> select username,user_id from dba_users where user_id=83;

USERNAME USER_ID

------------------------------ ----------

SCOTT