Tuning Session Cached Cursors parameter

Session Cached Cursors parameter

Check the maximum value used by Oracle for value of Session Cached Cursors parameter

SELECT MAX(value) max_value

  FROM V$SESSTAT

 WHERE STATISTIC# IN

 (SELECT statistic# 

    FROM V$STATNAME 

   WHERE name = 'session cursor cache count');

max_value

---------

      500

If value has reached the maximum limit, then you can increase the value of this parameter.

Check the usage percentage of Session Cached Cursors

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 SYS.V_$STATNAME N, 

     SYS.V_$SESSTAT S

       WHERE N.name = 'session cursor cache count'

         AND S.statistic# = N.statistic#),

     (SELECT value

        FROM SYS.V_$PARAMETER

       WHERE name = 'session_cached_cursors');

PARAMETER                   VALUE    USAGE

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

session_cached_cursors        500     100%

Note: If usage is above 95% percent than you can also increase the value.

Increase or Decrease the value by setting with alter command

To change this parameter need to restart the Database.

ALTER SYSTEM SET session_cached_cursors = 1000 SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

This parameter can also be changed at Session level

ALTER SESSION SET session_cached_cursors = 1000;

To get info about query+session:

SELECT C.user_name, C.sid, VSQL.sql_text

  FROM V$OPEN_CURSOR C, V$SQL VSQL

 WHERE C.sql_id = VSQL.sql_id 

   AND VSQL.sql_text LIKE 'MERGE into SGA_W_IPN_SUBSCRIBER%';

SELECT sql_text, 

       SUM(executions)  executions,

       SUM(end_of_fetch_count) end_of_fetch_count,

       SUM(parse_calls) parse_calls,

       SUM(invalidations) invalidations,

       SUM(cpu_time) cpu_time

  FROM V$SQL

WHERE sql_text LIKE 'MERGE into SGA_W_IPN_SUBSCRIBER%'

GROUP BY sql_text

HAVING SUM(parse_calls) > 1

ORDER BY SUM(parse_calls) DESC;