[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