FAQ: What is an appropriate value for Oracle's init.ora parameter session_cached_cursors?
What is an appropriate value for Oracle's init.ora parameter session_cached_cursors?
As defined by Oracle's documentation, Reference section, Chapter 1 -- "Initialization Parameters":
This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are re-executed by a user.
From Oracle release 188.8.131.52 forward, the default value has been increased from 0 to 50. The new default value is sufficient for ArcSDE/ArcGIS applications and should not be lowered.
Verify the Oracle instance's value is not 0 (or < 50) because the cache is now used by Oracle to hold PL/SQL cursors open for optimal performance when they are executed.
A value too small can have a negative impact for an ArcSDE/ArcGIS session. ArcSDE uses several stored procedures for data management and if the PL/SQL cursors are not held, additional parsing and contention can be introduced within the server.