[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语句,无论如何都要分析,以后也都是软分析.