Toad World Blog

Session Cursor Caching - Part Two

May 11, 2017 1:52:37 PM by Anju Garg

In first part of this article, we

  • Learnt
    • What is ‘Session Cursor Caching’?
    • How to Enable Session Cursor Caching?
  • Explored ‘Session Cursor Caching’ in detail by means of following practical scenarios:
    • Caching of the cursor in SQL*Plus
    • Caching of the cursor in PL/SQL Block

In this second and final part of this article, we will find out the impact of session cursor caching on parsing when a SQL statement is issued using

  • SQL*Plus
  • PL/SQL
    • Anonymous Block
    • Stored Procedure

We will also learn how to monitor and use SESSION_CACHED_CURSORS for tuning.

I.            Impact of session cursor caching on parsing

We already know that when cursor of a SQL statement gets cached in session cache, it greatly reduces the cost of soft parsing. Next we will try to find out whether session cursor caching reduces the number of soft parses as well:

    • Using SQL*Plus
    • Using Anonymous PL/SQL Block
    • Using PL/SQL Stored Procedure

 

a)          Impact of session cursor caching on parsing using SQL*Plus

We will execute a SQL statement 100 times in a SQL*Plus session and try to find out the number of times it is parsed when session cursor caching is disabled / enabled.

Case-I: Session Cursor Caching disabled

  • Flush shared pool and start an HR session with SESSION_CACHED_CURSORS=0
SYS>alter system flush shared_pool;
HR>conn hr/hr
   alter session set session_cached_cursors=0
 
   sho parameter cached
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     0
  • After hard parsing the statement, check initial parsing statistics for the SQL statement and HR session
HR>select /*+ session_cache */ count(*) from hr.employees;
 
SYS>select sql_text, parse_calls, executions from V$sql
    where sql_text like '%/*+ session_cache */%';
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select /*+ session_cache */ count(*) from hr.emplo           1          1
yees
 
 . . .
 
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         61 CACHED_CURSORS          0
HR                 35         61 CURSORS_HITS            0
HR                 35         61 HARD PARSES            65
HR                 35         61 PARSES                488
  • Execute the statement 100 times in SQL*Plus session and observe that the statement is soft parsed (searched in library cache) as many times (100) as it is executed.
HR>@exec_sql
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         61 CACHED_CURSORS          0
HR                 35         61 CURSORS_HITS            0
HR                 35         61 HARD PARSES            65
HR                 35         61 PARSES                588
 
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select /*+ session_cache */ count(*) from hr.emplo         101        101
yees
    select sql_text, parse_calls, executions from            1          1
V$sql     where sql_text like '%/*+ session_cache
*/%'

Case-II: Session Cursor Caching enabled

  • Now, we will enable session cursor caching in a new HR session by setting SESSION_CACHED_CURSORS=1
SYS>alter system flush shared_pool;
HR>conn hr/hr
 
   alter session set session_cached_cursors = 1;
  • Execute the SQL statement 4 times in the session so that it gets cached in session cache.
HR>select /*+ session_cache */ count(*) from hr.employees;
   select /*+ session_cache */ count(*) from hr.employees;
   select /*+ session_cache */ count(*) from hr.employees;
   select /*+ session_cache */ count(*) from hr.employees;
 
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
  • Find out initial parse statistics for the SQL and HR session
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select /*+ session_cache */ count(*) from hr.emplo           4          4
yees
 
. . .
 
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         65 CACHED_CURSORS          1
HR                 35         65 CURSORS_HITS           15
HR                 35         65 HARD PARSES            24
HR                 35         65 PARSES                162
  • Execute the statement 100 times in a SQL*Plus HR session and observe that after the statement is found in session cache 100 times (CURSORS_HITS  =  115): the cursor’s presence and validity is checked in library cache (soft parsed) as many times (100) as it is executed.
HR>@exec_sql
 
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         65 CACHED_CURSORS          1
HR                 35         65 CURSORS_HITS          115
HR                 35         65 HARD PARSES            24
HR                 35         65 PARSES                262
 
 
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select /*+ session_cache */ count(*) from hr.emplo         104        104
yees

