[20151229]關於共享池4x-SQL記憶體結構父子游標 (補充3).txt

-- 前幾天的測試,還是有點問題,連結如下:
-- http://blog.itpub.net/267265/viewspace-1942280/
-- 繼續做1點補充:

1.環境:
SCOTT@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

--session 1:
SCOTT@book> select * from dept where deptno=10;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

--sql_id=4xamnunv51w9j，這個可以查詢v$sql獲得。

2.測試:
--session 2:
$ cat shp4.sql
SELECT DECODE (kglhdadr,
               kglhdpar, '父遊標控制程式碼地址',
               '子游標控制程式碼地址')
          text,
       kglhdadr,
       kglhdpar,
       substr(kglnaobj,1,40) c40,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16,
           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 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'

TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16 N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游標控制程式碼地址 000000006318D1D0 0000000061B435C0 select * from dept where deptno=10       000000006653F0F8 00000000695D1770       4488      12144       3067    19699      19699 911274289 4xamnunv51w9j
父遊標控制程式碼地址 0000000061B435C0 0000000061B435C0 select * from dept where deptno=10       000000007C37E8D0 00                     4720          0          0     4720       4720 911274289 4xamnunv51w9j

3.前面我的測試提到如果這個時候執行重新整理共享池不會導致這條語句從共享池退出。
SYS@test> alter system flush shared_pool;
System altered.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16 N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游標控制程式碼地址 000000006318D1D0 0000000061B435C0 select * from dept where deptno=10       00               00                        0          0       3067     3067       3067 911274289 4xamnunv51w9j
父遊標控制程式碼地址 0000000061B435C0 0000000061B435C0 select * from dept where deptno=10       000000007C37E8D0 00                     4720          0          0     4720       4720 911274289 4xamnunv51w9j

--子游標控制程式碼的KGLOBHD0 ，KGLOBHD6 不在. 但是父遊標控制程式碼與子游標控制程式碼以及父遊標堆0不會清除。

--實際上我一直認為如果session 1不退出,這條語句不會從共享池退出，實際上存在一點點小錯誤。如果回到會話1執行其他語句，在回
--到session 2再重新整理共享池，就可以重新整理將這條語句清除出去。

--session 1:
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:36:01

SYS@book> alter system flush shared_pool ;
System altered.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
old 16: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1'
new 16: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j'
no rows selected

4.再回到session 1，執行3次。

--session 1:
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:39:04

SCOTT@book> @&r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
90 5157 13988 32 2 alter system kill session '90,5157' immediate;

--session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
SADDR                   SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                           LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- ---------------------------------- ------------------- ----------- --------------------
0000000084EC6260         90 SCOTT      000000006307E710 911274289 4xamnunv51w9j select * from dept where deptno=10                                 DICTIONARY LOOKUP CU
                                                                                                                                                    RSOR CACHED
--CURSOR_TYPE型別變為"DICTIONARY LOOKUP CURSOR CACHED".

--這個時候重新整理看看：

SYS@book> alter system flush shared_pool ;
System altered.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16 N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游標控制程式碼地址 000000008505B4D8 000000006307E710 select * from dept where deptno=10       00               00                        0          0       3067     3067       3067 911274289 4xamnunv51w9j
父遊標控制程式碼地址 000000006307E710 000000006307E710 select * from dept where deptno=10       000000006193DB68 00                     4720          0          0     4720       4720 911274289 4xamnunv51w9j

--子游標控制程式碼的KGLOBHD0 ，KGLOBHD6 不在. 但是父遊標控制程式碼與子游標控制程式碼以及父遊標堆0不會清除。
--換一句話講如果執行的sql語句執行3次在一個會話中，CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'.

--回到session 1，再執行2次:

select * from dept where deptno=10;
select * from dept where deptno=10;

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:46:05

--session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
no rows selected

--????沒有記錄。也就是CURSOR_TYPE="DICTIONARY LOOKUP CURSOR CACHED",重新整理共享池會導致該語句不會被會話快取。

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16 N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游標控制程式碼地址 000000008505B4D8 000000006307E710 select * from dept where deptno=10       0000000085053450 000000006114F6F8       4528      12144       3067    19739      19739 911274289 4xamnunv51w9j
父遊標控制程式碼地址 000000006307E710 000000006307E710 select * from dept where deptno=10       000000006193DB68 00                     4720          0          0     4720       4720 911274289 4xamnunv51w9j

