Shared Pool Tuning: Cursor Tuning (Tuning Open_Cursors, Session_Cached_Cursors, Cursor_Space_For_Time)

Shared Pool Tuning: Cursor Tuning

The three most important parameter for shared pool tuning are OPEN_CURSORS, SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME. But most of the time we see that these two parameters SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME are ignored or unused.

OPEN CURSORS

Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

V$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open. To monitor open cursors, query v$sesstat where name=’opened cursors current’. This will give the number of currently opened cursors, by session:

n  Total cursors open, by session

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';

n  Total cursors open, by username & machine

You may find it useful to monitor open cursors by username and machine while working on N-tier Application:

select sum(a.value) total_cur,

       avg(a.value) avg_cur,

       max(a.value) max_cur,

       s.username,

       s.machine

  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, s.machine

 order by 1 desc;

n  Tuning OPEN_CURSORS

To see if you've set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS

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;

SESSION_CACHED_CURSORS

Every time a statement is issued, the session searches the shared pool for the parsed state, if it doesn’t get the parsed version, it will “hard parse” it, and if it exists in shared pool, it will still do a “soft parse”.

The term “hard parse” is a costly operation, even a “soft parse” requires library cache latch and CPU overhead, which is will be significant number if we sum up.

This parameters default value is 50. This will improve the “soft parse” performance by doing a softer soft parse. Confused? Let’s have a closer look.

If this parameter is set, oracle maintains a local session cache which stores recently closed cursors of a session and maintains the cursors for which there have been 3 parsed calls in the past, so all the SQL’s issued by a session are not here. Each cursor if pinned here, is not freeable and you may lead to allocate more shared pool area.

A normal cursor in shared pool is sum of 2 components:


a) Heap 0 – size 1KB
b) SQL Area – size multiple of 4k


When we use session_cached_cursors only first component of cursor which is HEAP 0 is pinned in local session cache and if there is a call for re-parse for a statement, Oracle first checks the existence of the cursor in local cache and if found, it gets the address of the rest of the cursor which is in SQL Area provided that it is not aged out, so hereby saving CPU overhead and library cache latch contention.

The following query shows maximum value for session_cached_cursors in the past. If this value equals “session_cached_cursors” (see init.ora parameter), you should consider increasing it.

n  Maximum value for session_cached_cursors

select max(value)

  from v$sesstat

 where STATISTIC# in

       (select STATISTIC#

          from v$statname

         where name = 'session cursor cache count');

n  SESSION_CACHED_CURSORS by session

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.

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 = 'session cursor cache count';

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

select c.user_name, c.sid, sql.sql_text

  from v$open_cursor c, v$sql sql

 where c.sql_id = sql.sql_id -- for 9i and earlier use: c.address=sql.address

   and c.sid = &sid;

n  To see the hard and soft parse percentage, issue the following query:

           

select to_char (100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,

       to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,

       to_char(100 * hard / calls, '999990.00') || '%' hard_parses

  from (select value calls

          from sys.v_$sysstat

         where name = 'parse count (total)'),

       (select value hard

          from sys.v_$sysstat

         where name = 'parse count (hard)'),

       (select value sess

          from sys.v_$sysstat

         where name = 'session cursor cache hits');

         

CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
-----------------                 -----------           -----------
           45.56%                   49.98%               4.46%

n  To see the usage percentage on session_cached_cursors and open_cursors issue the following query:

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')

union all

select 'open_cursors',

       lpad(value, 5),

       to_char(100 * used / value, '990') || '%'

  from (select max(sum(s.value)) used

          from sys.v_$statname n, sys.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 sys.v_$parameter where name = 'open_cursors')

           
             PARAMETER                VALUE    USAGE
             ----------------------       -----        -----
             session_cached_cursors    40           100%
             open_cursors                    4000       3%

n  Tuning SESSION_CACHED_CURSORS

If you choose to use SESSION_CACHED_CURSORS to help out an application that is continually closing and reopening cursors, you can monitor its effectiveness via two more statistics in v$sesstat. The statistic "session cursor cache hits" reflects the number of times that a statement the session sent for parsing was found in the session cursor cache, meaning it didn't have to be reparsed and your session didn't have to search through the library cache for it. You can compare this to the statistic "parse count (total)"; subtract "session cursor cache hits" from "parse count (total)" to see the number of parses that actually occurred.

select cach.value cache_hits,

       prs.value all_parses,

       prs.value - cach.value sess_cur_cache_not_used

  from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2

 where cach.statistic# = nm1.statistic#

   and nm1.name = 'session cursor cache hits'

   and prs.statistic# = nm2.statistic#

   and nm2.name = 'parse count (total)'

   and cach.sid = &sid

   and prs.sid = cach.sid;

More importantly if you want to see how your session cache is doing. Issue the following query.

select cache / tot * 100 "Session cursor cache%"

  from (select value tot from v$sysstat where name = 'parse count (total)'),

       (select value cache

          from sys.v_$sysstat

         where name = 'session cursor cache hits');

Session cursor cache%
---------------------
     90.745855740977


A value near 100 is considered very well. But you may still consider increasing this parameter if MAX(VALUE) in query one shows you equal number of cached cursor which you have set.

Also increasing this parameter will mean extra memory required for shared pool, so you must increase your shared pool when you use this parameter.

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


select a.name, b.value
  from v$statname a, v$mystat b
 where a.statistic# = b.statistic#
   and lower(a.name) like '%' || lower('&1') || '%';

NAME                                                                                      VALUE

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

opened cursors cumulative                                                          186

opened cursors current                                                               1

session cursor cache hits                                                             136

session cursor cache count                                                          35

cursor authentications                                                                  11

that shows I've 35 cursors in my "cache" ready to be opened faster then normal -- but I never 

exceeded my 1000 open cursors at a time threshold.

CURSOR_SPACE_FOR_TIME

When the cursors are closed by application, they can still be in shared pool, until there comes a request for a new cursor and oracle needs to use more memory using LRU algorithm. SESSION_CACHED_CURSORS helps you in pinning (partial because it only pins HEAP 0) when the cursors are closed.

Oracle requires parsed state of SQL at PARSE and EXECUTE phase. If oracle parses (soft or hard) a statement, there is a likely hood that Oracle may age out your SQL out of shared pool after PARSE state if it requires to accommodate a new SQL coming its way. So in the EXECUTE state, there is a possibility that parsed information is lost and oracle parse it again.

CURSOR_SPACE_FOR_TIME if set to TRUE, ensures that SQL is not aged out before the cursor is closed, so in EXECUTE phase, you will have the PARSE information.

But this is generally a rare case and happens in a very highly active environment because to accommodate a new SQL, Oracle first check the free space and if it doesn’t get, it checks the closed cursors and see if any cursor can be aged out and when there is no space which can be reclaimed, Oracle comes to open cursors which are not EXECUTED.
This generally happens when the space of shared pool is too less.

It is not suggested to set up this parameter to TRUE in most of the cases. The reason behind this is that some serious compromise. When a cursor is pinned, it can’t be aged out and related memory cannot be freed for any new SQL, Oracle will keep all the open cursors pinned and not freeable. If you use this parameter, you are pinning the whole cursor not just the HEAP 0 which is 1k, you are pinning HEAP 0 (1k) + SQL Area (multiple of 4k) which makes shared pool life tough because of space issues.

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

hAVE A nICE dAY !!!