Hence, we have observed that if a SQL statement is repeatedly executed in a SQL*Plus session, it is parsed as many times as it is executed even if session cursor caching is enabled,. In other words, SQL*Plus

  • Makes the soft parse a little less costly by avoiding syntax and semantics checks
  • Cannot reduce parsing since for every execution; even if the statement is found in session cache, the presence and validity of its cursor in library cache needs to be confirmed.
  • Is a simple command-line tool to run ad hoc SQL without the ability to cache the cursor handle.

Since SQL*Plus cannot cache the cursor handles, it is not designed to scale and hence is not really a programming environment to write database applications in. Now, we will explore PL/SQL, which can cache the cursor handles in PL/SQL cache and provides an environment in whichto code database applications.  We will explore the impact of session cursor caching on parsing the SQL statements issued using anonymous PL/SQL block and PL/SQL stored procedure.

b)            Impact of session cursor caching on parsing using anonymous PL/SQL Block

Now, we will issue the same SQL statement 100 times using an anonymous PL/SQL block and observe the number of times it is parsed when session cursor caching is disabled / enabled.

 

Case-I: Session Cursor Caching disabled.

  • Flush shared pool and start an HR session with SESSION_CACHED_CURSORS=0

SYS>alter system flush shared_pool;

HR>conn hr/hr
   alter session set session_cached_cursors=0
 
 
   sho parameter cached
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     0
  • Execute an anonymous PL/SQL block in which the SQL is executed 100 times. Get parsing statistics for PL/SQL, SQL and HR session. Observe that since session cursor caching is disabled, SQL is parsed as many times (100) as it is executed.
HR>  begin
  
         for i in 1 .. 100
        loop
              execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
       end loop;
   end ;
   /
 
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         51 CACHED_CURSORS          0
HR                 35         51 CURSORS_HITS            0
HR                 35         51 HARD PARSES            61
HR                 35         51 PARSES                488
 
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
    select sql_text, parse_calls, executions from            1          1
V$sql     where sql_text like '%/*+ session_cache
*/%'
 
 begin           for i in 1 .. 100         loop              1          1
            execute immediate 'select /*+ session_
cache */ count(*) from hr.employees' ;        end
loop;    end ;
 
select /*+ session_cache */ count(*) from hr.emplo         100        100
yees
  • Execute the anonymous PL/SQL block a second time and observe that the PL/SQL block is hard parsed since it is anonymous. Also, SQL is again parsed as many times (100) as it is executed.
HR>  begin
  
         for i in 1 .. 100
        loop
              execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
       end loop;
   end ;
   /
 
SYS>@sesstat
 
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         51 CACHED_CURSORS          0
HR                 35         51 CURSORS_HITS            0
HR                 35         51 HARD PARSES            62
HR                 35         51 PARSES                589

SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
    select sql_text, parse_calls, executions from            2          2
V$sql     where sql_text like '%/*+ session_cache
*/%'
 
 begin           for i in 1 .. 100         loop              2          2
            execute immediate 'select /*+ session_
cache */ count(*) from hr.employees' ;        end
loop;    end ;
 
select /*+ session_cache */ count(*) from hr.emplo         200        200
yees

Hence, when session cursor caching is disabled and a SQL statement is executed multiple times within an anonymous PL/SQL block, the statement is parsed every time it is executed.

 

Case-II: Session Cursor Caching enabled.

  • Now, we will start a new HR session and set SESSION_CACHED_CURSORS=1 so that PL/SQL cache can now keep one cursor open.
SYS>alter system flush shared_pool;

HR>conn hr/hr

   alter session set session_cached_cursors = 1;
  • Execute the same anonymous PL/SQL block in this session.
HR>  begin
  
         for i in 1 .. 100
        loop
              execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
       end loop;
   end ;
   /
  • Capture parsing and cursor statistics for the SQL and HR session. Note that for 100 executions of the SQL statement, it has been parsed only once since its cursor handle got cached in PL/SQL cache after the first execution of the SQL and kept open thereafter.
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         53 CACHED_CURSORS          0
HR                 35         53 CURSORS_HITS          113
HR                 35         53 HARD PARSES            61
HR                 35         53 PARSES                375
 
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
begin           for i in 1 .. 100         loop               1          1
           execute immediate 'select /*+ session_c
ache */ count(*) from hr.employees' ;        end l
oop;    end ;
 
    select sql_text, parse_calls, executions from            1          1
V$sql     where sql_text like '%/*+ session_cache
*/%'
 
