[20160215]超长sql语句与父子光标.txt

[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.说明内存不足可以拆分多段。也没有进入保留池。

--太复杂放弃!

上一篇:服务限流算法的几种实现


下一篇:【java并发】Semaphore 的使用