[20170621]Session Cursor Caching 4.txt

[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語句,無論如何都要分析,以後也都是軟分析.