RE: ORA-4031 on 9.2.0.5
- From: "Subbiah, Nagarajan" <Nagarajan.Subbiah@xxxxxxxx>
- To: "'Oracle-L (E-mail)'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Jan 2005 10:31:42 -0500
Hi Oracle Gurus,
"PL/SQL Cursor Caching
Prior to release of the 9.2.0.5.0 patch set, the maximum number of cursors
that
could be cached for fast lookup by PL/SQL was bounded by the value of the
init.ora parameter open_cursors. If you currently have open_cursors set to a
high value (for example, greater than 1000), it is likely that this is
causing
large numbers of PL/SQL cursors to be cached in the shared pool. This could
lead
to issues with memory management, frequent reloading of library cache
objects
and ORA-04031 errors.
Patch set 9.2.0.5.0 alleviates the issue by changing the init.ora parameter
which determines the upper bound for PL/SQL cursor caching from open_cursors
to
session_cached_cursors.
Most users will not need to modify the value of either of these parameters.
If
you already have session_cached_cursors set to a value greater than the
open_cursors parameter, then this change will have no performance impact
upon
your system.
However, if you have session_cached_cursors set to zero, or set at a value
significantly lower than the open_cursors parameter, and you are concerned
that
PL/SQL cursors need to be cached for optimal performance, then you should
ensure
that the session_cached_cursors parameter is increased appropriately.
This issue is bug number 3150705. "
How to findout the optimal value for the session_cached_cursors?
Maximum open cursor for a session from v$open_cursor is 145 and the
session_cached_cursor values is 128.
Raja.
> -----Original Message-----
> From: Subbiah, Nagarajan
> Sent: Wednesday, January 19, 2005 10:48 AM
> To: 'Oracle-L (E-mail)'
> Subject: ORA-4031 on 9.2.0.5
>
> We are using oracle 9.2.0.5 on HP 11.11. The v$sgastat monitoring shows
> that the 'shared pool miscellaneous' is keep growing and ORA-4031 occurs
> in few days. The session_cached_cursors, value is 128 and the
> open_cursors is 600. What does the 'shared pool miscellaneous' refers to
> and any known bugs on ORA-4031 on 9.2.0.5?
>
> Any script is available for the pro-active monitoring and take some action
> to avoid it before the application start throwing the error message?
> Value of _kghdsidx_count is 2. Shared_pool_size was increased from 250M to
> 600M.
>
> Thanks,
> Raja
--
//www.freelists.org/webpage/oracle-l
Other related posts:
