[20170621]Session Cursor Caching 2.txt

lfree發表於2017-06-21

[20170621]Session Cursor Caching 2.txt

--//當sql執行時,第一次要經歷硬分析,第二次軟分析,如果session_cached_cursors設定的化,還可以繞過軟分析,也有人叫"軟軟分析".
--//摘錄連結的一段話:
--//www.toadworld.com/platforms/oracle/b/weblog/archive/2017/04/12/session-cursor-caching-part-one

When a SQL statement is issued, the server process, after checking its syntax and semantics, searches the library cache
for an existing cursor for the SQL statement. If a cursor does not already exist, a new cursor is created (hard parse),
else existing cursor is used (soft parse). Whereas hard parsing is a resource intensive operation, soft parse, although
less expensive, also incurs some cost, as the server process has to search the library cache for previously parsed SQL,
which requires the use of the library cache and shared pool latches. Latches can often become points of contention for
busy OLTP systems, thereby affecting response time and scalability. To minimize the impact on performance, session
cursors of repeatedly issued statements can be stored in the session cursor cache to reduce the cost of or even
eliminate soft parse. This is called Session Cursor Caching. When session cursor caching is enabled, Oracle caches the
cursor of a reentrant SQL statement in the session memory (PGA /UGA). As a result, the session cursor cache now contains
a pointer into the library cache where the cursor existed when it was closed. Since presence of a cursor in session
cursor cache guarantees the correctness of the corresponding SQL's syntax and semantics, these checks are bypassed when
the same SQL is resubmitted. Subsequently, instead of searching for the cursor in library cache, the server process
follows the pointer in the session memory and uses the cursor after having confirmed its presence and validity.

Hence, if a closed cursor is found in the session cursor cache, it is registered as a 'session cached cursor hit' and
also as a 'soft parse', since a visit to the shared SQL area must be made to  confirm its presence and validity.
However, as we will see, if the cached cursor is in open state, it can be used straightaway, thereby avoiding even the
soft parse.

Thus, session cursor caching:

    Avoids syntax  and semantics check
    Greatly reduces the cost of soft parse by cutting down on latch use and waits
    Can avoid soft parsing if the cached cursor is in an open state

    Improves performance and scalability of applications that repeatedly issue parse calls on the same set of SQL
    statements.

Cursors cached in session cursor cache are managed using an LRU algorithm, which removes older entries from the session
cursor cache to make room for newer ones whenever needed.

--//如果軟分析能減少latch,mutex等相關等待事件,減少cpu的消耗.昨天看了以上鍊接的問題,裡面提到Anonymous PL/SQL Block以及
--//PL/SQL Stored Procedure在第一次執行時就會被cache,而不像單獨執行的sql語句要3次執行才會進入session cursor cache.
II.Caching of the cursor in PL/SQL Block

IIa)Anonymous PL/SQL Block: The cursor of a SQL inside an anonymous PL/SQL block gets cached in the PL/SQL cache on the
    very first execution.

IIb)PL/SQL Stored Procedure: The cursor of a SQL inside a PL/SQL block in a PL/SQL Stored Procedure gets cached in
    PL/SQL cache on the very first execution.

--//我自己也測試看看,驗證這種說法:
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

SCOTT@book> show parameter session_cached_cursors
NAME                   TYPE    VALUE
---------------------- ------- -----
session_cached_cursors integer 50

--//測試指令碼如下:
$ cat a1.txt
begin
  for i in 1 .. 1
    loop
      execute immediate 'select /*+ session_cache */ count(*) from emp' ;
    end loop;
end ;
/
--//透過掃描共享池可以確定sql_id是('35j79kxa30dh4' ,'56gmvvxsuh8u9').

$ cat a2.txt
create or replace procedure sess_cache as
begin
for i in 1 .. 1
  loop
    execute immediate 'select /*+ Session_cache */ count(*) from emp' ;
  end loop;
end ;
/

