oracle sub share pool简介以及共享sql实现实质(三)
RESS
-------------------------------------------------- ------------- ---------- ------------- ----------------
select * from emp 3 1745700775 680d47a7 000000007D188328
SQL>
SQL> select sql_text,username,address,hash_value,to_char(hash_value,'xxxxxxxxxx') hex_hash_value,child_number,child_latch from v$sql a,dba_users b
where a.parsing_user_id=b.user_id and sql_text like 'select * from emp%';
2
SQL_TEXT USERNAME ADDRESS HASH_VALUE HEX_HASH_VA CHILD_NUMBER CHILD_LATCH
-------------------------------------------------- ------------------------------ ---------------- ---------- ----------- ------------ -----------
select * from emp RHYS 000000007D188328 1745700775 680d47a7 1 0
select * from emp SCOTT 000000007D188328 1745700775 680d47a7 0 0
对library cache进行转储；
SQL> alter session set events 'immediate trace name library_cache level 1';
Session altered.
SQL> alter session set events 'immediate trace name library_cache level 2';
Session altered.
SQL> alter session set events 'immediate trace name library_cache level 4';
Session altered.
SQL> alter session set events 'immediate trace name library_cache level 8';
Session altered.
SQL> alter session set events 'immediate trace name library_cache level 16';
Session altered.
SQL> alter session set events 'immediate trace name library_cache level 32';
Session altered.
SQL>
获得跟踪文件：
[sql]
SQL> @trace.sql
TRACE_FILE_NAME
----------------------------------------------------------------------
/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_1864.trc
SQL>
通过v$sql中的hash_value值找到对应的bucket；
Bucket: #=83879 Mutex=0x798c4248(0, 32, 0, 6)
LibraryHandle: Address=0x7d188328 Hash=680d47a7 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select * from emp
FullHashValue=552d9e82bf86a695a136485b680d47a7 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1745700775 OwnerIdn=83
Statistics: InvalidationCount=1 ExecutionCount=4 LoadCount=6 ActiveLocks=0 TotalLockCount=5 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=4 HandleInUse=4 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7d1883d8(0, 6, 0, 0) Mutex=0x7d188468(1, 124, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x7d1883b8[0x7d1883b8,0x7d1883b8]
Pin=0x7d188398[0x7d188398,0x7d188398]
LoadLock=0x7d188410[0x7d188410,0x7d188410]
Timestamp: Current=10-22-2013 00:12:23
HandleReference: Address=0x7d1884e8 Handle=(nil) Flags=[00]
LibraryObject: Address=0x6add20b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=0x6add2f60 Reference=0x6add29a0 Handle=0x6d6a4108
Child: id='1' Table=0x6add2f60 Reference=0x6add2ce8 Handle=0x6d5f4820
Child: id='2' Table=0x6add2f60 Reference=0x6ad6bea8 Handle=0x7d0899f8
NamespaceDump:
Parent Cursor: