[20160215]超长sql语句与父子光标.txt
--看<oracle内核技术揭秘>提到sql语句不会进入保留池, 要进入保留池，chunk的大小必须大于_shared_pool_reserved_min_alloc。
--而实际上许多sql语句一般最大4096字节。而且这些内存分配的原则是首先从共享池分配，不行并且大于
--shared_pool_reserved_min_alloc才会从保留池申请。
--作者视乎忘记一种特殊情况就是超长sql语句，这样父游标保存sql的语句chunk可以大于4096字节。自己测试这种情况看看。
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> @ &r/hide _shared_pool_reserved_min_alloc
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------
_shared_pool_reserved_min_alloc minimum allocation size in bytes for reserved area of shared pool TRUE 4400 4400
2.建立一个sql语句脚本：
select /*+
zzzzzzzzzzzzz
....
zzzzz
*/ * from dept where deptno=10;
$ wc aa.sql
70 76 68043 aa.sql
--大小68043字节。
SCOTT@book> @aa.sql
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--查询获得该语句的sql_id=4bm0a97bq61za.
3.查询sql的内存结构：
# cat /home/oracle11g/sqllaji/sharepool/shp4.sql
column N0_6_16 format 9999999
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1';
SYS@book> @ &r/sharepool/shp4 4bm0a97bq61za
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 0000000061BE43F8 0000000063ACE5C0 select /*+ zzzzzzzzzzzzzzzzzzzzzzzzzzzzz 000000007C0CE810 000000006473F770 4488 12144 71074 87706 87706 3613591530 4bm0a97bq61za
父游标句柄地址 0000000063ACE5C0 0000000063ACE5C0 select /*+ zzzzzzzzzzzzzzzzzzzzzzzzzzzzz 000000007C0CE900 00 72736 0 0 72736 72736 3613591530 4bm0a97bq61za
*/
--其他结构应该跟以前的测试一样，可以参考我以前的blog。http://blog.itpub.net/267265/viewspace-1966412/
old 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000063ACE5C0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F50B1279228 24728 1 1 2 KGLHD 0000000063ACE590 68576 recr 80 00
--可以发现KSMCHSIZ=68576.chunk大小68576,而我的sql语句文本长度68043。也就说明sql语句如果sql语句超长有可能分配很大的chunk
--(大于4096).
3.另外建立sql脚本：
--内容与前面相似。
$ wc bb.sql
1170 1176 1168043 bb.sql
--执行后获得sql_id=ags54g605qs0f.
SYS@book> @ &r/sharepool/shp4 ags54g605qs0f
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 000000007C27BD28 0000000063983060 select /*+ zzzzzzzzzzzzzzzzzzzzzzzzzzzzz 0000000061BCC2F8 00000000646A2770 4504 12144 1171074 1187722 1187722 2153472014 ags54g605qs0f
父游标句柄地址 0000000063983060 0000000063983060 select /*+ zzzzzzzzzzzzzzzzzzzzzzzzzzzzz 000000007C27C100 00 1172736 0 0 1172736 1172736 2153472014 ags54g605qs0f
*/
old 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000063983060', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F50B144E228 24994 1 1 2 KGLHD 0000000063983030 1168576 recr 80 00
--可以发现chunk的大小占用1168576。1168576/1024/1024=1.1144M
4.是否通过这个可以确定sql语句也可以进入保留池呢？
--修改bb.sql脚本：
select /*+ &&1
zzzzzzzzzzzzzzzz
...
zzzzz
&&1 */ * from dept where deptno= &&1;
--这样每次不同的sql语句都不一样。
#! /bin/bash
for i in $(seq 500)
do
echo @bb.sql $i
done
--执行它产生执行脚本cc.sql。
--执行cc.sql。
--打开另外的会话不断执行：
SYS@book> SELECT * FROM x$ksmsp WHERE KSMCHSIZ=1168576 and KSMCHCLS like '%recr%';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F9C119523F8 9509 1 1 2 KGLHD 00000000656B1AE0 1168576 recr 80 00
00007F9C11952190 9516 1 1 2 KGLHD 0000000065593DC0 1168576 recr 80 00
00007F9C11951F28 9523 1 1 2 KGLHD 00000000654760A0 1168576 recr 80 00
SYS@book> SELECT * FROM x$ksmsp WHERE KSMCHSIZ=1168576 and KSMCHCLS like '%recr%';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F9C119508E0 9510 1 1 2 KGLHD 00000000656B1AE0 1168576 recr 80 00
00007F9C11952660 9517 1 1 2 KGLHD 0000000065593DC0 1168576 recr 80 00
00007F9C119523F8 9524 1 1 2 KGLHD 00000000654760A0 1168576 recr 80 00
00007F9C11951DC8 9542 1 1 2 KGLHD 0000000065206638 1168576 recr 80 00
00007F9C11951B60 9549 1 1 2 KGLHD 00000000650E8918 1168576 recr 80 00
..
SYS@book> SELECT * FROM x$ksmsp WHERE KSMCHSIZ=1168576 and KSMCHCLS like '%recr%';
no rows selected
--到最后竟然没有查询结果。顺便从共享池找到一条相似sql语句看看。
SYS@book> @ &r/sharepool/shp4 0000000061A67A00
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 0000000061B76578 0000000061A67A00 select /*+ 485 zzzzzzzzzzzzzzzzzzzzzzzzz 0000000061982F28 000000007CFEC770 4488 12144 1171084 1187716 1187716 4105512445 dw1zwymuba9gx
父游标句柄地址 0000000061A67A00 0000000061A67A00 select /*+ 485 zzzzzzzzzzzzzzzzzzzzzzzzz 00000000618DC7A8 00 1172737 0 0 1172737 1172737 4105512445 dw1zwymuba9gx
*/
old 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000061A67A00', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F9C11A18C60 6516 1 1 2 KGLHD 0000000061A679D0 18304 recr 80 00
--哦！KSMCHSIZ=18304.说明内存不足可以拆分多段。也没有进入保留池。
--太复杂放弃！