select /*+ session_cache */ count(*) from hr.emplo           1        100
yees
  • Execute the anonymous PL/SQL block once again and note that the cursor for the SQL is found in the PL/SQL cache for all 100 executions (CURSORS_HITS = 213). Note that, since the cursor of the SQL was closed after the previous execution of the PL/SQL block, it is reopened on the first execution of the SQL this time, thereby causing a soft parse. The subsequent 99 parse calls find the cursor in the cache and do not need to reopen the cursor, so that the SQL statement is parsed only once for all the 100 executions.
HR>  begin
  
         for i in 1 .. 100
        loop
              execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
       end loop;
   end ;
   /
 
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         53 CACHED_CURSORS          0
HR                 35         53 CURSORS_HITS          213
HR                 35         53 HARD PARSES            61
HR                 35         53 PARSES                377
 
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
begin           for i in 1 .. 100         loop               2          2
           execute immediate 'select /*+ session_c
ache */ count(*) from hr.employees' ;        end l
oop;    end ;
 
    select sql_text, parse_calls, executions from            2          2
V$sql     where sql_text like '%/*+ session_cache
*/%'
 
select /*+ session_cache */ count(*) from hr.emplo           2        200
yees

Hence, if a SQL statement is executed multiple times within an anonymous PL/SQL block with session cursor caching enabled, the statement is parsed once per execution of the block; i.e., if above anonymous PL/SQL block is executed 5 times, while the SQL statement will be executed 500 times, it will be parsed only 5 times.  During an execution of an anonymous PL/SQL block in which a SQL is repeatedly executed, the cursor handle of the SQL gets cached after its first execution and kept open thereafter so that subsequent parse calls do not need to reopen it; i.e. ,no more parses. After the block has been executed, the cursor of the SQL is closed so that the next execution of the block requires library cache to be visited to confirm the presence and validity of the cursor at the location pointed to by the pointer in session cache, thereby causing soft parse.

c)            Impact of session cursor caching on parsing using PL/SQL Stored Procedure

Now, we will issue the same SQL statement 100 times from within a PL/SQL block in a stored procedure and observe the number of times it is parsed when session cursor caching is disabled / enabled.

 

Case-I: Session Cursor Caching disabled.

  • Flush shared pool and start an HR session. Create a stored PL/SQL procedure sess_cache in which the SQL is executed 100 times. Set SESSION_CACHED_CURSORS=0 in HR session.
SYS>alter system flush shared_pool;
 
HR>conn hr/hr
 
   create or replace procedure sess_cache as
begin
  
         for i in 1 .. 100
        loop
              execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
       end loop;
   end ;
   /
 
   alter procedure sess_cache compile;
 
 
   alter session set session_cached_cursors = 0;
 
   sho parameter cached
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     0
  • Execute the procedure sess_cache.  Get initial parsing statistics for PL/SQL, SQL and HR session. Note that the SQL is parsed as many times as it is executed since session cursor caching is not enabled.
HR>   exec sess_cache;
 
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         71 CACHED_CURSORS          0
HR                 35         71 CURSORS_HITS            0
HR                 35         71 HARD PARSES           179
HR                 35         71 PARSES               1255
 
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
    select sql_text, parse_calls, executions from            1          1
V$sql     where sql_text like '%/*+ session_cache
*/%'
 
select /*+ session_cache */ count(*) from hr.emplo         100        100
yees 
  • Execute the procedure a second time and observe that  the SQL is parsed as many times (100) as it is executed. The PL/SQL block is soft parsed since it is part of a stored procedure.
HR>   exec sess_cache;
 
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         71 CACHED_CURSORS          0
HR                 35         71 CURSORS_HITS            0
HR                 35         71 HARD PARSES           179
HR                 35         71 PARSES               1356
 
 
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
    select sql_text, parse_calls, executions from            2          2
V$sql     where sql_text like '%/*+ session_cache
*/%'
 
select /*+ session_cache */ count(*) from hr.emplo         200        200
yees

Hence, when session cursor caching is disabled and a SQL statement is executed multiple times from within a PL/SQL block in a stored procedure, the statement is parsed every time it is executed.

 

Case-II: Session Cursor Caching enabled.

  • Now, we will start a new HR session and set SESSION_CACHED_CURSORS=1 so that PL/SQL cache can now keep one cursor open.  
SYS>alter system flush shared_pool;
HR>conn hr/hr
 
   create or replace procedure sess_cache as
