[20180819]關於父子游標問題(11g).txt
[20180819]關於父子游標問題(11g).txt
--//sql語句存在父子游標,子游標堆6在父遊標堆0裡面.
--//如果存在許多子游標的情況下,父遊標堆0是否大小是發生變化呢.測試看看.
--//另外11g引入引數_cursor_obsolete_threshold限制子游標的數量,測試它的一些控制機制.
1.環境:
--//session 1:
SCOTT@book> @ 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
SCOTT@book> SELECT count(*) FROM dept WHERE deptno=10;
COUNT(*)
----------
1
--//確定sql_id=2xw4k6w7wc5ka.
--//session 2:
SYS@book> @ &r/hide _cursor_obsolete_threshold
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
-------------------------- ---------------------------------------------- ------------- ------------- ------------
_cursor_obsolete_threshold Number of cursors per parent before obsoletion TRUE 1024 1024
--//退出session 1,重新整理共享池,清除該語句在共享池.這樣才能清除乾淨.
SYS@book> alter system flush shared_pool;
System altered.
2.建立測試指令碼:
$ cat aa.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&2
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||i;
FOR j IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||j;
EXECUTE IMMEDIATE 'SELECT count(*) FROM dept WHERE deptno=10' into l_count;
END LOOP;
END LOOP;
END;
/
--//執行如上指令碼,能產生許多子游標.主要是因為環境變數發生了變化.
--//session 1:
SCOTT@book> @ aa.sql 1 64
PL/SQL procedure successfully completed.
3.檢視父子游標情況:
--//session 2:
SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父遊標控制程式碼地址 000000007CBC2C60 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CBC2BA8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
--//檢視父遊標堆0的chunk:
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007CBC2BA8')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F8F6AF85568 1634 1 1 1 KGLH0^fc6164a 000000007DB3C420 4096 freeabl 0 000000007CBC2BA8
00007F8F6AF5A1F8 2515 1 1 1 KGLH0^fc6164a 000000007D879970 4096 freeabl 0 000000007CBC2BA8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
00007F8F6AF5EA40 2676 1 1 1 KGLH0^fc6164a 000000007D7C2F20 4096 freeabl 0 000000007CBC2BA8
00007F8F6AF55250 3713 1 1 1 KGLH0^fc6164a 000000007D342488 4096 freeabl 0 000000007CBC2BA8
00007F8F6AF450F0 4407 1 1 1 KGLH0^fc6164a 000000007CE8C5F8 4096 freeabl 0 000000007CBC2BA8
00007F8F6AF47AC0 4471 1 1 1 KGLH0^fc6164a 000000007CE6D850 4096 freeabl 0 000000007CBC2BA8
00007F8F6AF30440 4856 1 1 1 KGLH0^fc6164a 000000007CC973C8 4096 freeabl 0 000000007CBC2BA8
00007F8F6AF33E80 4965 1 1 1 KGLH0^fc6164a 000000007CBD41F0 4096 freeabl 0 000000007CBC2BA8
00007F8F6B0A1400 5593 1 1 1 KGLH0^fc6164a 000000007C7F4F60 4096 freeabl 0 000000007CBC2BA8
00007F8F6B08CD28 6025 1 1 1 KGLH0^fc6164a 000000007C55FC10 4096 freeabl 0 000000007CBC2BA8
00007F8F6B0907B0 6226 1 1 1 KGLH0^fc6164a 000000007C442F48 4096 freeabl 0 000000007CBC2BA8
00007F8F6B07A300 6652 1 1 1 KGLH0^fc6164a 000000007C137798 4096 freeabl 0 000000007CBC2BA8
00007F8F6B070CD8 7591 1 1 1 KGLH0^fc6164a 000000007BC7D898 4096 freeabl 0 000000007CBC2BA8
00007F8F6B072158 7717 1 1 1 KGLH0^fc6164a 000000007BB93BC0 4096 freeabl 0 000000007CBC2BA8
00007F8F6B074238 7807 1 1 1 KGLH0^fc6164a 000000007BB49798 4096 freeabl 0 000000007CBC2BA8
00007F8F6B0754B8 7846 1 1 1 KGLH0^fc6164a 000000007BB19348 4096 freeabl 0 000000007CBC2BA8
00007F8F6B058C38 8653 1 1 1 KGLH0^fc6164a 000000007B62C700 4096 freeabl 0 000000007CBC2BA8
00007F8F6B05AFB8 8778 1 1 1 KGLH0^fc6164a 000000007B5A06E8 4096 freeabl 0 000000007CBC2BA8
00007F8F6B05C6B0 8804 1 1 1 KGLH0^fc6164a 000000007B588C38 4096 freeabl 0 000000007CBC2BA8
00007F8F6B05DDA8 8830 1 1 1 KGLH0^fc6164a 000000007B55FD78 4096 recr 4095 000000007CBC2BA8
00007F8F6B02A960 8930 1 1 1 KGLH0^fc6164a 000000007B4D8640 4096 freeabl 0 000000007CBC2BA8
00007F8F6B02A490 8944 1 1 1 KGLH0^fc6164a 000000007B4AAFF0 4096 freeabl 0 000000007CBC2BA8
22 rows selected.
--//可以發現如果產生子游標很多,父遊標堆0的chunk也會很多,不像1個子游標的情況下僅僅1個chunk.
--//檢視父遊標堆0的描述符chunk:
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007CBC2BA8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F8F6AF337F8 4997 1 1 1 KGLDA 000000007CBC2B40 240 freeabl 0 00
SYS@book> @ &r/sharepool/shp4 2xw4k6w7wc5ka 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007CBC27E0 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 0 0 0 000000007CBC2728 000000007B5604E8 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 0
子游標控制程式碼地址 000000007C6C4A90 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 0 0 0 000000007C6C49D8 000000007B560AB0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 1
子游標控制程式碼地址 000000007DA59628 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 0 0 0 000000007DA59570 000000007B4D89C0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 2
子游標控制程式碼地址 000000007D66E770 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 0 0 0 000000007D66E6B8 000000007B4D8E80 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 3
...//太長
子游標控制程式碼地址 000000007B693320 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 1 0 0 000000007B693268 000000007D87A1B0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 63
父遊標控制程式碼地址 000000007CBC2C60 000000007CBC2C60 SELECT count(*) FROM dept WHERE deptno=1 1 0 0 000000007CBC2BA8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
65 rows selected.
--//隨便看看一個子游標堆6的描述符chunk:(KGLOBHD6=000000007D87A1B0)
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007D87A1B0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F8F6AF6DD40 2621 1 1 1 KGLH0^fc6164a 000000007D879970 4096 freeabl 0 000000007CBC2BA8
--//可以發現堆6的描述符chunk與前面的父遊標堆0的chunk相同,注意看前面下劃線內容.也就是子游標堆6的描述符chunk在父遊標堆0的chunk中.
4.繼續測試_cursor_obsolete_threshold限制子游標的數量.
--//退出session 1,重新整理共享池.
--//session 2:
SYS@book> alter system flush shared_pool;
System altered.
SCOTT@book> alter session set "_cursor_obsolete_threshold"=64;
Session altered.
--//預設引數1024,有點大,減少到64,這樣好測試一些.
--//session 1:
SCOTT@book> @ aa.sql 1 65
PL/SQL procedure successfully completed.
--//session 2
SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父遊標控制程式碼地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B442B50 00 4736 0 0 4736 4736 264640074 2xw4k6w7wc5ka 65535
--//產生2個父遊標,注意2條記錄的KGLHDLMD=1.表示還沒有釋放遊標.
--//注:我開始以為會出現多父多子的情況.實際上並不是,檢視v$sql檢視就很容易明白.
SYS@book> select address,child_number,IS_OBSOLETE from v$sql where sql_id='2xw4k6w7wc5ka' and IS_OBSOLETE='N';
ADDRESS CHILD_NUMBER I
---------------- ------------ -
000000007D0716A0 0 N
SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE;
ADDRESS I COUNT(*)
---------------- - ----------
000000007D0716A0 N 1
000000007BBF0758 Y 64
--//可以發現僅僅1個子游標是IS_OBSOLETE='N'.其它IS_OBSOLETE='Y',共有64個子游標,而且地址也不同(這個地址對應父遊標的地址).繼續測試:
--//父遊標地址000000007BBF0758下的子游標都是IS_OBSOLETE='Y'.
--//session 1:
SCOTT@book> @ aa.sql 1 65
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父遊標控制程式碼地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B442B50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007B59E0F8 000000007B59E0F8 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B59E040 00 4736 0 0 4736 4736 264640074 2xw4k6w7wc5ka 65535
SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE;
ADDRESS I COUNT(*)
---------------- - ----------
000000007D0716A0 Y 64
000000007B59E0F8 N 2
000000007BBF0758 Y 64
--//產生2個父遊標,注意後2條記錄的KGLHDLMD=1. 而地址000000007B59E0F8對應的IS_OBSOLETE='N',其它都是IS_OBSOLETE='Y'.
--//表示父遊標控制程式碼地址=000000007B59E0F8,當前有效(IS_OBSOLETE='N').繼續測試:
--//session 1:
SCOTT@book> @ aa.sql 1 65
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父遊標控制程式碼地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B442B50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007B59E0F8 000000007B59E0F8 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B59E040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007CB65CB0 000000007CB65CB0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CB65BF8 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535
SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE;
ADDRESS I COUNT(*)
---------------- - ----------
000000007D0716A0 Y 64
000000007BBF0758 Y 64
000000007B59E0F8 Y 64
000000007CB65CB0 N 3
--//大家自己看,不再說明.
--//可以發現1個規律.如果當前父遊標下存在64個子游標的情況下,再有子游標產生,該父遊標下的子游標無效(IS_OBSOLETE='Y'),建立新的父遊標.
--//我前面呼叫的指令碼@ aa.sql 1 65,每次都有1個子游標無法容納,產生1個新的父遊標,這樣3次,這樣新建立的父遊標下就存在3個子游標.
--//如果執行如下,就不會建立新的父遊標.
--//session 1:
SCOTT@book> @ aa.sql 1 61
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
old 21: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 21: WHERE kglobt03 = '2xw4k6w7wc5ka' or kglhdpar='2xw4k6w7wc5ka' or kglhdadr='2xw4k6w7wc5ka' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父遊標控制程式碼地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B442B50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007B59E0F8 000000007B59E0F8 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B59E040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007CB65CB0 000000007CB65CB0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CB65BF8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE;
ADDRESS I COUNT(*)
---------------- - ----------
000000007D0716A0 Y 64
000000007BBF0758 Y 64
000000007B59E0F8 Y 64
000000007CB65CB0 N 64
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//注意看KGLHDLMD=1那行,父遊標控制程式碼地址=000000007CB65CB0.與下劃線看到的地址一致(IS_OBSOLETE='N').如果我繼續執行
--//session 1:
SCOTT@book> @ aa.sql 1 64
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父遊標控制程式碼地址 000000007BBF0758 000000007BBF0758 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007DA01CE8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007D0716A0 000000007D0716A0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B442B50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007B59E0F8 000000007B59E0F8 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B59E040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007CB65CB0 000000007CB65CB0 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CB65BF8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007CE9EFB8 000000007CE9EFB8 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007CE9EF00 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535
SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE;
ADDRESS I COUNT(*)
---------------- - ----------
000000007CE9EFB8 N 3
000000007CB65CB0 Y 64
000000007D0716A0 Y 64
000000007BBF0758 Y 64
000000007B59E0F8 Y 64
--//奇怪竟然又生產新的父遊標,下面有3個子游標.why?
--//中午自己認真看一遍,突然明白為什麼存在3個子游標,IS_OBSOLETE='N'.
5.一步一步來解析:
--//執行@ aa.sql 1 65, 一個父遊標僅僅有64個子遊標,這樣最後1個語句,生成新父遊標,對應的optimizer_index_caching=65.
--//執行@ aa.sql 1 65, 執行到optimizer_index_caching=64時,該父遊標無法再加入子游標,生成新父遊標,對應的optimizer_index_caching=64,65.存在2個子游標.
--//執行@ aa.sql 1 65, 執行到optimizer_index_caching=63時,該父遊標無法再加入子游標,生成新父遊標,對應的optimizer_index_caching=63,64,65.
--//執行@ aa.sql 1 61, 對於父遊標正好有64個子遊標.不會生成新的父遊標.而對應子游標的optimizer_index_caching=63,64,65,1,2,....,61
--//執行@ aa.sql 1 64, optimizer_index_caching從1,2,..,61都能找到對應的子游標.而當執行optimizer_index_caching=62時,全部子游標不合適.
--//而且該父遊標下已經存在64個子遊標,這樣該父遊標下全部子游標變成IS_OBSOLETE='Y'.生成新的父遊標.對應的optimizer_index_caching=62,63,64.存在3個子游標.
--//可以透過一個簡單的測試證明自己的判斷:
--//退出sessioin 1,重新整理共享池.
--//session 2:
SYS@book> alter system flush shared_pool;
System altered.
--//session 1,順序執行如下:
SCOTT@book> alter session set "_cursor_obsolete_threshold"=64;
Session altered.
@ aa.sql 1 65
@ aa.sql 1 65
@ aa.sql 1 65
@ aa.sql 1 61
--//建立測試指令碼ab.sql:
$ cat ab.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 62..64
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||i;
FOR j IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||j;
EXECUTE IMMEDIATE 'SELECT count(*) FROM dept WHERE deptno=10' into l_count;
END LOOP;
END LOOP;
END;
/
--//注:僅僅呼叫執行optimizer_index_caching=62,63,64的情況.
--//session 1
SCOTT@book> @ ab.sql 1
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父遊標控制程式碼地址 000000007D3E0D18 000000007D3E0D18 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007D3E0C60 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007CD26CF0 000000007CD26CF0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007CD26C38 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007B4808C0 000000007B4808C0 SELECT count(*) FROM dept WHERE deptno=1 0 0 000000007B480808 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007D14F128 000000007D14F128 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007D14F070 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父遊標控制程式碼地址 000000007B9760C8 000000007B9760C8 SELECT count(*) FROM dept WHERE deptno=1 1 0 000000007B976010 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535
SYS@book> select address,IS_OBSOLETE,count(*) from v$sql where sql_id='2xw4k6w7wc5ka' group by address ,IS_OBSOLETE;
ADDRESS I COUNT(*)
---------------- - ----------
000000007D3E0D18 Y 64
000000007CD26CF0 Y 64
000000007B9760C8 N 3
000000007D14F128 Y 64
000000007B4808C0 Y 64
--//還有3個子遊標,IS_OBSOLETE='N'.
--//測試有點亂,不過還是能基本說明問題.oracle各個版本_cursor_obsolete_threshold引數一直的不斷調整.
--//看來家裡的windows系統12.1.0.1:
SYS@test> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SYS@test> @ hide _cursor_obsolete_threshold
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
-------------------------- ----------------------------------------------- ------------- ------------- ------------
_cursor_obsolete_threshold Number of cursors per parent before obsoletion. FALSE 64 64
--//據說12.2.0.1版本修改為8192.當然重點定位為什麼子游標太多,定位問題很關鍵.
--//我個人感覺1024還是比較合理.
6.附上測試指令碼:
--//shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, '父遊標控制程式碼地址',
'子游標控制程式碼地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
KGLHDLMD,
KGLHDPMD,
-- kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03 ,
kglobt09
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;
--//shp4z.sql
column N0_6_16 format 99999999
select * from (
SELECT DECODE (kglhdadr,
kglhdpar, '父遊標控制程式碼地址',
'子游標控制程式碼地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
KGLHDLMD,
KGLHDPMD,
-- kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03 ,
kglobt09
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
) where kglhdadr=kglhdpar;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2212504/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180813]重新整理共享池與父子游標.txt
- [20211220]關於標量子查詢問題.txt
- [20201210]11G ACS相關問題.txt
- Oracle - 共享遊標、父子游標、硬軟解析Oracle
- [20191202]關於hugepages相關問題.txt
- [20191129]關於hugepages的問題.txt
- [20181123]關於降序索引問題.txt索引
- [20180403]關於時區問題.txt
- [20201126]11g VPD的問題.txt
- [20230317]關於TIME_WAIT問題.txtAI
- [20210520]關於主鍵索引問題.txt索引
- [20190918]關於函式索引問題.txt函式索引
- [20181229]關於字串的分配問題.txt字串
- [20190910]關於降序索引問題5.txt索引
- [20200711]關於左右連線的問題.txt
- [20200416]關於軟軟解析的問題.txt
- [20181124]關於降序索引問題4.txt索引
- [20181124]關於降序索引問題3.txt索引
- [20181124]關於降序索引問題2.txt索引
- 關於 a 標籤跳轉問題
- [20191220]關於共享記憶體段相關問題.txt記憶體
- [20211018]運維中關於history的問題.txt運維
- [20190603]關於dbms_output輸出問題.txt
- [20180724]Flashback query和子游標共享.txt
- [20210813]關於測試sql語句子游標的效能的一些補充說明.txtSQL
- [20190930]關於資料結構設計問題.txt資料結構
- [20191202]關於oracle例項是否使用hugepages問題.txtOracle
- [20190102]關於字串的分配問題(10g).txt字串
- [20180419]關於閃回的一些問題.txt
- [20180423]關於rman備份的問題2.txt
- 關於a標籤的字型顏色問題
- Recoil Input 游標位置被重置到末尾的問題
- [20191223]關於共享記憶體段相關問題3.txt記憶體
- [20221212]關於pre_page_sga引數的問題.txt
- [20200129]子游標不共享BIND_EQUIV_FAILURE.txtUIAI
- [20210812]測試sql語句子游標的效能.txtSQL
- 關於二維陣列指標的問題陣列指標
- [20201116]11g連線謂詞推入push_pred問題.txt