--//透過掃描共享池可以確定sql_id是('4jkqmfqgmv51n' ,'ahuyb9ssxunh0').
--//注意註解部分存在大小不一致,避免sql_id相同.

2.測試1:
SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
no rows selected

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
no rows selected

--//執行第1次.
SCOTT@book> @ a1.txt
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
56gmvvxsuh8u9           1          1
35j79kxa30dh4           1          1

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- --------------------
00000000854DC040 232 SCOTT     000000007C1AE3A0 1906844489 56gmvvxsuh8u9 begin   for i in 1 .. 1     loop       execute immediate 'se                                 OPEN
00000000854DC040 232 SCOTT     000000007D625090 1412445700 35j79kxa30dh4 select /*+ session_cache */ count(*) from emp                                                PL/SQL CURSOR CACHED

--//執行1次,你可以發現sql_id='35j79kxa30dh4',CURSOR_TYPE='PL/SQL CURSOR CACHED'.換1句話就是已經cache了.
--//再次執行1次:
SCOTT@book> @ a1.txt
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
56gmvvxsuh8u9           2          2
35j79kxa30dh4           2          2

SYS@book> select * from V$OPEN_CURSOR where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- --------------------
00000000854DC040 232 SCOTT     000000007C1AE3A0 1906844489 56gmvvxsuh8u9 begin   for i in 1 .. 1     loop       execute immediate 'se                                 OPEN
00000000854DC040 232 SCOTT     000000007D625090 1412445700 35j79kxa30dh4 select /*+ session_cache */ count(*) from emp                                                PL/SQL CURSOR CACHED

--//可以發現PARSE_CALLS增加1次.換一句話還是執行1次軟分析.
SCOTT@book> @ a1.txt
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
56gmvvxsuh8u9           3          3
35j79kxa30dh4           3          3
--//可以發現PARSE_CALLS增加1次.換一句話還是執行1次軟分析.

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- --------------------
00000000854DC040 232 SCOTT     000000007C1AE3A0 1906844489 56gmvvxsuh8u9 begin   for i in 1 .. 1     loop       execute immediate 'se                                 OPEN
00000000854DC040 232 SCOTT     000000007D625090 1412445700 35j79kxa30dh4 select /*+ session_cache */ count(*) from emp                                                PL/SQL CURSOR CACHED

--//第4次執行:
SCOTT@book> @ a1.txt
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
56gmvvxsuh8u9           4          4
35j79kxa30dh4           4          4

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- -------------------------------
00000000854DC040 232 SCOTT     000000007C1AE3A0 1906844489 56gmvvxsuh8u9 begin   for i in 1 .. 1     loop       execute immediate 'se                                 DICTIONARY LOOKUP CURSOR CACHED
00000000854DC040 232 SCOTT     000000007D625090 1412445700 35j79kxa30dh4 select /*+ session_cache */ count(*) from emp                                                PL/SQL CURSOR CACHED

--//可以發現PARSE_CALLS增加1次.換一句話還是執行1次軟分析.但是注意看匿名塊的sql_id='56gmvvxsuh8u9'的CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'.

--//第5次執行:
SCOTT@book> @ a1.txt
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
56gmvvxsuh8u9           5          5
35j79kxa30dh4           5          5

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- ---------------------
00000000854DC040 232 SCOTT     000000007C1AE3A0 1906844489 56gmvvxsuh8u9 begin   for i in 1 .. 1     loop       execute immediate 'se                                 SESSION CURSOR CACHED
00000000854DC040 232 SCOTT     000000007D625090 1412445700 35j79kxa30dh4 select /*+ session_cache */ count(*) from emp                                                PL/SQL CURSOR CACHED

--//可以發現每次parse_call都會增加.而對應的CURSOR_TYPE='SESSION CURSOR CACHED'.(sql_id=56gmvvxsuh8u9).

3.測試2:
--//測試儲存過程看看.先建立儲存過程.
@ a2.txt

