Oracle -- Open Cursor & Session Cached Cursors

 

This article mainly covers  importance of SESSION_CACHED_CURSORS on performance and  open cursors  .   Apart from SESSION_CACHED_CURSORS ,   other parameters that control   open cursors in database are cursor_sharing and  how developers close cursor in pl/sql block  .

OPEN_CURSORS 
specifies the maximum number of open cursors (handles to private SQL areas) a
session can have at once. You can use this parameter to prevent a session from opening an excessive
number of cursors. This parameter also constrains the size of the PL/SQL cursor cache, which PL/SQL
uses to avoid having to reparse as statements are re-executed by a user. 

SESSION_CACHED_CURSORS 
lets you specify the number of session cursors to cache. Repeated
parse calls of the same SQL statement cause the session cursor for that statement to be moved into the
session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen
the cursor. 

If the session cursor cache count is maxed out, session_cursor_cache_hits is low compared to all parses, and you suspect that the application is re-submitting the same queries for parsing repeatedly, then increasing SESSION_CURSOR_CACHE_COUNT may help with latch contention and give a slight boost to performance. Note that if your application is not resubmitting the same queries for parsing repeatedly, then session_cursor_cache_hits will be low and the session cursor cache count may be maxed out, but caching cursors by session will not help at all. For example, if your application is using a lot of unsharable SQL, raising this parameter will not help

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

Handling Cursor from Pl/SQL side Using Precompiler Option RELEASE_CURSOR=YES 

Well this is  totally different  topic altogether hence not sharing too much information on this. However  just to give  direction to developer on cursor handling can be done at coding side , 

Find out the session that is causing the error by using the following SQL statement:

select sid ,  count(*)  from v$open_cursor  group by sid order by 2  ;

select sql_id ,  count(*)  from v$open_cursor where sid=193  group by sql_id  ;

col hwm_open_cur format 99,999

col max_open_cur format 99,999

select 

   max(a.value) as hwm_open_cur, 

   p.value      as max_open_cur

from 

   v$sesstat a, 

   v$statname b, 

   v$parameter p

where 

   a.statistic# = b.statistic# 

and 

   b.name = 'opened cursors current'

and 

   p.name= 'open_cursors'

group by p.value;

select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null;

select  sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME from v$open_cursor where sid in ($SID);

SELECT  max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE  a.statistic# = b.statistic#  and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;

-- cursor open for  each session and its address 

SELECT C.SID AS "OraSID",
 C.ADDRESS || ':' || C.HASH_VALUE AS "SQL Address",
 COUNT(C.SADDR) AS "Cursor Copies"
 FROM V$OPEN_CURSOR C
GROUP BY C.SID, C.ADDRESS || ':' || C.HASH_VALUE
HAVING COUNT(C.SADDR) > 2
ORDER BY 3 DESC
/ 

--  open cursor value and  its usage 

SELECT
 'session_cached_cursors' parameter,
 LPAD(value, 5) value,
 DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') ||
'%') usage
FROM
 (SELECT
 MAX(s.value) used
 FROM
 v$statname n,
 v$sesstat s
 WHERE
 n.name = 'session cursor cache count' and
 s.statistic# = n.statistic#
 ),
 (SELECT
 value
 FROM
 v$parameter
 WHERE
 name = 'session_cached_cursors'
 )
UNION ALL
SELECT
 'open_cursors',
 LPAD(value, 5),
 to_char(100 * used / value, '990') || '%'
