[20180906]測試同一會話多個子遊標是否快取.txt

[20180906]測試同一會話多個子遊標是否快取.txt

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

SCOTT@test01p> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
       234         27 2444:3304                DEDICATED 5148                      55          9 alter system kill session '234,27' immediate;

SCOTT@test01p> show parameter optimizer_index_caching
NAME                                 TYPE                 VALUE
------------------------------------ -------------------- ----------
optimizer_index_caching              integer              0

2.測試:
--//session 1:
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
--//確定sql_id=07447rnnn54q7.

--//session 2:
SYS@test> select * from V$OPEN_CURSOR where sid=234 and sql_id='07447rnnn54q7';
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                             LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE           CHILD_ADDRESS    CON_ID
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------ ------------------- ----------- --------------------- ---------------- ------
000007FF312D41D8 234 SCOTT     000007FF25F196C8  692228807 07447rnnn54q7 select  * from deptx where deptno=10                                 SESSION CURSOR CACHED 000007FF25F18218      3

--//該語句已經進入游標快取..

SYS@test> @ sharepool/shp4 07447rnnn54q7 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000007FF25F18218 000007FF25F196C8 select  * from deptx where deptno=10              1          0          0 000007FF25F18160 000007FF25F18D48       4072      12144       3117     19333      19333  692228807 07447rnnn54q7          0
父遊標控制程式碼地址 000007FF25F196C8 000007FF25F196C8 select  * from deptx where deptno=10              1          0          0 000007FF25F19610 00                     4072          0          0      4072       4072  692228807 07447rnnn54q7      65535

--//V$OPEN_CURSOR.ADDRESS=000007FF25F196C8 對應父遊標控制程式碼地址.
--//V$OPEN_CURSOR.CHILD_ADDRES=000007FF25F18218,對應子游標控制程式碼地址.

3.繼續測試:
--//session 1:
SCOTT@test01p> alter session set optimizer_index_caching=100;
Session altered.

--//修改會話引數optimizer_index_caching=100,這樣游標不再共享,必須生成新的子游標.
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;

--//session 2:
SYS@test> select * from V$OPEN_CURSOR where sid=234 and sql_id='07447rnnn54q7';
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                             LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE           CHILD_ADDRESS    CON_ID
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------ ------------------- ----------- --------------------- ---------------- ------
000007FF312D41D8 234 SCOTT     000007FF25F196C8  692228807 07447rnnn54q7 select  * from deptx where deptno=10                                 SESSION CURSOR CACHED 000007FF25F18218      3
000007FF312D41D8 234 SCOTT     000007FF25F196C8  692228807 07447rnnn54q7 select  * from deptx where deptno=10                                 SESSION CURSOR CACHED 000007FF28E627F8      3

--//可以發現同一個會話,相同的sql語句可以快取2個遊標.我一直以為僅僅快取1個.看來親自測試很有必要.

SYS@test> @ sharepool/shp4 07447rnnn54q7 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000007FF25F18218 000007FF25F196C8 select  * from deptx where deptno=10              0          0          0 000007FF25F18160 000007FF25F18D48       4072      12144       4565     20781      20781  692228807 07447rnnn54q7          0
子游標控制程式碼地址 000007FF28E627F8 000007FF25F196C8 select  * from deptx where deptno=10              1          0          0 000007FF241136C0 000007FF2420BAF8       4072      12144       4565     20781      20781  692228807 07447rnnn54q7          1
父遊標控制程式碼地址 000007FF25F196C8 000007FF25F196C8 select  * from deptx where deptno=10              1          0          0 000007FF25F19610 00                     4072          0          0      4072       4072  692228807 07447rnnn54q7      65535

--//V$OPEN_CURSOR.ADDRESS=000007FF25F196C8 對應父遊標控制程式碼地址.
--//V$OPEN_CURSOR.CHILD_ADDRES=000007FF25F18218,對應子游標控制程式碼地址(KGLOBT09=0)
--//V$OPEN_CURSOR.CHILD_ADDRES=000007FF28E627F8,對應子游標控制程式碼地址(KGLOBT09=1).
--//順便說一下x$kglob.KGLOBT09 對應的就是child number號.x$kglob.KGLOBT09=65535就是父遊標.

--//可以發現當執行3次以上快取遊標後,實際上類似建立一種快捷方式,直接能定位到子游標控制程式碼地址,
--//有一些文章提到可以定位子游標控制程式碼堆6,執行獲得執行計劃.避免一些latch以及metux的操作.
--//也就是軟軟解析.

--//另外如果已經快取的遊標,新開啟的回話第一次執行也是軟解析,因為執行前V$OPEN_CURSOR檢視在當前sid下沒有記錄.
--//再次執行才是軟軟解析.

--//重新整理共享池重複測試:
--//session 1:
SCOTT@test01p> @spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- -----------------------------------------------
       152          9 5460:4376                DEDICATED 5564                      22          5 alter system kill session '152,9' immediate;
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
select  * from deptx where deptno=10;
--//確定sql_id=07447rnnn54q7.

--//session 2:
SYS@test01p> select * from V$OPEN_CURSOR where  sid=152 and sql_id='07447rnnn54q7';
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                             LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE           CHILD_ADDRESS    CON_ID
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------ ------------------- ----------- --------------------- ---------------- ------
000007FF312547D8 152 SCOTT     000007FF23E1CC60  692228807 07447rnnn54q7 select  * from deptx where deptno=10                                 SESSION CURSOR CACHED 000007FF257BA640      3

SYS@test01p> @ sharepool/shp4 07447rnnn54q7 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000007FF257BA640 000007FF23E1CC60 select  * from deptx where deptno=10              1          0          0 000007FF1F2CCAE0 000007FF2554B638       4072      12144       3117     19333      19333  692228807 07447rnnn54q7          0
父遊標控制程式碼地址 000007FF23E1CC60 000007FF23E1CC60 select  * from deptx where deptno=10              1          0          0 000007FF1FD5C898 00                     4072          0          0      4072       4072  692228807 07447rnnn54q7      65535

--//session 3:
SCOTT@test01p> @spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
        13        157 2340:2536                DEDICATED 4428                      56          4 alter system kill session '13,157' immediate;

--//session 2:
SYS@test01p> select * from V$OPEN_CURSOR where sid=13 and sql_id='07447rnnn54q7';
no rows selected

--//sid=13 還沒有快取游標.

--//session 3:
SCOTT@test01p> select  * from deptx where deptno=10;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK

SCOTT@test01p> select sysdate from dual ;
SYSDATE
-------------------
2018-09-07 22:18:02

--//session 2:
SYS@test01p> select * from V$OPEN_CURSOR where  sql_id='07447rnnn54q7';
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                             LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE                     CHILD_ADDRESS    CON_ID
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------ ------------------- ----------- ------------------------------- ---------------- ------
000007FF31123630  13 SCOTT     000007FF23E1CC60  692228807 07447rnnn54q7 select  * from deptx where deptno=10                                 DICTIONARY LOOKUP CURSOR CACHED 000007FF257BA640      3
000007FF312547D8 152 SCOTT     000007FF23E1CC60  692228807 07447rnnn54q7 select  * from deptx where deptno=10                                 SESSION CURSOR CACHED           000007FF257BA640      3

--//可以發現會話sid=13(session 3)當前的CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'.