共享内存shared pool （6）：追踪sql语句
构建实验数据
--使用NC50用户查询（会话1） SQL> conn NC50/NC50 Connected. SQL> create table emp as select * from scott.emp; Table created. SQL> set line 160 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 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 10 14 rows selected. SQL>
--使用scott用户查询（会话1） SQL> conn scott/tiger Connected. SQL> set line 160 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 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 10 14 rows selected. SQL>
--视图V$SQLAREA是在父游标级别上统计的sql信息。是对v$sql的汇总表，进行了group by hash_value,sql_id的汇总。 SQL> select SQL_TEXT,VERSION_COUNT,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx') HEX,ADDRESS 2 from v$sqlarea 3 where sql_text like 'select * from emp%'; SQL_TEXT VERSION_COUNT HASH_VALUE HEX ADDRESS ------------------------------ ------------- ---------- ----------- -------- select * from emp 2 1745700775 680d47a7 38CD4848 SQL> --视图V$SQL是在子游标级别上列出了在共享sql区域的统计信息。 SQL> select sql_text,username,ADDRESS,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx') HEX_HASH_VALUE, CHILD_ADDRESS,CHILD_NUMBER,CHILD_LATCH 2 from v$sql a,dba_users b 3 where a.PARSING_USER_ID = b.user_id and sql_text like 'select * from emp%'; SQL_TEXT USERNAME ADDRESS HASH_VALUE HEX_HASH_VA CHILD_AD CHILD_NUMBER CHILD_LATCH ------------------------------ ------------------------------ -------- ---------- ----------- -------- ------------ ----------- select * from emp SCOTT 38CD4848 1745700775 680d47a7 33701E04 1 0 select * from emp NC50 38CD4848 1745700775 680d47a7 31CBF5F4 0 0 SQL>
--两条sql语句因为其代码完全相同,所以其ADDRESS,HASH_VALUE也完全相同. --这就意味着,这两条sql语句在shared pool中的存储位置是相同的(尽管其执行计划可能不同),代码得以共享 --两个查询来自不同的用户，所以会产生两个子游标 --Oracle将sql文本转换为相应的ASCII数值,然后根据数值通过Hash函数计算其HASH_VALUE --在shared pool中寻找是否存在相同的sql语句,如果存在,则进入下一步骤;如果不存在则尝试获取shared pool latch请求内存,存储该sql代码
--因为大小写字母的ascii值是不同的,所以Oracle会把大小写不同的代码作为不同的sql来处理,我们看一下测试: SQL> select * from scott.dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from scott.DEPT; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> col sql_text for a30 SQL> select sql_text,hash_value from v$sql where sql_text like 'select * from scott%'; SQL_TEXT HASH_VALUE ------------------------------ ---------- select * from scott.DEPT 3411718958 select * from scott.dept 911793802 SQL> --我们看到大小写的不同使得原本相同的sql语句变成了两条不同的代码
--查看当时的转储文件
SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 100';
Session altered.
SQL> select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/diag/rdbms/oracle/beijing/trace/beijing_ora_20326.trc
SQL>
Bucket: #=83879 Mutex=36319e9c(380000, 46, 0, 6)
LibraryHandle: Address=38cd4848 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=95
Statistics: InvalidationCount=0 ExecutionCount=4 LoadCount=3 ActiveLocks=0 TotalLockCount=4 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=3 HandleInUse=3
Concurrency: DependencyMutex=38cd48b0(0, 0, 0, 0) Mutex=38cd48fc(56, 74, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=38cd48a0[38cd48a0,38cd48a0]
Pin=38cd48a8[38cd4890,38cd4890]
Timestamp: Current=03-12-2015 22:53:56
LibraryObject: Address=3495c034 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=3495cfb0 Reference=3495c63c Handle=31cbf5f4
Child: id='1' Table=3495cfb0 Reference=3495c814 Handle=33701e04
Children:
Child: childNum='0'
LibraryHandle: Address=31cbf5f4 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=3 LoadCount=1 ActiveLocks=0 TotalLockCount=4 TotalPinCount=5
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=31cbf65c(0, 0, 0, 0) Mutex=38cd48fc(56, 74, 0, 6)
Flags=RON/PIN/PN0/EXP/[10012111]
WaitersLists:
Lock=31cbf64c[31cbf64c,31cbf64c]
Pin=31cbf654[31cbf63c,31cbf63c]
LibraryObject: Address=33d28034 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x33d280b8 Heap6=0x309e1598 Heap0 Load Time=03-12-2015 22:53:56 Heap6 Load Time=03-12-2015 22:53:56
Child: childNum='1'
LibraryHandle: Address=33701e04 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=0 TotalLockCount=3 TotalPinCount=4
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=33701e6c(0, 0, 0, 0) Mutex=38cd48fc(56, 74, 0, 6)
Flags=RON/PIN/PN0/EXP/[10012111]
WaitersLists:
Lock=33701e5c[33701e5c,33701e5c]
Pin=33701e64[33701e4c,33701e4c]
LibraryObject: Address=312b6034 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x312b60b8 Heap6=0x321b675c Heap0 Load Time=03-12-2015 22:55:07 Heap6 Load Time=03-12-2015 22:55:07
NamespaceDump:
Parent Cursor: sql_id=a2dk8bdn0ujx7 parent=0x3495c0b8 maxchild=2 plk=n ppn=n
Current Cursor Sharing Diagnostics Nodes:
Child Node: 1 ID=37 reason=Authorization Check failed(4) size=5x4
translation table position: 0
original handle: 952821476
temp handle: 838579372
schema: 95
synonym object number: 0
Child Node: 0 ID=37 reason=Authorization Check failed(4) size=5x4
translation table position: 0
original handle: 838579372
temp handle: 952821476
schema: 84
synonym object number: 0
DUMP SQL语句父子游标的堆0和堆6
SQL> col KGLNAOBJ format a20 SQL> select kglhdadr,kglhdpar,kglnaobj,KGLOBHS0,kglobhd0,KGLOBHS6,KGLOBHD6 2 from x$kglob 3 where kglnaobj like 'select * from emp%'; KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHS0 KGLOBHD0 KGLOBHS6 KGLOBHD6 -------- -------- -------------------- ---------- -------- ---------- -------- 31CBF5F4 38CD4848 select * from emp 4332 38CB0164 8136 3495C4E4 33701E04 38CD4848 select * from emp 4332 31D04C14 8136 3495C758 38CD4848 38CD4848 select * from emp 4464 38D9E3F4 0 00 SQL> --父游标LibraryHandle 地址KGLHDPAR：38CD4848 (KGLHDPAR = KGLHDADR) --子游标LibraryHandle 地址KGLHDADR：31CBF5F4 (KGLHDPAR != KGLHDADR) --子游标LibraryHandle 地址KGLHDADR：33701E04 (KGLHDPAR != KGLHDADR) --父游标堆0描述KGLOBHD0：38D9E3F4 --子游标堆0描述KGLOBHD0：38CB0164 --子游标堆6描述KGLOBHD6：3495C4E4 --子游标堆0描述KGLOBHD0：31D04C14 --子游标堆6描述KGLOBHD6：3495C758
父游标堆0(会话1)
alter session set events 'immediate trace name heapdump_addr level 2,addr 0x38D9E3F4';
select value from v$diag_info where name like 'De%';
******************************************************
HEAP DUMP heap name="PCUR^680d47a7" desc=0x38d9e3f4
extent sz=0xff4 alt=32767 het=28 rec=9 flg=2 opc=0
parent=0x200010b4 owner=0x38cd4848 nex=(nil) xsz=0xfe4 heap=(nil)
fl2=0x26, nex=(nil)
EXTENT 0 addr=0x3495c01c
Chunk 3495c024 sz= 2732 perm "perm " alo=2100
Dump of memory from 0x3495C024 to 0x3495CAD0
3495C020 40000AAD 00000000 00000000 [...@........]
3495C030 00000834 38CD4848 3495C5A0 00000000 [4...HH.8...4....]
子游标堆0(会话2)
alter session set events 'immediate trace name heapdump_addr level 2,addr 0x38CB0164';
select value from v$diag_info where name like 'De%';
******************************************************
HEAP DUMP heap name="CCUR^680d47a7" desc=0x38cb0164
extent sz=0xff4 alt=32767 het=28 rec=9 flg=2 opc=0
parent=0x200010b4 owner=0x31cbf5f4 nex=(nil) xsz=0xfe4 heap=(nil)
fl2=0x26, nex=(nil)
EXTENT 0 addr=0x33d2801c
Chunk 33d28024 sz= 2592 perm "perm " alo=1720
Dump of memory from 0x33D28024 to 0x33D28A44
33D28020 40000A21 00000000 00000000 [!..@........]
子游标堆6(会话3)
alter session set events 'immediate trace name heapdump_addr level 2,addr 0x3495C4E4';
select value from v$diag_info where name like 'De%';
******************************************************
HEAP DUMP heap name="SQLA^680d47a7" desc=0x3495c4e4
extent sz=0xff4 alt=32767 het=184 rec=0 flg=2 opc=2
parent=0x200010b4 owner=0x3495c424 nex=(nil) xsz=0xff4 heap=(nil)
fl2=0x27, nex=(nil)
EXTENT 0 addr=0x321b6964
Chunk 321b696c sz= 2064 free " "
Dump of memory from 0x321B696C to 0x321B717C
321B6960 C0000811 [....]
参考blog：
http://www.eygle.com/archives/2004/10/shared_pool-4.html
http://blog.csdn.net/haibusuanyun/article/details/17844211