--//第1次執行:
SCOTT@book> exec  sess_cache
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
ahuyb9ssxunh0           1          1
4jkqmfqgmv51n           1          1

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                       LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- ---------------------------------------------- ------------------- ----------- --------------------
00000000854DC040 232 SCOTT     000000007CD6DCC0  836588032 ahuyb9ssxunh0 BEGIN sess_cache; END;                                                         OPEN
00000000854DC040 232 SCOTT     000000007B66C080 2671612980 4jkqmfqgmv51n select /*+ Session_cache */ count(*) from emp                                  PL/SQL CURSOR CACHED
--//可以發現PARSE_CALLS呼叫增1.sql_id='4jkqmfqgmv51n',CURSOR_TYPE='PL/SQL CURSOR CACHED'.也就是已經cache了.

--//第2次執行:
SCOTT@book> exec  sess_cache
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
ahuyb9ssxunh0           2          2
4jkqmfqgmv51n           1          2

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                      LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- --------------------------------------------- ------------------- ----------- --------------------
00000000854DC040 232 SCOTT     000000007CD6DCC0  836588032 ahuyb9ssxunh0 BEGIN sess_cache; END;                                                        OPEN
00000000854DC040 232 SCOTT     000000007B66C080 2671612980 4jkqmfqgmv51n select /*+ Session_cache */ count(*) from emp                                 PL/SQL CURSOR CACHED

--//可以發現儲存過程中執行的sql_id='4jkqmfqgmv51n',PARSE_CALLS=1.也就是沒有軟分析.

--//第3次執行:
SCOTT@book> exec  sess_cache
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
ahuyb9ssxunh0           3          3
4jkqmfqgmv51n           1          3

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                      LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- --------------------------------------------- ------------------- ----------- --------------------
00000000854DC040 232 SCOTT     000000007CD6DCC0  836588032 ahuyb9ssxunh0 BEGIN sess_cache; END;                                                        OPEN
00000000854DC040 232 SCOTT     000000007B66C080 2671612980 4jkqmfqgmv51n select /*+ Session_cache */ count(*) from emp                                 PL/SQL CURSOR CACHED
--//可以發現儲存過程中執行的sql_id='4jkqmfqgmv51n',PARSE_CALLS=1.也就是沒有軟分析.

--//第4次執行:
SCOTT@book> exec  sess_cache
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
ahuyb9ssxunh0           4          4
4jkqmfqgmv51n           1          4

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                      LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- --------------------------------------------- ------------------- ----------- -------------------------------
00000000854DC040 232 SCOTT     000000007CD6DCC0  836588032 ahuyb9ssxunh0 BEGIN sess_cache; END;                                                        DICTIONARY LOOKUP CURSOR CACHED
00000000854DC040 232 SCOTT     000000007B66C080 2671612980 4jkqmfqgmv51n select /*+ Session_cache */ count(*) from emp                                 PL/SQL CURSOR CACHED

--//sql_id='ahuyb9ssxunh0',CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'.
--//可以發現儲存過程中執行的sql_id='4jkqmfqgmv51n',PARSE_CALLS=1.也就是沒有軟分析.

--//第5次執行:
SCOTT@book> exec  sess_cache
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions from v$sql where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SQL_ID        PARSE_CALLS EXECUTIONS
------------- ----------- ----------
ahuyb9ssxunh0           5          5
4jkqmfqgmv51n           1          5

SYS@book> select * from V$OPEN_CURSOR where sql_id in ('4jkqmfqgmv51n' ,'ahuyb9ssxunh0');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                      LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- --------------------------------------------- ------------------- ----------- ---------------------
00000000854DC040 232 SCOTT     000000007CD6DCC0  836588032 ahuyb9ssxunh0 BEGIN sess_cache; END;                                                        SESSION CURSOR CACHED
00000000854DC040 232 SCOTT     000000007B66C080 2671612980 4jkqmfqgmv51n select /*+ Session_cache */ count(*) from emp                                 PL/SQL CURSOR CACHED

