session_cached_cursors and open_cursor sizing

Pinning SQL with session_cached_cursors is especially useful for reentrant SQL that contains hoist variables,  and there are many incarnations of the SQL, each with a different host variable value.  

The session_cached_cursors parameter is used to reduce the amount of parsing with SQL statements 

that use host variables and with PL/SQL cursors. 

The session_cached_cursors parameter has a default value of 50, and increasing the value of 

session_cached_cursors will requires a larger shared_pool_size to cache the cursors. 

ALTER SYSTEM SET OPEN_CURSORS = 500 SCOPE=BOTH;

SESSION_CACHED_CURSORS specifies the number of session cursors to cache.

ALTER SESSION SET SESSION_CACHED_CURSORS = 50;

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once.  Can  be used this parameter to prevent a session from opening an excessive number of cursors.

For example, if OPEN_CURSORS is set to 100, then each session can have up to 100 cursors open at one time. If a single session has 100 (open_cursors value) cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but it is recommended set this to at least 300 for most applications. It would have to monitor the cursor usage to determine an appropriate value.
Two main initialization parameters that affect cursors are:

* SESSION_CACHED_CURSORS
This parameter sets the maximum number of cached closed cursors for each session. The default setting is 50. We can use this parameter to prevent a session from opening an excessive number of cursors, thereby filling the library cache or forcing excessive hard parses. This parameter has no effect on ORA-1000 errors or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached. There is no relationship between the two parameters. We can set SESSION_CACHED_CURSORS higher than OPEN_CURSORS because session cursors are not cached in an open state.

* OPEN_CURSORS
This parameter specifies the maximum number of cursors a session can have open simultaneously.

MONITORING OPEN CURSORS

v$open_cursor shows cached cursors, not currently open cursors, by session. If we are wondering how many cursors a session has open, do not 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:

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

If we are running several N-tiered applications with multiple webservers,  may find it useful to monitor open cursors by username and machine:

-- Total cursors open, by username & machine

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;

TUNING OPEN_CURSORS

If sessions are running close to the limit have set for OPEN_CURSORS, raise it.  goal in tuning this parameter is to set it high enough that never get an ORA-1000 during normal operations.

If set OPEN_CURSORS to a high value, this does not mean that every session will have that number of cursors open. Cursors are opened on an as-needed basis. And if one of  applications has a cursor leak, it will eventually show up even with OPEN_CURSORS set high.

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

SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
2> from v$sesstat a, v$statname b, v$parameter p
3> where a.statistic# = b.statistic# 
4> and b.name = 'opened cursors current'
5> and p.name= 'open_cursors'
6> group by p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
            1953         2500

After have increased the value of OPEN_CURSORS, keep an eye on v$sesstat to see if opened cursors current keeps increasing for any of sessions. If have an application session whose opened cursors current always increases to catch up with OPEN_CURSORS, then  have likely got a cursor leak in  application code, i.e. application is opening cursors and not closing them when it is done. The application developers need to go through the code, find the cursors that are being left open, and close them. 


MONITORING THE SESSION CURSOR CACHE (SESSION_CACHED_CURSORS)

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

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

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

TUNING THE SESSION CURSOR CACHE (SESSION_CACHED_CURSORS)

 Can query V$SYSSTAT to determine whether the session cursor cache is sufficiently large for the database instance.

To tune the session cursor cache: 

If choose to use SESSION_CACHED_CURSORS to help out an application that is continually closing and reopening cursors, 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 did not have to be reparsed and  session did not have to search through the library cache for it. 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.

1. Determine how many cursors are currently cached in a particular session.

For example, enter the following query for session 35:

SQL> SELECT a.value curr_cached, p.value max_cached,
2 s.username, s.sid, s.serial#
3 FROM v$sesstat a, v$statname b, v$session s, v$parameter2 p
4 WHERE a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid
5 AND p.name='session_cached_cursors'
6 AND b.name = 'session cursor cache count';
Enter value for sid: 35
old 4: WHERE a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid
new 4: WHERE a.statistic# = b.statistic# and s.sid=a.sid and a.sid=35

CURR_CACHED MAX_CACHED USERNAME SID SERIAL#
----------- ---------- -------- --- -------
         49         50 APP       35     263

The preceding result shows that the number of cursors currently cached for session 35 is close to the maximum.

2. Find the percentage of parse calls that found a cursor in the session cursor cache.

For example, enter the following query for session 35:

SQL> SELECT cach.value cache_hits, prs.value all_parses,round((cach.value/prs.value)*100,2) as "% found in cache"
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;

Enter value for sid: 35
old 8: AND cach.sid= &sid and prs.sid= cach.sid
new 8: AND cach.sid= 35 and prs.sid= cach.sid

CACHE_HITS ALL_PARSES % found in cache
---------- ---------- ----------------
        34        700             4.57

The preceding result shows that the number of hits in the session cursor cache for session 35 is low compared to the total number of parses.

3. Consider increasing SESSION_CURSOR_CACHE when the following statements are true:
* The session cursor cache count is close to the maximum.
* The percentage of session cursor cache hits is low relative to the total parses.
* The application repeatedly makes parse calls for the same queries.

In this example, setting SESSION_CURSOR_CACHE to 100 may help boost performance.

If the session cursor cache count is maxed out, session_cursor_cache_hits is low compared to all parses, and  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 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. In case application is using a lot of unsharable SQL, raising this parameter will not help.