Apr 12, 2017 4:38:41 PM by Anju Garg
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:
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.
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:
SYS> alter system set session_cached_cursors=0 scope=spfile;
startup force;
[oracle@node1 ~]$ sqlplus hr/hr
HR>alter session set session_cached_cursors = 10;
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
SYS>@search_sess_cache
no rows selected
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 202
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ---------------------------------------- -----------
HR 35 select /*+ session_cache */ count(*) fro OPEN
m hr.employees
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
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
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.
HR>conn hr/hr
alter session set session_cached_cursors=1;
Session altered.
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
...
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
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 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
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
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
. . .
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
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
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 */%'
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
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
SYS>alter system flush shared_pool;
HR>conn hr/hr
alter session set session_cached_cursors = 1;
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.
SYS>alter system flush shared_pool;
HR>conn hr/hr
alter session set session_cached_cursors = 1;
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;
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
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 */%';
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)') ;
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
Tags: Oracle
Written by Anju Garg
Anju Garg is an Oracle Ace with over 14 years of experience in IT Industry in various roles. Since 2010, she has been involved in teaching and has trained more than a hundred DBAs from across the world in various core DBA technologies like RAC, Data guard, Performance Tuning, SQL statement tuning, Database Administration etc.
She is a regular speaker at Sangam and OTNYathra. She writes articles about Oracle and is one of the reviewers of the following book published by Pearson Oracle Problem-Solving and Troubleshooting Handbook
She is certified for :
She is passionate about learning and has keen interest in RAC and Performance Tuning. She shares her knowledge via her technical blog at http://oracleinaction.com/
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.