[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'.