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