begin
  
         for i in 1 .. 100
        loop
              execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
       end loop;
   end ;
   /
 
   alter procedure sess_cache compile;

   alter session set session_cached_cursors = 1;
  • Execute the PL/SQL stored procedure for the first time in this session. Note that the SQL is parsed once for 100 executions since its cursor gets cached in PL/SQL cache after the first execution.

HR>   exec sess_cache;

 

SYS>@sesstat

 

USERNAME          SID    SERIAL# STATISTIC           VALUE

---------- ---------- ---------- -------------- ----------

HR                 35         73 CACHED_CURSORS          0

HR                 35         73 CURSORS_HITS          113

HR                 35         73 HARD PARSES           178

HR                 35         73 PARSES               1138

 

SYS>@sql_stats

 

SQL_TEXT                                           PARSE_CALLS EXECUTIONS

-------------------------------------------------- ----------- ----------

    select sql_text, parse_calls, executions from            1          1

V$sql     where sql_text like '%/*+ session_cache

*/%'

 

select /*+ session_cache */ count(*) from hr.emplo           1        100

yees

 

  •      Execute the stored procedure a second time. Observe that the cursor for the SQL is found in the session cache for all the 100 executions (CURSORS_HITS = 213).  Also, PARSE_CALLS remain at the same value (1) as earlier, since the cursor cached in PL/SQL cache is kept open even after execution of the procedure is over so the next execution does not require reopening it and a soft parse is avoided.
HR>   exec sess_cache;
 
SYS>@sesstat
 
USERNAME          SID    SERIAL# STATISTIC           VALUE
---------- ---------- ---------- -------------- ----------
HR                 35         73 CACHED_CURSORS          0
HR                 35         73 CURSORS_HITS          213
HR                 35         73 HARD PARSES           178
HR                 35         73 PARSES               1139
 
SYS>@sql_stats
 
SQL_TEXT                                           PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
    select sql_text, parse_calls, executions from            2          2
V$sql     where sql_text like '%/*+ session_cache
*/%'

select /*+ session_cache */ count(*) from hr.emplo           1        200
yees

Hence, if a SQL statement is executed multiple times from within a PL/SQL block in a stored procedure with session cursor caching enabled, the statement is parsed once per session for any number of executions of the procedure; i.e., if the above PL/SQL procedure is executed 5 times, while the SQL statement will be executed 500 times, it will be parsed only once. During an execution of a stored procedure in which a SQL is repeatedly executed, the cursor handle of the SQL gets cached after its first execution and kept open for the life of the session; i.e., even after the procedure has been executed, the cursor of the SQL is kept open. As a result, as long as the cursor is cached in PL/SQL cache, subsequent executions of the procedure need not reopen it, thereby avoiding any more soft parses.

 II.            Monitor and use SESSION_CACHED_CURSORS for tuning

It is evident that, whereas session cursor caching will not reduce hard parses, it can

  • Make soft parses less costly
  • Reduce the number of soft parses (only if PL/SQL is used)

In other words, session cursor caching can improve performance only when same SQL statements are resubmitted. This can occur in many applications including FORMS based applications where users often switch between forms. Every time a user switches to a new form, all the SQL statements opened for the old form will be closed. The SESSION_CACHED_CURSORS parameter will cause closed cursors to be cached within the session so that a subsequent call to parse the statement will bypass the parse phase.

Hence, we should start by finding out whether the application executes the same SQL statements repeatedly.  For this, we need to look at the ‘SQL ordered by Parse Calls’ section of the AWR / Statspack report.  If the parse to execute ratio of many of the SQL statements / procedures is found to be close to 1, increasing SESSION_CACHED_CURSORS might help.

Once we know that the application is submitting same SQL statements repeatedly, next, we need to know if the current value of SESSION_CACHED_CURSORS is appropriate.

