|This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.|
The purpose of the document is to explain how to check a database such as Oracle for any open database cursors.
This document is specific to an Oracle database. The intention is that the details in this document can be used by Siebel developers and programmers to troubleshoot memory leak issues that occur as a result of custom configuration and/or custom scripting that has been added to the standard SRF.
For example, Siebel programmers often create custom scripts to reference and query business component records and retrieve data. During this process, memory objects are created in the Siebel Object Manager that result in a new database cursor been opened in the database. If this newly created object is not released in the correct way at the end of the script, then this will result in a the database cursor remaining open. This document describes the steps to check for these types of Open Cursors in an Oracle database.
Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.
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. If a single session has OPEN_CURSORS # of 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 that you set this to at least 500 for most applications.
There are two main initialization parameters that affect cursors. One is OPEN_CURSORS, and the other is SESSION_CACHED_CURSORS.
SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. You can set SESSION_CACHED_CURSORS to higher than OPEN_CURSORS, lower than OPEN_CURSORS, or anywhere in between. This parameter has no effect on ora-1000's or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached.
There's no relationship between the two parameters. If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session.
Therefore to check how many cursors a session has open, don't look in v$open_cursor as it shows the cursors in the session cursor cache for each session, not cursors that are actually open. Instead, to monitor open cursors, query the v$sesstat view.
To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session.
Please use the Select statement below to check the open cursors.
Proofread this script. before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script. may not be in an executable state when you first receive it. Check over the script. to ensure that errors of this type are corrected.
--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';
Here is sample output from the open cursor check.
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;
Note: Please note that the format of the output may be displayed differently in the environment you are using
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38267/viewspace-730266/，如需转载，请注明出处，否则将追究法律责任。