--//sql_id='ahuyb9ssxunh0',CURSOR_TYPE='SESSION CURSOR CACHED'.
--//可以發現儲存過程中執行的sql_id='4jkqmfqgmv51n',PARSE_CALLS=1.也就是沒有軟分析.

總結:
1.從以上測試也說明匿名pl/sql塊,與儲存過程的不同.
2.第1次執行sql語句在session cursor cache馬上cache.但是匿名pl/sql塊每次都會進行軟分析.
3.而儲存過程的sql語句,以後執行都是軟軟分析.
4.許多東西還是不理解.也許分析不對.希望大家指正.哈哈.

--//補充測試,另外如果單獨在sqlplus下執行如下:select /*+ session_cache */ count(*) from emp;會建立子游標.因為execute
--//immediate呼叫的原因.產生1次類似遞迴的呼叫.我以前有blog提到這個問題.
--//連結 [20160407]游標共享TOP_LEVEL_RPI_CURSOR=>:http://blog.itpub.net/267265/viewspace-2076620/

SCOTT@book> select /*+ session_cache */ count(*) from emp;
COUNT(*)
--------
14

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  35j79kxa30dh4, child number 1
-------------------------------------
select /*+ session_cache */ count(*) from emp
Plan hash value: 2937609675
--------------------------------------------------------------------
| Id  | Operation        | Name   | E-Rows | Cost (%CPU)| E-Time   |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |        |     1 (100)|          |
|   1 |  SORT AGGREGATE  |        |      1 |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |     14 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1

SCOTT@book> @ &r/share 35j79kxa30dh4
old  15:           and q.sql_id like ''&1''',
new  15:           and q.sql_id like ''35j79kxa30dh4''',
SQL_TEXT                       = select /*+ session_cache */ count(*) from emp
SQL_ID                         = 35j79kxa30dh4
ADDRESS                        = 000000007D625090
CHILD_ADDRESS                  = 000000007CD55258
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>7</ID><reason>Top Level RPI Cursor(0)</reason><size>2x4</size><ctxxyfl>1024</ctxxyfl><ispri>0</ispri></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select /*+ session_cache */ count(*) from emp
SQL_ID                         = 35j79kxa30dh4
ADDRESS                        = 000000007D625090
CHILD_ADDRESS                  = 000000007C3A5C70
CHILD_NUMBER                   = 1
TOP_LEVEL_RPI_CURSOR           = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.

--//看來還給測試不使用execute immediate的情況如何.

SCOTT@book> select sql_id,PARSE_CALLS,executions,child_number from v$sql where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SQL_ID        PARSE_CALLS EXECUTIONS CHILD_NUMBER
------------- ----------- ---------- ------------
56gmvvxsuh8u9           6          6            0
35j79kxa30dh4           6          6            0
35j79kxa30dh4           1          1            1

--//分析呼叫6次,sql_id=35j79kxa30dh4,並且因為游標不能共享有產生子游標,在分析1次.

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
no rows selected

--//奇怪,消失了.

SCOTT@book> @ a1.txt
PL/SQL procedure successfully completed.

SCOTT@book> select sql_id,PARSE_CALLS,executions,child_number from v$sql where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SQL_ID        PARSE_CALLS EXECUTIONS CHILD_NUMBER
------------- ----------- ---------- ------------
56gmvvxsuh8u9           7          7            0
35j79kxa30dh4           7          7            0
35j79kxa30dh4           1          1            1

SCOTT@book> select * from V$OPEN_CURSOR where sql_id in ('35j79kxa30dh4' ,'56gmvvxsuh8u9');
SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- --- --------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- --------------------
00000000854DC040 232 SCOTT     000000007C1AE3A0 1906844489 56gmvvxsuh8u9 begin   for i in 1 .. 1     loop       execute immediate 'se                                 OPEN
00000000854DC040 232 SCOTT     000000007D625090 1412445700 35j79kxa30dh4 select /*+ session_cache */ count(*) from emp                                                PL/SQL CURSOR CACHED

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2141045/,如需轉載,請註明出處,否則將追究法律責任。

相關文章