【CURSOR】How to Monitor and tune Open and Cached Cursors (Doc ID 1430255.1)

In this Document

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

GOAL

This article deals with monitoring and tuning of open and cached cursors and initialization parameters which affect open cursors.

SOLUTION

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.

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 Oracle recommends that you set this to at least 500 for most applications. You 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. You 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. You 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 you 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:

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

TUNING OPEN_CURSORS

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

If you 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 your applications has a cursor leak, it will eventually show up even with OPEN_CURSORS set high.

To see if you have 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.

After you have increased the value of OPEN_CURSORS, keep an eye on v$sesstat to see if opened cursors current keeps increasing for any of your sessions. If you have an application session whose opened cursors current always increases to catch up with OPEN_CURSORS, then you have likely got a cursor leak in your application code, i.e. your 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.

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.

TUNING THE SESSION CURSOR CACHE (SESSION_CACHED_CURSORS)

You 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 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 did not have to be reparsed and your session did not 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.

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

For example, enter the following query for session 35:

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:

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