FROM
 (SELECT
 MAX(sum(s.value)) used
 FROM
 v$statname n,
 v$sesstat s
 WHERE
 n.name in ('opened cursors current', 'session cursor cache
count') and
 s.statistic# = n.statistic#
 GROUP BY
 s.sid
 ),
 (SELECT
 value
 FROM
 v$parameter
 WHERE
 name = 'open_cursors'
 )
/ 

-- Cursor details for particular session 

SELECT b.SID, UPPER(a.NAME), b.VALUE
 FROM v$statname a, v$sesstat b, v$session c
 WHERE a.statistic# = b.statistic#
 AND c.SID = b.SID
 AND LOWER(a.NAME) LIKE '%' || LOWER('CURSOR')||'%' 
 AND b.SID=20
UNION
SELECT SID, 'v$open_cursor opened cursor', COUNT(*)
FROM v$open_cursor
WHERE SID=&sid
GROUP BY SID
ORDER BY SID
/

 SELECT a.value curr_cached, p.value max_cached,
s.username, s.sid, s.serial#
 FROM v$sesstat a, v$statname b, v$session s, v$parameter2 p
 WHERE a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid
 AND p.name='session_cached_cursors'
 AND b.name = 'session cursor cache count';

-- CHECKING CACHE CURSOR FOR SESSIONS 

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='HAFEEZ' 
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='HAFEEZ' 
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='HAFEEZ' 
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='HAFEEZ' 
and b.name in ( 'parse count (hard)') ;

-- CHECKING DETAIL OF PARTICULAR SQL ID 

SELECT
  CHILD_NUMBER CN,
  NAME,
  VALUE,
  ISDEFAULT DEF
FROM
  V$SQL_OPTIMIZER_ENV
WHERE
  SQL_ID='5ngzsfstg8tmy'
  AND CHILD_NUMBER in (0,2,12)
ORDER BY
  NAME,
  CHILD_NUMBER;

SELECT
  CHILD_NUMBER CN,
  PARSING_SCHEMA_NAME,
  OPTIMIZER_ENV_HASH_VALUE OPTIMIZER_ENV,
  INVALIDATIONS,
  PARSE_CALLS,
  IS_OBSOLETE,
  FIRST_LOAD_TIME,
  TO_CHAR(LAST_ACTIVE_TIME,'YYYY-MM-DD/HH24:MI:SS') LAST_ACTIVE_TIME
FROM
  V$SQL
WHERE
  SQL_ID='5ngzsfstg8tmy';

It will provide highest count of open cursor that opened by a session currently. 

========================================================== 

col open_cur_configured form 9999999999

col max_open_cur form a15

select 

max(a.value) as highest_open_cur,

p.value as max_open_cur

from v$sesstat a, 

v$statname b, 

v$parameter p

where a.statistic# = b.statistic# 

and b.name = 'opened cursors current'

and p.name= 'open_cursors'

group by p.value;

Cursor usage per user:

======================

select 

sum(a.value) total_cur, 

max(a.value) max_cur, 

s.username

from v$sesstat a, 

v$statname b,

v$session s 

where a.statistic# = b.statistic# 

and s.sid=a.sid 

and b.name = 'opened cursors current' 

group by s.username

order by 2 desc;

Cursor usage per session:

=========================

select 

s.sid,

s.serial#,

s.username,

a.value cur_count

from 

v$sesstat a,

v$statname b,

v$session s

where a.statistic# = b.statistic# 

and s.sid = a.sid

and b.name = 'opened cursors current' 

order by 4 desc ;

historical open cursor usage:

============================= 

select to_char(b.begin_interval_time,'DD-MON-YY HH24:MI') begin_time,a.instance_number,a.value Open_cursor from dba_hist_sysstat a, dba_hist_snapshot b 

where a.stat_name='opened cursors current' 

and to_char(b.begin_interval_time,'DD-MON-YY HH24:MI') like '%14-OCT-19%'

and a.snap_id=b.snap_id 

and a.instance_number=b.instance_number 

order by 2,1;

To get the % useage of all available open cursor 

======================================================

SELECT 'session_cached_cursors' parameter, LPAD(value, 5) value, 

DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') || '%' ) usage 

FROM ( SELECT MAX(s.value) used 

FROM v$statname n, v$sesstat s 

WHERE 

n.name = 'session cursor cache count' and 

s.statistic# = n.statistic# ), 

(SELECT value 

FROM v$parameter 

WHERE 

name = 'session_cached_cursors' ) 

UNION ALL 

SELECT 'open_cursors', LPAD(value, 5), to_char(100 * used / value, '990') || '%' 

FROM 

(SELECT MAX(sum(s.value)) used 

FROM v$statname n, v$sesstat s 

WHERE 

n.name in ('opened cursors current', 'session cursor cache count') and s.statistic#=n.statistic# 

GROUP BY 

s.sid 

), 

(SELECT value 

FROM v$parameter 

WHERE 

name = 'open_cursors' ) 

/ 

Cursor leak in Oracle :

It is important to be able to diagnose which cursor is being 'leaked' (not closed) to identify what part of the application is responsible for managing the cursor,

  Lists each cursor, which has been opened by the session more than once in descending order.
   SELECT COUNT(*), address
  2  FROM v$open_cursor
  3  WHERE sid = 135
  4  GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);

Enable  Traving to diagnose ORA-1000   :
ALTER SYSTEM SET EVENTS '1000 trace name errorstack level 3';

Bugs Related to Cursors :

1) BUG 30518349 - ORA-01000 SELECT DOCID FROM "DR#IX_TS0481$U" , "DR#IX_TS0481$K"...

2) Bug 23608322 - CURSOR LEAK WITH DBMS_SQL.RETURN_RESULT IN JDBC THIN DRIVER 12.1.0.2 was opened for this issue.

References   : 

https://docs.oracle.com/cd/E40329_01/admin.1112/e27149/cursor.htm#OMADM5352
https://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_10opt.htm

https://docs.oracle.com/database/121/TGSQL/tgsql_cursor.htm#TGSQL848