Frequently asked question
What is an appropriate value for Oracle's init.ora parameter session_cached_cursors?
Answer
As defined by Oracle's documentation, Reference section, Chapter 1 -- "Initialization Parameters":"SESSION_CACHED_CURSORS specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed."
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 9.2.0.5 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.
Article ID: 000006975
Software:
- Legacy Products
Receive notifications and find solutions for new or common issues
Get summarized answers and video solutions from our new AI chatbot.
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback
Get help from ArcGIS experts
Download the Esri Support App