oracle sub share pool简介以及 共享sql实现实质。
今天是2013-10-21，从上周五到现在已经三天没有研究oracle，感觉少了很多东西。现在开始继续挖掘oracle的内容
。
我们知道在9i开始shared pool已经增加了sub shared pool，我们可以通过转储shared pool来查看，并且到了10g每
个sub shared pool被划分了四个分区，同样通过转储的信息可以 看到sga heap（1,0），sga heap（1，1），sga
heap(1,2) ,sga heap(1,3)。
另外对于sub shared pool最多有7个，如果cpu个数大于4，且shared pool size 大于250M（10g sub shared pool至
少为256M，11g为512mb）。
该值受隐含参数控制：
可以看到我主机cpu个数为32颗，shared pool size为：2G那么就启用了sub shared pool功能。
23:03:00 [email protected]>set linesize 20023:03:03 [email protected]>@getsp.sql???? par ????: kgh???? 2: where a.indx=b.indx and a.ksppinm like '%&par%'???? 2: where a.indx=b.indx and a.ksppinm like '%kgh%'KSPPINM KSPPSTVL KSPPDESC-------------------------------------------------- -------------------- ------------------------------------------------------------_kghdsidx_count 7 max kghdsidx count23:03:09 [email protected]>show parameter shared_pool_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------shared_pool_size big integer 2G23:04:08 [email protected]>
每个sub sharedpool 都有单独的shared pool latch进行管理：
23:07:57 [email protected]>select addr,latch#,name,gets,misses,sleeps,spin_gets,immediate_gets,immediate_misses from v$latch_children where name='shared pool';ADDR LATCH# NAME GETS MISSES SLEEPS SPIN_GETS IMMEDIATE_GETS IMMEDIATE_MISSES---------------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- -------------- ----------------C000000048EE76B0 216 shared pool 59703790 8097 1028 7091 0 0C000000048EE7610 216 shared pool 74572270 11489 1743 9799 0 0C000000048EE7570 216 shared pool 85353572 16887 2974 14033 0 0C000000048EE74D0 216 shared pool 88043530 18467 3128 15470 0 0C000000048EE7430 216 shared pool 66513683 10129 1126 9036 0 0C000000048EE7390 216 shared pool 84015797 13276 1788 11553 0 0C000000048EE72F0 216 shared pool 578252857 36040 5139 31168 0 0已选择7行。23:08:42 [email protected]>
另外对于每个sub spool的使用情况也看查看x$kghlu；
对于library cache的转储有如下等级：
level 1，转储library cache 统计信息
level 2，转储hash table 概要
level 4，转储library cache 对象，只包含基本信息
level 8，转储library cache 对象，包含详细信息（如 child references，pin waiters等）
level 16 增加heap sizes 信息
level 32 增加heap信息：
（如上等级转载是eygle的blog）
摘自网络一张shared pool中library cache 图，如下：
对于library cache 有很多hash buckets组成，然后每个hash buckets又有library cache handel（指向library cache object的指针，namespace等信息），library cache object的heap 0为控制信息。
如下所示sql共享的过程：
首先在不同用户下执行sql
SQL> startup forceORACLE instance started.
Total System Global Area 492707840 bytesFixed Size 2254544 bytesVariable Size 339740976 bytesDatabase Buffers 146800640 bytesRedo Buffers 3911680 bytesDatabase mounted.Database opened.
SQL> conn scott/rootConnected.SQL> set linesize 200SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> conn rhys/amyConnected.SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7935 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL>
用户分别为scott以及rhys，但是他们都有一个emp的表。
然后查看在shared pool中存储的sql如下：
SQL> set linesize 200SQL> col sql_text for a50SQL> select sql_text,version_count,hash_value,to_char(hash_value,'xxxxxxxxxxxx')hex,address from v$sqlarea where sql_text like 'select * from emp%';SQL_TEXT VERSION_COUNT HASH_VALUE HEX ADDRESS-------------------------------------------------- ------------- ---------- ------------- ----------------select * from emp 3 1745700775 680d47a7 000000007D188328SQL> 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 bwhere 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 0select * 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> @trace.sqlTRACE_FILE_NAME----------------------------------------------------------------------/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_1864.trcSQL>
通过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=1745700775OwnerIdn=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: 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
Bucket: #=83879 Mutex=0x798c4248(0, 34, 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, 7, 0, 0) Mutex=0x7d188468(1, 126, 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]
ReferenceList:
Reference: Address=0x6acfef80 Handle=0x6d7b9910 Flags=ROD[21]
Reference: Address=0x6ada5f80 Handle=0x6d687200 Flags=ROD[21]
LibraryObject: Address=0x6add20b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^680d47a7 pins=0 Change=NONE
Heap=0x6d7b0b68 Pointer=0x6add2150 Extent=0x6add2030 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=4.109375 Size=7.953125 LoadTime=4294245460
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
Children:
Child: childNum='0'
LibraryHandle: Address=0x6d6a4108 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=5 TotalPinCount=7
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x6d6a41b8(0, 1, 0, 0) Mutex=0x7d188468(1, 126, 0, 6)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x6d6a4198[0x6d6a4198,0x6d6a4198]
Pin=0x6d6a4178[0x6d6a4178,0x6d6a4178]
LoadLock=0x6d6a41f0[0x6d6a41f0,0x6d6a41f0]
ReferenceList:
Reference: Address=0x6add29a0 Handle=0x7d188328 Flags=CHL[02]
LibraryObject: Address=0x6af290b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
Dependencies: count='2' size='16' table='0x6af29ee8'
Dependency: num='0'
Reference=0x6af29668 Position=0 Flags=DEP[0001]
Handle=0x6d688488 Type=NONE(255) Parent=SCOTT
Dependency: num='1'
Reference=0x6af29708 Position=14 Flags=DEP[0001]
Handle=0x6d7a1388 Type=TABLE(02) Parent=SCOTT.EMP
ReadOnlyDependencies: count='1' size='16'
ReadDependency: num='0' Table=0x6af29f80 Reference=0x6af29568 Handle=0x6d687200 Flags=DEP/ROD/KPP[61]
Accesses: count='1' size='16'
Dependency: num='1' Type=0009
Translations: count='1' size='16'
Translation: num='0' Original=0x6d7a1388 Final=0x6d7a1388
DataBlocks:
Block: #='0' name=KGLH0^680d47a7 pins=0 Change=NONE
Heap=0x6d797f20 Pointer=0x6af29150 Extent=0x6af29030 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=2.421875 Size=3.937500 LoadTime=4294571240
Block: #='6' name=SQLA^680d47a7 pins=0 Change=NONE
Heap=0x6add2770 Pointer=0x6b0bcbd0 Extent=0x6b0bbf90 Flags=I/-/-/A/-/E
FreedLocation=0 Alloc=9.132812 Size=11.859375 LoadTime=0
NamespaceDump:
Child Cursor: Heap0=0x6af29150 Heap6=0x6b0bcbd0 Heap0 Load Time=10-22-2013 00:17:48 Heap6 Load Time=10-22-2013 00:17:48
Child: childNum='1'
LibraryHandle: Address=0x6d5f4820 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=3 TotalPinCount=5
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x6d5f48d0(0, 1, 0, 0) Mutex=0x7d188468(1, 126, 0, 6)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x6d5f48b0[0x6d5f48b0,0x6d5f48b0]
Pin=0x6d5f4890[0x6d5f4890,0x6d5f4890]
LoadLock=0x6d5f4908[0x6d5f4908,0x6d5f4908]
ReferenceList:
Reference: Address=0x6add2ce8 Handle=0x7d188328 Flags=CHL[02]
LibraryObject: Address=0x6cf1d0b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
Dependencies: count='2' size='16' table='0x6cf1dee8'
Dependency: num='0'
Reference=0x6cf1d668 Position=0 Flags=DEP[0001]
Handle=0x6d7f37d0 Type=NONE(255) Parent=RHYS
Dependency: num='1'
Reference=0x6cf1d708 Position=14 Flags=DEP[0001]
Handle=0x6d688bb8 Type=TABLE(02) Parent=RHYS.EMP
ReadOnlyDependencies: count='1' size='16'
ReadDependency: num='0' Table=0x6cf1df80 Reference=0x6cf1d568 Handle=0x6d7b9910 Flags=DEP/ROD/KPP[61]
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 83
SQL>
然后看下一个更加详细的内容：
library handle 可以看到Hash=680d47a7 ，ObjectName: Name=select * from emp 用户为83（scott）和第一个转储的一样的信息，然后继续往下看，当我们看到referencelist的时候正式有个handle概要信息，指向了不同的library cache object 的handle，；
Reference: Address=0x6acfef80 Handle=0x6d7b9910 Flags=ROD[21]
Reference: Address=0x6ada5f80 Handle=0x6d687200 Flags=ROD[21]
首先看：Child: childNum='0'
根据以上信息继续查看library cache object内容：
Dependencies: count='2' size='16' table='0x6af29ee8'
Dependency: num='0'
Reference=0x6af29668 Position=0 Flags=DEP[0001]
Handle=0x6d688488 Type=NONE(255) Parent=SCOTT
Dependency: num='1'
Reference=0x6af29708 Position=14 Flags=DEP[0001]
Handle=0x6d7a1388 Type=TABLE(02) Parent=SCOTT.EMP
ReadOnlyDependencies: count='1' size='16'
ReadDependency: num='0' Table=0x6af29f80 Reference=0x6af29568 Handle=0x6d687200 Flags=DEP/ROD/KPP[61]
注意这里的Handle=0x6d687200正是依赖的library cache handle中的Reference: Address=0x6ada5f80 Handle=0x6d687200 Flags=ROD[21] 。
在看下Child: childNum='1' ：
Dependencies: count='2' size='16' table='0x6cf1dee8'
Dependency: num='0'
Reference=0x6cf1d668 Position=0 Flags=DEP[0001]
Handle=0x6d7f37d0 Type=NONE(255) Parent=RHYS
Dependency: num='1'
Reference=0x6cf1d708 Position=14 Flags=DEP[0001]
Handle=0x6d688bb8 Type=TABLE(02) Parent=RHYS.EMP
ReadOnlyDependencies: count='1' size='16'
ReadDependency: num='0' Table=0x6cf1df80 Reference=0x6cf1d568 Handle=0x6d7b9910 Flags=DEP/ROD/KPP[61]
可以看到同样Handle=0x6d7b9910指向了reference信息中的handle。
Child: childNum='2' 到这里才是正在不需要进行sql共享的根源。