我们进一步来讨论一下shared pool的处理:
先进行相应查询，获得测试数据:
[oracle@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Thu Aug 26 10:21:54 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production SQL> connect eygle/eygle Connected. SQL> create table emp as select * from scott.emp; Table created. SQL> SQL> connect / as sysdba Connected. SQL> startup force; ORACLE instance started. Total System Global Area 47256168 bytes Fixed Size 451176 bytes Variable Size 29360128 bytes Database Buffers 16777216 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> set linesize 120 SQL> connect scott/tiger Connected. SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 8888 EYGLE MANAGER 11-AUG-04 9999 10 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 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 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 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 11 rows selected. SQL> connect eygle/eygle Connected. SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 8888 EYGLE MANAGER 11-AUG-04 9999 10 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 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 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 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 11 rows selected. SQL> select SQL_TEXT,VERSION_COUNT,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx') HEX,ADDRESS 2 from v$sqlarea where sql_text like 'select * from emp%'; SQL_TEXT VERSION_COUNT HASH_VALUE HEX ADDRESS -------------------- ------------- ---------- ----------- -------- select * from emp 2 2648707557 9de011e5 52D9EA28 SQL> select sql_text,username,ADDRESS,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx') HEX_HASH_VALUE,CHILD_NUMBER,CHILD_LATCH 2 from v$sql a,dba_users b 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_NUMBER CHILD_LATCH -------------------- ------------------------------ -------- ---------- ----------- ------------ ----------- select * from emp SCOTT 52D9EA28 2648707557 9de011e5 0 1 select * from emp EYGLE 52D9EA28 2648707557 9de011e5 1 1 注意:这里我们可以看出v$sqlarea和v$sql两个视图的不同之处 v$sql中为每一条sql保留一个条目，而v$sqlarea中根据sql_text进行group by,通过version_count计算子指针的个数. 我们注意到,这两条sql语句因为其代码完全相同,所以其ADDRESS,HASH_VALUE也完全相同. 这就意味着,这两条sql语句在shared pool中的存储位置是相同的(尽管其执行计划可能不同),代码得以共享. 在此过程中Oracle完成sql解析的第一个步骤:语法解析 Oracle进行语法检查时遵循自右向左,自下向上的原则,如果发现语法错误就马上返回错误. 语法检查通过以后,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 4096614922 select * from scott.dept 2089404358 我们注意到,仅只大小写的不同使得原本相同的sql语句变成了两条"不同的代码" 所以从这里我们可以看出,sql的规范编写非常重要. 完成这一个步骤以后,Oracle需要进行的是语义分析: 在此步骤中,Oracle需要验证对象是否存在,相关用户是否具有权限,引用的是否是相同的对象... 对于我们第一个查询,实际上emp表来自不同的用户,那么sql的执行计划也就不同了 当然影响sql执行计划的因素还有很多,包括优化器模式等 SQL> select a.*,to_char(to_hash,'xxxxxxxxxx') Hex_HASH_VALUE 2 from V$OBJECT_DEPENDENCY a where to_name='EMP'; FROM_ADD FROM_HASH TO_OWNER TO_NAME TO_ADDRE TO_HASH TO_TYPE HEX_HASH_VA -------- ---------- -------------------- -------------------- -------- ---------- ---------- ----------- 52D9EA28 2648707557 SCOTT EMP 52D9DEBC 828228010 2 315dc1aa 52D9EA28 2648707557 EYGLE EMP 52D82E58 1930491453 2 7310f63d 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> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 100'; Session altered. |
此处在不同级别对shared pool进行转储.
在转向trace文件之前,我们通过下图来看一下Library CACHE的结构:
<!--[if !vml]-->
<!--[endif]-->
Library Cache Handle指向Library Cache Object,含对象名,namespace,时间戳,引用列表,锁定对象及pin对象列表等.
我们从dump文件中看看具体的信息,由以上v$sql视图我们得到以上查询的hash_value为9de011e5，ADDRESS为52D9EA28
在bucket 4851中,我们找到了select * from emp 这条sql语句.
BUCKET 4581: LIBRARY OBJECT HANDLE: handle=52d9ea28 name=select * from emp hash=9de011e5 timestamp=08-26-2004 10:24:43
==>这个hash正是v$sql中该sql语句的hash_value值
namespace=CRSR flags=RON/TIM/PN0/SML/[12010000] kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=1 lwt=0x52d9ea40[0x52d9ea40,0x52d9ea40] ltm=0x52d9ea48[0x52d9ea48,0x52d9ea48] pwt=0x52d9ea58[0x52d9ea58,0x52d9ea58] ptm=0x52d9eab0[0x52d9eab0,0x52d9eab0] ref=0x52d9ea30[0x52d9ea30, 0x52d9ea30] lnd=0x52d9eabc[0x52d9eabc,0x52d9eabc] LIBRARY OBJECT: object=52d9e7b0 type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0 CHILDREN: size=16 child# table reference handle ------ -------- --------- -------- 0 52d9e96c 52d9e6cc 52d9e4ac 1 52d9e96c 52d9e70c 52d885cc ==>这就是我们前边提到过的子指针,每个都指向了一个不同的handle
DATA BLOCKS: data# heap pointer status pins change alloc(K) size(K) ----- -------- -------- ------ ---- ------ -------- -------- 0 52d9e9b8 52d9e838 I/-/A 0 NONE 0.86 1.09
==>此处的heap就是指内存地址. ==>这里存放的就是SQL代码及用户连接信息
HEAP DUMP OF DATA BLOCK 0: ****************************************************** HEAP DUMP heap name="library cache" desc=0x52d9e9b8 extent sz=0x224 alt=32767 het=16 rec=9 flg=2 opc=0 parent=0x5000002c owner=0x52d9e7b0 nex=(nil) xsz=0x224 EXTENT 0 addr=0x52d9e558 Chunk 52d9e560 sz= 540 perm "perm " alo=448 52D9E560 5000021D 00000000 52D9E7A0 000001C0 [...P.......R....] 52D9E570 52D9E704 52D9E660 00020002 52D9E57C [...R`..R....|..R] 52D9E580 52D9E57C 00000000 52D9E588 52D9E588 [|..R.......R...R] 52D9E590 00000000 52D9E594 52D9E594 00000000 [.......R...R....] 52D9E5A0 52D9E5A0 52D9E5A0 00000000 52D9E5AC [...R...R.......R] 52D9E5B0 52D9E5AC 00000000 52D9E5B8 52D9E5B8 [...R.......R...R] ……. 52D9E8D0 52D9E630 00000000 52D9E570 00000000 [0..R....p..R....] 52D9E8E0 00000000 00000000 00000000 00000000 [................] 52D9E8F0 00000000 00000000 00000002 0000000D [................] 52D9E900 00000001 00000000 [........] Permanent space = 900 ****************************************************** BUCKET 4581 total object count=1
我们以handle:52d885cc为例看一下Library Cache Object的结构: ****************************************************** LIBRARY OBJECT HANDLE: handle=52d885cc namespace=CRSR flags=RON/KGHP/PN0/[10010000] kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=1 lwt=0x52d885e4[0x52d885e4,0x52d885e4] ltm=0x52d885ec[0x52d885ec,0x52d885ec] pwt=0x52d885fc[0x52d885fc,0x52d885fc] ptm=0x52d88654[0x52d88654,0x52d88654] ref=0x52d885d4[0x52d9e70c, 0x52d9e70c] lnd=0x52d88660[0x52d88660,0x52d88660] LIBRARY OBJECT: object=52d82a24 type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0 DEPENDENCIES: count=1 size=16 dependency# table reference handle position flags ----------- -------- --------- -------- -------- ------------------- 0 52d82be0 52d82b20 52d82e58 14 DEP[01]
==>在dependency部分我们看到,这个cursor依赖的对象handle: 52d82e58 ==>这个handle指向的就是EYGLE.EMP表，如果以上两个CRSR访问的是同一个对象, ==>那么这两个SQL才会是真的共享.这里我们的sql虽然是相同的，访问的却是不同用户 ==>的数据表, 子指针的概念就出来了. ==>在这里我们看到52d82e58指向的是EYGLE.EMP这个对象,也就是EYGLE所查询的数据表. ACCESSES: count=1 size=16 dependency# types ----------- ----- 0 0009 TRANSLATIONS: count=1 size=16 original final -------- -------- 52d82e58 52d82e58 DATA BLOCKS: data# heap pointer status pins change alloc(K) size(K) ----- -------- -------- ------ ---- ------ -------- -------- 0 52d8c244 52d827e4 I/-/A 0 NONE 1.09 1.64 6 52d82ac0 52d817c4 I/-/A 0 NONE 3.70 3.73
==>这里的Data Blocks是个重要的部分 ==>每个控制块包含一个heap descriptor,指向相应的heap memory,这个heap memory ==>包含的就是Diana Tree,P-Code,Source Code,Shared Cursor Context area等重要==>数据,也就是我们通常。所说的，解析过的SQL及执行计划树,真正到这里以后,sql才得==>以共享.也就真正的避免了硬解析
HEAP DUMP OF DATA BLOCK 0: ****************************************************** HEAP DUMP heap name="library cache" desc=0x52d8c244 extent sz=0x224 alt=32767 het=16 rec=9 flg=2 opc=0 parent=0x5000002c owner=0x52d82a24 nex=(nil) xsz=0x224 EXTENT 0 addr=0x52d80ff0 Chunk 52d80ff8 sz= 464 free " " 52D80FF0 C00001D1 00000000 [........] 52D81000 52D8C28C 52D8C28C 00000000 00000000 [...R...R........] 52D81010 00000000 00000000 00000000 00000000 [................] Repeat 26 times 52D811C0 00000000 00000000 [........] Chunk 52d811c8 sz= 76 freeable "kgltbtab " 52D811C0 1000004D 52D80FF8 [M......R] 52D811D0 0A857928 52D82B68 00000000 00000000 [(y..h+.R........] 52D811E0 00000000 00000000 00000000 00000000 [................] Repeat 2 times 52D81210 00000000 [....] EXTENT 1 addr=0x52d827cc Chunk 52d827d4 sz= 540 perm "perm " alo=532 52D827D0 5000021D 00000000 52D82A14 [...P.....*.R] 52D827E0 00000214 00000000 0000001A 00000069 [............i...] ……………. 52D825D0 00000000 00000000 00000000 52D81ACF [...............R] 52D825E0 00000000 00000000 00000000 00000000 [................] Repeat 3 times 52D82620 52D8263C 00000010 000006D4 00000010 [ 52D82630 000006F4 00000010 000006F8 00000010 [................] 52D82640 000006FC 00000010 00000714 00000010 [................] 52D82650 00000720 00000010 0000072C 00000010 [ .......,.......] 52D82660 00000744 00000010 0000074C 00000010 [D.......L.......] 52D82670 00000764 00000010 0000077C [d.......|...] Permanent space = 3784 MARKS: Mark 0x52d8237c ****************************************************** 这里的handle=52d82e58就是sql依赖的对象信息: BUCKET 63037: LIBRARY OBJECT HANDLE: handle=52d82e58 name=EYGLE.EMP hash=7310f63d timestamp=08-26-2004 10:23:40 namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000] kkkk-dddd-llll=0000-0501-0501 lock=0 pin=0 latch#=1 lwt=0x52d82e70[0x52d82e70,0x52d82e70] ltm=0x52d82e78[0x52d82e78,0x52d82e78] pwt=0x52d82e88[0x52d82e88,0x52d82e88] ptm=0x52d82ee0[0x52d82ee0,0x52d82ee0] ref=0x52d82e60[0x52d82e60, 0x52d82e60] lnd=0x52d82eec[0x52d7dcf0,0x52d89fc8] LIBRARY OBJECT: object=52d81594 type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 ==>Type:对象类型,这里是一张表 ==>flags:代表对象状态 DATA BLOCKS: data# heap pointer status pins change alloc(K) size(K) ----- -------- -------- ------ ---- ------ -------- -------- 0 52d8c1e4 52d8161c I/-/A 0 NONE 0.66 1.09
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9417382/viewspace-937428/，如需转载，请注明出处，否则将追究法律责任。 
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%> <%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%}%>
<%=items[i].items.items[j].createtime%>
回复
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备09055130号-4 北京市公安局海淀分局网监中心备案编号：11010802021510 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员 |
转载于:http://blog.itpub.net/9417382/viewspace-937428/
983
到【灌水乐园】发言