--session 1:
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:48:33

--session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
SADDR                   SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                           LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- ---------------------------------- ------------------- ----------- --------------------
0000000084EC6260         90 SCOTT      000000006307E710 911274289 4xamnunv51w9j select * from dept where deptno=10                                 DICTIONARY LOOKUP CU
                                                                                                                                                    RSOR CACHED

--執行3次，CURSOR_TYPE="DICTIONARY LOOKUP CURSOR CACHED"

--session 1:
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2015-12-29 09:49:18

--session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- ---------------------------------- ------------------- ----------- ---------------------
0000000084EC6260 90 SCOTT 000000006307E710 911274289 4xamnunv51w9j select * from dept where deptno=10 SESSION CURSOR CACHED

--執行4次，CURSOR_TYPE="SESSION CURSOR CACHED".

--這個時候再重新整理看看：

SYS@book> alter system flush shared_pool ;
System altered.

SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- ---------------------------------- ------------------- ----------- ---------------------
0000000084EC6260 90 SCOTT 000000006307E710 911274289 4xamnunv51w9j select * from dept where deptno=10 SESSION CURSOR CACHED

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16 N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游標控制程式碼地址 000000008505B4D8 000000006307E710 select * from dept where deptno=10       00               00                        0          0       3067     3067       3067 911274289 4xamnunv51w9j
父遊標控制程式碼地址 000000006307E710 000000006307E710 select * from dept where deptno=10       000000006193DB68 00                     4720          0          0     4720       4720 911274289 4xamnunv51w9j

5.退出會話1：

SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j';
no rows selected

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16 N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游標控制程式碼地址 000000008505B4D8 000000006307E710 select * from dept where deptno=10       00               00                        0          0       3067     3067       3067 911274289 4xamnunv51w9j
父遊標控制程式碼地址 000000006307E710 000000006307E710 select * from dept where deptno=10       000000006193DB68 00                     4720          0          0     4720       4720 911274289 4xamnunv51w9j

SYS@book> alter system flush shared_pool ;
System altered.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j
no rows selected

--也就是退出會話，在session cache cursor不存在了，再重新整理共享池就可以將這條sql語句佔用共享池的記憶體清除乾淨。

--總結：
1.實際上session 1執行第1次，在session 2 檢視v$open_cursor檢視，對應會話的CURSOR_TYPE='OPEN'.這個時候重新整理共享池還會有部分內容佔據共享池。(父子游標以及父遊標堆0,父遊標堆0的DS描述符).
2.這個時候session 1在執行別的語句，在session 2 檢視v$open_cursor檢視，對應會話才不存在，重新整理共享池可以清除乾淨。
3.重新再來session 1執行3次後，在session 2 檢視v$open_cursor檢視，對應會話的CURSOR_TYPE='OPEN'.再執行別的語句，在session 2 檢視v$open_cursor檢視CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'。
4.這個時候重新整理共享池還會有部分內容佔據共享池。檢視v$open_cursor檢視，對應的sql語句的CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'。
5.但是這個時候session 1 相同語句，開啟游標要重新記數，執行4次以上，CURSOR_TYPE="SESSION CURSOR CACHED".
6.這個時候無論如何重新整理共享池還會有部分內容佔據共享池，但是這個時候session 1 相同語句，開啟游標還要重新記數。

--總之如果語句在執行完成沒有其他執行，當前的游標是open狀態，這個時候無法完全從共享池清除。
--如果CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'(執行3次) 或者CURSOR_TYPE='SESSION CURSOR CACHED'(執行4次),這個時候無法完全從共享池清除。但是重新開啟的游標要重新記數。
--在v$open_cusor無法查詢到，才能徹底的清除乾淨。所以按照vage的介紹，如果出現ora-4031,臨時解決要清除kill一組相似的應用，才能臨時解決這個問題。

--這個是我的測試結果，環境11.2.0.4，其他版本我不確定是這種情況。

1229關於共享池4-SQL記憶體結構父子游標補充3

相關文章