[20180906]测试同一会话多个子游标是否缓存.txt

来源：这里教程网时间：2026-03-03 11:58:05 作者：

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

编辑推荐：

返回列表

[20180906]测试同一会话多个子游标是否缓存.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推