[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.說明記憶體不足可以拆分多段。也沒有進入保留池。

--太複雜放棄！

[20160215]超長sql語句與父子游標.txt

相關文章