[20170621]Session Cursor Caching 4.txt
--//前面的测试由于匿名块的sql语句使用execute immediate 调用,这样导致
--//在sqlplus下执行相同的sql语句无法共享,产生了子光标.影响的测试.链接:http://blog.itpub.net/267265/viewspace-2141045/
--//这次修改一些直接执行看看.
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
$ cat a3.txt
declare
a number := 0;
begin
select /*+ find_me */ count(*) into a from emp;
end ;
/
--//通过扫描共享池可以确定sql_id是('3msqvmftys0rz','6d4p4q16sp5gy');
2.测试1:
SCOTT@book> @ a3.txt
PL/SQL procedure successfully completed.
SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('3msqvmftys0rz','6d4p4q16sp5gy');
SQL_ID PARSE_CALLS EXECUTIONS
------------- ----------- ----------
3msqvmftys0rz 1 1
6d4p4q16sp5gy 1 1
SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('3msqvmftys0rz','6d4p4q16sp5gy');
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- --------------------
00000000854DC040 232 SCOTT 000000007D772D10 3018588927 3msqvmftys0rz SELECT /*+ find_me */ COUNT(*) FROM EMP PL/SQL CURSOR CACHED
00000000854DC040 232 SCOTT 000000007BE3F8A8 1300927998 6d4p4q16sp5gy declare a number := 0; begin select /*+ find_me */ coun OPEN
--//注意看一个小细节.sql_id='3msqvmftys0rz'对应的sql_text变成了大写(当然我注解就是小写),换一句讲在执行前,oracle做了格式化处理.
--//sql_id='3msqvmftys0rz',PARSE_CALLS=1,并且CURSOR_TYPE='PL/SQL CURSOR CACHED'.
`
3.测试2:
--//单独执行sql语句看看.
SCOTT@book> SELECT /*+ find_me */ COUNT(*) FROM EMP;
COUNT(*)
----------
14
SCOTT@book> select sql_id,PARSE_CALLS,executions,child_number from v$sql where sql_id in ('3msqvmftys0rz','6d4p4q16sp5gy');
SQL_ID PARSE_CALLS EXECUTIONS CHILD_NUMBER
------------- ----------- ---------- ------------
3msqvmftys0rz 1 1 0
3msqvmftys0rz 1 1 1
6d4p4q16sp5gy 1 1 0
SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('3msqvmftys0rz','6d4p4q16sp5gy');
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- --------------------------------------- ------------------- ----------- -------------------
00000000854DC040 232 SCOTT 000000007D772D10 3018588927 3msqvmftys0rz SELECT /*+ find_me */ COUNT(*) FROM EMP PL/SQL CURSOR CACHED
00000000854DC040 232 SCOTT 000000007D772D10 3018588927 3msqvmftys0rz SELECT /*+ find_me */ COUNT(*) FROM EMP OPEN
--//可以发现光标依旧无法共享.
SCOTT@book> @ &r/share 3msqvmftys0rz
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''3msqvmftys0rz''',
SQL_TEXT = SELECT /*+ find_me */ COUNT(*) FROM EMP
SQL_ID = 3msqvmftys0rz
ADDRESS = 000000007D772D10
CHILD_ADDRESS = 000000007C167840
CHILD_NUMBER = 0
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>7</ID><reason>Top Level RPI Cursor(0)</reason><size>2x4</size><ctxxyfl>3072</ctxxyfl><ispri>0</ispri></ChildNode>
--------------------------------------------------
SQL_TEXT = SELECT /*+ find_me */ COUNT(*) FROM EMP
SQL_ID = 3msqvmftys0rz
ADDRESS = 000000007D772D10
CHILD_ADDRESS = 000000007B96F830
CHILD_NUMBER = 1
TOP_LEVEL_RPI_CURSOR = Y
REASON =
--------------------------------------------------
PL/SQL procedure successfully completed.
--//不能共享的原因还是Top Level RPI Cursor.
4.修改匿名块如下,继续测试:
$ cat a4.txt
declare
a number := 0;
begin
null;
select /*+ find_me */ count(*) into a from emp;
end ;
/
SCOTT@book> @ a4.txt
PL/SQL procedure successfully completed.
SCOTT@book> select sql_id,PARSE_CALLS,executions,child_number from v$sql where sql_id in ('3msqvmftys0rz','6d4p4q16sp5gy');
SQL_ID PARSE_CALLS EXECUTIONS CHILD_NUMBER
------------- ----------- ---------- ------------
3msqvmftys0rz 2 2 0
3msqvmftys0rz 1 1 1
6d4p4q16sp5gy 1 1 0
--//sql_id='3msqvmftys0rz',子光标0的PARSE_CALLS=2,增加1次.
SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('3msqvmftys0rz','6d4p4q16sp5gy');
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- --------------------------------------- ------------------- ----------- --------------------
00000000854DC040 232 SCOTT 000000007D772D10 3018588927 3msqvmftys0rz SELECT /*+ find_me */ COUNT(*) FROM EMP PL/SQL CURSOR CACHED
--//可以对于匿名块中的sql语句,无论如何都要分析,以后也都是软分析.