If SESSION_CACHED_CURSORS is low, it is quite probable that the cursor for a SQL has aged out of session cache by the time same it is resubmitted, resulting in low session cursor cache hits. We can look at following sections of the AWR report:

  • Instance Efficiencies: A low value of Execute to Parse % ratio in the AWR report indicates that the SQL is being parsed most of the time when it is executed.  This could be because of either or both of the following:
    • The SQL is being issued repeatedly from SQL*Plus. It would be advisable to issue such SQL statements using the PL/SQL stored procedure.
    • The current value of the parameter SESSION_CACHED_CURSORS is insufficient, causing the aging out of cursors from session cache. If this is the case, you may benefit by increasing the the value of SESSION_CACHED_CURSORS.
  • Instance Activity Statistics: If the statistic session cursor cache hits  << ( parse count (total)  -  parse count (hard) ) it indicates that very few soft parses are finding the cursor in session cache, which might be because of  cursors getting  aged out of session cache due to insufficient  size of the cache. Increasing the value of SESSION_CACHED_CURSORS might be helpful.

Moreover, we can also issue following query against v$sysstat to compare session cursor cache hits with soft parses in the instance since startup.

SQL>select cach.value cache_hits, prs.value all_parses, hprs.value hard_parses,
            prs.value - hprs.value Soft_parses
     from v$sysstat cach, v$sysstat prs, v$sysstat hprs,
          v$statname nm1, v$statname nm2, v$statname nm3
     where cach.statistic# = nm1.statistic# 
       and nm1.name = 'session cursor cache hits'
       and prs.statistic#=nm2.statistic#
       and nm2.name= 'parse count (total)'
       and hprs.statistic#=nm3.statistic#
       and nm3.name= 'parse count (hard)' ;
 
CACHE_HITS ALL_PARSES HARD_PARSES SOFT_PARSES
---------- ---------- ----------- -----------
     64139      49182        2547       46635

If CACHE_HITS << SOFT_PARSES, it may indicate that cursors are not getting cached in the session as size of the cache is insufficient.

Since SESSION_CACHED_CURSORS can also be set at the session level, we next need to find out if the value is appropriate for the sessions that are caching a large number of cursors. For this, we will monitor the statistic "session cursor cache count" for all the sessions to identify such sessions.

SQL> select  s.sid, s.username,  a.value "session cursor cache count"
     from v$sesstat a, v$statname b, v$session s
  where a.statistic# = b.statistic#  and s.sid=a.sid
  and b.name = 'session cursor cache count'
  and s.username is not null
  order by a.value desc;

 SID USERNAME        session cursor cache count
---- --------------- --------------------------
  38 HR                                      50
  23 SYS                                      1
  29 HR                                       1

Here, it can be seen that the HR session with SID 38 is caching the highest number of cursors. Now, we would like to find out whether the setting of parameter SESSION_CACHED_CURSORS is appropriate in such sessions. We will monitor the statistic 'session cursor cache count' for such sessions in concurrence with the corresponding setting of the SESSION_CACHED_CURSORS parameter and the statistic 'session cursor cache hits’.

SQL>select sess.username usr, sess.sid , hits.value CACHE_HITS,
           cached.value curr_cached, par.value max_cacheable,
           prs.value all_parses, hprs.value hard_parses,
        prs.value - hprs.value Soft_parses
from    v$session sess, v$parameter2 par,
        v$sesstat cached,  v$statname cached_stat,
        v$sesstat hits, v$statname hits_stat,
        v$sesstat prs, v$statname prs_stat,
        v$sesstat hprs, v$statname hprs_stat
where sess.sid = &sid
  and par.name = 'session_cached_cursors'
  and hits.statistic# = hits_stat.statistic#
  and hits_stat.name = 'session cursor cache hits'
  and hits.sid=sess.sid
  and cached.statistic# = cached_stat.statistic#
  and cached_stat.name = 'session cursor cache count'
  and cached.sid=sess.sid
  and prs.statistic# = prs_stat.statistic#
  and prs_stat.name = 'parse count (total)'
  and prs.sid=sess.sid
  and hprs.statistic# = hprs_stat.statistic#
  and hprs_stat.name = 'parse count (hard)'
  and hprs.sid=sess.sid;
  
Enter value for sid: 38
old   9: where sess.sid = &sid
new   9: where sess.sid = 38
 
USR   SID CACHE_HITS CURR_CACHED MAX_CACHEABLE   ALL_PARSES HARD_PARSES SOFT_PARSES
---- ---- ---------- ----------- --------------- ---------- ----------- -----------
HR     38        105          50 50                     172          45        127

If CURR_CACHED is close to MAX_CACHEABLE (SESSION_CACHED_CURSORS parameter) and CACHE_HITS is low compared to SOFT_PARSES, it may indicate that even though the session is using the session cache to the fullest, cache hits are low because cached cursors are getting aged out because of insufficient size of the cache. In such cases, increasing the SESSION_CACHED_CURSORS parameter for the session may reduce latch contention and improve performance. 

