Introduction
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.
How to Enable Session Cursor Caching?
To enable caching of session cursors, a positive integer value must be assigned to the initialization parameter SESSION_CACHED_CURSORS, which specifies the maximum number of session cursors to cache. This parameter also constrains the size of the PL/SQL cursor cache used by PL/SQL to avoid reparsing the statements re-submittted by a user.
In this first installment of this two-article series, we will explore this feature in more detail by means of following practical scenarios:
- Caching of the cursor in SQL*Plus
- A SQL statement that has not been previously executed in an instance, when repeatedly executed in a session, gets cached at the end of the third execution.
- When the same SQL statement is issued in another session, it gets cached in that session’s cursor cache after the very first execution.
- When the cursor of a SQL currently present in Library Cache is invalidated, the SQL statement needs to be executed three more times in order to get it cached in the session cursor cache.
- When the cursor of a SQL is flushed out of the library cache, the SQL statement needs to be executed three more times in order to get it cached in the session cursor cache.
- Caching of the cursor in PL/SQL Block
- 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.
- 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.
- Caching of the cursor in SQL*Plus
- A SQL statement that has not been previously executed in an instance, when repeatedly executed in a session, gets cached at the end of the third execution.
- Set parameter session_cached_cursors = 0 at the instance level
SYS> alter system set session_cached_cursors=0 scope=spfile;
startup force;
- Set parameter session_cached_cursors = 10 in a new HR session
[oracle@node1 ~]$ sqlplus hr/hr
HR>alter session set session_cached_cursors = 10;
- Find out initial parse and session cursor cache statistics for HR session
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
------------------------------ ---------- ---------- -------------- ----------
HR 35 13 CACHED_CURSORS 0
HR 35 13 CURSORS_HITS 0
HR 35 13 HARD PARSES 16
HR 35 13 PARSES 172
- Verify that currently no statement is cached in session cursor cache
SYS>@search_sess_cache
no rows selected
- Execute the following SQL for the first time in the session
HR>select /*+ session_cache */ count(*) from hr.employees;
- Verify that the SQL is hard parsed (hard parses incremented by 1) and as a result, its cursor is created in library cache. Currently, its cursor is not cached in the session cache.
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
------------------------------ ---------- ---------- -------------- ----------
HR 35 13 CACHED_CURSORS 0
HR 35 13 CURSORS_HITS 14
HR 35 13 HARD PARSES 17
HR 35 13 PARSES 202
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ---------------------------------------- -----------
HR 35 select /*+ session_cache */ count(*) fro OPEN
m hr.employees
- Execute the same SQL for the second time in the session. Note that although the number of parses has incremented by 1, hard parses remain the same, indicating that the statement has been soft parsed. Moreover, since CURSOR_HITS remain the same, the cursor has not been found in session cache. Rather it is searched and found in the library cache.
HR>select /*+ session_cache */ count(*) from hr.employees;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 13 CACHED_CURSORS 0
HR 35 13 CURSORS_HITS 14
HR 35 13 HARD PARSES 17
HR 35 13 PARSES 203
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---- ---------------------------------------- ---------------
HR 32 select /*+ session_cache */ count(*) fro OPEN
m hr.employees
- Execute the same SQL for the third time in the session. The statement has been soft parsed for the second time now. Again, the cursor has not been found in session cache.
HR>select /*+ session_cache */ count(*) from hr.employees;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 13 CACHED_CURSORS 0
HR 35 13 CURSORS_HITS 14
HR 35 13 HARD PARSES 17
HR 35 13 PARSES 204
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ---------------------------------------- -----------
HR 35 select /*+ session_cache */ count(*) fro OPEN
- Execute the SQL for the fourth time in the session. The statement has been soft parsed again but since CURSOR_HITS have incremented by 1, the cursor was found in the session cache. This indicates that when the statement was soft parsed for the second time, it was recognized as a repeatable statement and its cursor was cached in session cursor cache to improve the performance of subsequent soft parses. After the first cursor hit, CACHED_CURSORS is incremented by 1 and CURSOR_TYPE is also updated to indicate that the cursor is now cached in session cache.
HR>select /*+ session_cache */ count(*) from hr.employees;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 13 CACHED_CURSORS 1
HR 35 13 CURSORS_HITS 15
HR 35 13 HARD PARSES 17
HR 35 13 PARSES 205
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ---------------------------------------- -----------
HR 35 select /*+ session_cache */ count(*) fro DICTIONARY
m hr.employees LOOKUP CURS
OR CACHED
Note that the CURSOR_TYPE (in this instance) is displaying the status of the cursor incorrectly as "DICTIONARY LOOKUP CURSOR CACHED". I have confirmed from Asktom that it should be, in fact, “SESSION CURSOR CACHED”, as the cursor has already become session cached. However, CURSOR_TYPE will be correctly set to “SESSION CURSOR CACHED” on the next and subsequent executions in this session.
Hence, we have practically verifiedthat a SQL statement which has not been previously executed in an instance, when repeatedly executed in a session, gets cached at the end of the third execution.
b. When the same SQL statement is issued in another session, it gets cached in that session’s cursor cache after the very first execution.
- Start another HR session and set session_cached_cursors to 1 in that session also
HR>conn hr/hr
alter session set session_cached_cursors=1;
Session altered.
- It can be seen that the SQL 'select /*+ session_cache */ count(*) from hr.employees' is still cached in library cache
SYS>select sql_text, executions, invalidations from v$sql
where sql_text like '%/*+ session_cache */%';
SQL_TEXT EXECUTIONS
------------------------------ ----------
select /*+ session_cache */ co 4
unt(*) from hr.employees
...
- Initially no cursor is cached in this session's cursor cache
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 15 CACHED_CURSORS 0
HR 35 15 CURSORS_HITS 0
HR 35 15 HARD PARSES 1
HR 35 15 PARSES 15
- Let us execute the same SQL from this HR session for the first time.
HR>select /*+ session_cache */ count(*) from hr.employees;
- The statement is soft parsed since it is found in library cache. CURSOR_HITS are 0 since the cursor was not available in this session’s cursor cache at the beginning of the execution of the SQL
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 15 CACHED_CURSORS 1
HR 35 15 CURSORS_HITS 0
HR 35 15 HARD PARSES 1
HR 35 15 PARSES 16
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------------------------ -----------
HR 35 select /*+ session_cache */ co OPEN
unt(*) from hr.employees
- When the same SQL is executed from this HR session for the second time, CURSOR_HITS increase by 1, indicating that the cursor was found in the session cursor cache. It means that the cursor had been cached at the end of the first execution in this session.
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 15 CACHED_CURSORS 1
HR 35 15 CURSORS_HITS 1
HR 35 15 HARD PARSES 1
HR 35 15 PARSES 17
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------------------------ -----------
HR 35 select /*+ session_cache */ co DICTIONARY
unt(*) from hr.employees LOOKUP CURS
OR CACHED
Hence, we can say that if a statement whose cursor is already available in library cache, i.e. it has already been executed at least twicein the instance, is submitted in any session, its cursor gets cached in that session’s cache at the endof firstexecution.
c. When the cursor of the SQL in library cache is invalidated, the SQL statement needs to be executed at least three more times to get it cached in the session cursor cache
- Let us invalidate the cursor of the SQL in library cache by modifying the structure of the underlying table
SYS>alter table hr.employees modify (first_name varchar2(35));
Table altered.
SYS>col sql_text for a30
select sql_text, executions, invalidations from v$sql
where sql_text like '%/*+ session_cache */%';SYS> 2
SQL_TEXT EXECUTIONS INVALIDATIONS
------------------------------ ---------- -------------
select /*+ session_cache */ co 6 1
unt(*) from hr.employees
. . .
- Let us execute the same SQL from this HR session for the first time after invalidation. As expected, the statement is hard parsed.
HR>select /*+ session_cache */ count(*) from hr.employees;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 15 CACHED_CURSORS 0
HR 35 15 CURSORS_HITS 15
HR 35 15 HARD PARSES 2
HR 35 15 PARSES 27
- The SQL has to be executed two more times to get it into session cache so that on the fourth execution, the cursor hit takes place.
HR>select /*+ session_cache */ count(*) from hr.employees;
HR>select /*+ session_cache */ count(*) from hr.employees;
HR>select /*+ session_cache */ count(*) from hr.employees;
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------------------------ -----------
HR 35 select /*+ session_cache */ co DICTIONARY
unt(*) from hr.employees LOOKUP CURS
OR CACHED
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 15 CACHED_CURSORS 1
HR 35 15 CURSORS_HITS 16
HR 35 15 HARD PARSES 2
HR 35 15 PARSES 30
Hence, after the cursor of a SQL in library cache is invalidated, the SQL statement needs to be executed at least three more times to get it cached in the session cursor cache.
d. When the cursor of a SQL is flushed out of the library cache, the SQL statement needs to be executed three more times to get it cached in the session cursor cache
- Let us flush the shared pool so that the SQL is not there in library cache.
SYS>alter system flush shared_pool;
System altered.
SYS>select sql_text, executions from v$sql
where sql_text like '%/*+ session_cache */%';
SQL_TEXT EXECUTIONS INVALIDATIONS
------------------------------ ---------- -------------
select sql_text, execution 1 0
s, invalidations from v$sql
where sql_text like '%/*+ session_cache */%'
- Note that after the statement is executed three times in HR session, CACHED_CURSORS = 0
HR>select /*+ session_cache */ count(*) from hr.employees;
HR>select /*+ session_cache */ count(*) from hr.employees;
HR>select /*+ session_cache */ count(*) from hr.employees;
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------------------------ -----------
HR 35 select /*+ session_cache */ co OPEN
unt(*) from hr.employees
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 15 CACHED_CURSORS 0
HR 35 15 CURSORS_HITS 30
HR 35 15 HARD PARSES 3
HR 35 15 PARSES 63
- After the fourth execution, the cursor hit takes place, indicating that cursor was cached after the third execution.
HR>select /*+ session_cache */ count(*) from hr.employees;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 15 CACHED_CURSORS 1
HR 35 15 CURSORS_HITS 31
HR 35 15 HARD PARSES 3
HR 35 15 PARSES 64
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------------------------ -----------
HR 35 select /*+ session_cache */ co DICTIONARY
unt(*) from hr.employees LOOKUP CURS
OR CACHED
Hence, after the cursor of a SQL is flushed / aged out of the library cache, the SQL statement needs to be executed three more times in a session to get it cached in the session cursor cache.
II. Caching of the cursor in PL/SQL Block
As I mentioned earlier, SESSION_CACHED_CURSORS also constrains the size of the PL/SQL cursor cache used by PL/SQL to avoid parsing the statements re-submittted by a user. Let us verify it for both – an anonymous PL/SQL block and PL/SQl block inside a stored procedure.
a. Anonymous PL/SQL Block : The cursor of a SQL in an anonymous PL/SQL block gets cached in PL/SQL cache on the first execution itself
- Let us flush the shared pool and start another HR session with SESSION_CACHED_CURSORS = 1
SYS>alter system flush shared_pool;
HR>conn hr/hr
alter session set session_cached_cursors = 1;
- It can be seen that on executing an anonymous PL/SQL block in which the SQL is executed once, its cursor gets cached in session cache on the first execution.
HR> begin
for i in 1 .. 1
loop
execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
end loop;
end ;
/
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------------------------ -----------
HR 35 select /*+ session_cache */ co PL/SQL CURS
unt(*) from hr.employees OR CACHED
b. PL/SQL Stored Procedure: The cursor of a SQL inside the PL/SQL block in a PL/SQL Stored Procedure gets cached in session cache on the first execution.
- Let us flush the shared pool and start another HR session with SESSION_CACHED_CURSORS = 1
SYS>alter system flush shared_pool;
HR>conn hr/hr
alter session set session_cached_cursors = 1;
- Create a stored procedure sess_cache in which the SQL is executed once
HR>create or replace procedure sess_cache as
begin
for i in 1 .. 1
loop
execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
end loop;
end ;
/
HR>alter procedure sess_cache compile;
- It can be seen that cursor of the SQL inside the PL/SQL block in a PL/SQL Stored Procedure gets cached in session cache on the first execution
HR> exec sess_cache;
SYS> @search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------------------------ -----------
HR 35 select /*+ session_cache */ co PL/SQL CURS
unt(*) from hr.employees OR CACHED
Summary
- Session cursor caching
- Avoids syntax and semantics check
- Can avoid soft parsing if the cached cursor is in an open state
- Greatly reduces the cost of soft parse by cutting down on latch use and waits
- 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.
- 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.
- When a SQL statement that has not been previously executed in an instance is repeatedly executed in a SQL*Plus session, the cursor gets cached at the end of the third execution.
- When the same SQL statement is issued in another SQL*Plus session, ithe cursor gets cached in that session’s cursor cache at the end of the first execution itself.
- When cursor of a SQL present in Library Cache is invalidated or flushed out of the library cache, the SQL statement needs to be executed in a SQL*Plus session three more times to get the cursor cached in the session cursor cache.
- The cursor of a SQL inside a PL/SQL block, whether anonymous or part of a PL/SQL Stored Procedure, gets cached in the PL/SQL cache on the first execution.
Scripts used:
-
search_sess_cache.sql
Find out type of the cursor cached in the HR session
col cursor_type for a15
col sid for 999
col sql_text for a40
select s.username, c.sid, c.sql_text, cursor_type
from v$open_cursor c, v$session s
where s.username='HR' and
c.sid = s.SID
and c.sql_text like '%/*+ session_cache */%';
- sesstat.sql
Find out parse and session cursor cache statistics for HR session
col username for a10
select s.username, s.sid, s.serial#, 'CACHED_CURSORS'
Statistic,
a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.username='HR'
and b.name in ('session cursor cache count')
union
select s.username, s.sid, s.serial#, 'CURSORS_HITS',
a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.username='HR'
and b.name in ( 'session cursor cache hits')
union
select s.username, s.sid, s.serial#, 'PARSES',
a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.username='HR'
and b.name in ( 'parse count (total)')
union
select s.username, s.sid, s.serial#, 'HARD PARSES',
a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.username='HR'
and b.name in ( 'parse count (hard)') ;
- sql_stats.sql
FInd out parsing and execution statistics for the SQL statement
select sql_text, parse_calls, executions from V$sql
where sql_text like '%/*+ session_cache */%'
In the second and final article in this series, we will learn the impact of session cursor caching on parsing using SQL*Plus and PL/SQL using a practical scenario.
References:
Oracle Core: Essential Internals for DBAs and Developers by Jonathan Lewis
http://www.orafaq.com/node/758
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:865497961356
https://hoopercharles.wordpress.com/2011/07/21/session_cached_cursors-possibly-interesting-details/
https://dioncho.wordpress.com/2009/03/13/the-secret-of-session_cached_cursors/
https://jonathanlewis.wordpress.com/2013/03/27/open-cursors/
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4621380200346754746
Start the discussion at forums.toadworld.com