Session Cursor Caching - Part One

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:

1. Caching of the cursor in SQL*Plus
   1. 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.
   2. When the same SQL statement is issued in another session, it gets cached in that session’s cursor cache after the very first execution.
   3. 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.
   4. 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.
2. Caching of the cursor in PL/SQL Block
   1. 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.
   2. 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. Caching of the cursor in SQL*Plus
   1. 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 twice in the instance, is submitted in any session, its cursor gets cached in that session’s cache at the end of first execution.

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