Note that CACHE_HITS  could also be low because your application is not submitting the same queries for parsing repeatedly. In such cases, even if CURR_CACHED is close to MAX_CACHEABLE, caching cursors by session won't help at all. Hence, before increasing the setting of SESSION_CACHED_CURSORS, it is advisable to look at the ‘SQL ordered by Parse Calls’ section of AWR report to confirm the presence of the PL/SQL procedure(s) with ‘Parse Calls’ close to ‘Executions’;  i.e., you are parsing almost for every execute.

Once we know that the parameter SESSION_CACHED_CURSORS needs to be increased, we should consider that it can be modified both at the session and the instance level. Also, modifying it at the instance level will require instance restart as the parameter is static. If repeated executions are from specific sessions, the parameter should be increased for those sessions only, as cursor caching increases the amount of memory consumed by a session.

First, issue the following query to find out the highest number of cursors cached across all the sessions.

SQL> select max(value) from v$sesstat
 where STATISTIC# in (select STATISTIC# from v$statname
                      where name='sessioncursor cache count');
 
MAX(VALUE)
----------
        50

If this value equals the instance parameter SESSION_CACHED_CURSORS, you should consider increasing it.

SQL> select NAME, value from v$parameter where name = 'session_cached_cursors';
 
NAME                           VALUE
------------------------------ ----------
session_cached_cursors         50

It is worth mentioning that oversized cursor cache

  • increases the amount of memory consumed by a session
  • causes more time to be spent in searching the larger cache

Hence, if the highest number of cursors cached across all the sessions <<  SESSION_CACHED_CURSORS,  the parameter may be reduced.

To monitor the benefit of increasing SESSION_CACHED_CURSORS, we can look in the AWR report at

  • The 'session cursor cache hits' statistic in the 'Instance Activity Statistics' section
  • The ‘execute to parse ratio %’ statistic in the 'Instance efficiencies' section

If both these statistics increase, it indicates that the repeated SQL being submitted from PL/SQL is able to take advantage of the increased value of SESSION_CACHED_CURSORS. If these statistics do not register a significant increase, it is advisable to switch to a lower value of the parameter.

 

Summary

  • SQL*Plus
    • Makes soft parse a little less costly
    • Cannot reduce parsing,since for every execution, even if the statement is found in session cache, the presence and validity of its cursor in library cache needs to be confirmed.
    • Is a simple command-line tool to run ad hoc SQL without the ability to cache the cursor handle.
    • PL/SQL can cache cursor handles in PL/SQL cache and hence reduce parsing.
    • During an execution of an anonymous PL/SQL block with repeatedly submitted SQL, if session cursor caching is enabled, the cursor handle of the SQL gets cached after its first execution and is kept open thereafter, so that subsequent parse calls do not need to reopen it. After the block has been executed, the cursor of the SQL is closed.  On the next execution of the block, even if the cursor is still cached, the library cache needs to be visited to confirm the presence and validity of the cursor at the location it points to, thereby causing soft parse.
    • During an execution of a stored procedure in which a SQL is repeatedly executed, if session cursor caching is enabled, the cursor handle of the SQL gets cached after its first execution and is kept open for the life of the session; i.e., even after the procedure has been executed, the cursor of the SQL is kept open. As a result, as long as the cursor is cached in PL/SQL cache, subsequent executions of the procedure in the session need not reopen it. thereby avoiding any more soft parses.
    • Session cursor caching can
      • Improve performance only when same SQL statements are resubmitted.
      • Reduce parsing only when same SQL statements are resubmitted using PL/SQL.

 

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 the 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 */%'
  • exec_sql.sql

 Issue the SQL statement 100 times using SQL*Plus

Contains following statement repeated 100 times

select /*+ session_cache */ count(*) from hr.employees;

 

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

Anju Garg

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 :

  • Oracle 9i Database Administration OCP
  • Oracle 11g Database Administration OCP
  • Oracle 11g Performance Tuning OCE
  • Oracle 11g R2 RAC OCE
  • Oracle 11g SQL Tuning OCE
  • Oracle 12c Database Administration OCP
  • Oracle Real Application Clusters 12c Certified Implementation Specialist

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/