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.

Monitoring open cursors

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:

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

Monitoring the session cursor cache

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

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_textfrom v$open_cursor c, v$sql sqlwhere c.sql_id=sql.sql_id — for 9i and earlier use: c.address=sql.addressand c.sid=&sid

Check Session Cursor Cache tuning for details.

 

Advertisements

One response to this post.

  1. Posted by veerana on October 13, 2010 at 12:15 pm

    Great article!

    I do agree with AKV’s comment that it is better to force developers to close their cursors rather than having a DBA trying to resolve the problem by tweaking Oracle.

    David Litchfield recently made a very strong case that dangling cursors are a security concern; procedures/packages with open cursors may leak (potentially sensitive) information in an unexpected manner. Not only can attackers reuse the old cursor and check what it would have returned, you can also change the value of any bind variable used by it. Not only may you be able to reuse SELECT statements, but also INSERT statements. So dangling cursors could be a great problem for an application with a security model based on AUTHID DEFINER. best seo

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: