FAQ: What is an appropriate value for the Oracle init.ora parameter 'open_cursors'?


What is an appropriate value for the Oracle init.ora parameter 'open_cursors'?


As defined by Oracle's documentation, Reference section, Chapter 1 -- "Initialization Parameters":

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

It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed."

The ArcSDE 9.0 release is optimized to hold frequently executed cursors open to improve performance and scalability.

The initial release of ArcSDE 9.0 holds the following types of cursors open:
Various SDE data management queries, various anonymous PL/SQL blocks, spatial queries, logfile queries, and miscellaneous queries used when editing multi-versioned tables.

The cursors are only held open if the parameter open_cursors has been set to a large enough value. When an ArcSDE session connects, it obtains the value of the open_cursors parameter and holds cursors up to 80% of the value.

For example, if the Oracle instance's open_cursors parameter is set to 300, ArcSDE only holds open 240 cursors concurrently for the session. Once the 80% threshold limit is encountered, any cursor, that was a potential candidate to be held open, is closed after executing and fetching the result set from the database.

As noted by Oracle's documentation, setting the parameter to a large value has no adverse affects. Therefore, administrators can set the value extremely large, for example, 2000. If administrators wish to calculate the potential number of cursors a session has open, the following formula based on the organization's data model, can be used as a guideline:

� Various SDE data management cursors (20) + various anonymous PL/SQL blocks (20) +.

� Spatial queries -- potential 6 per layer.

� Logfile queries (11), and miscellaneous queries used when editing multi-versioned tables -- 12 per multi-versioned table or layer.

Therefore, an ArcMap application with 10 layers being edited in the document can potentially have 231 cursors open (20 + 20 + 60 + 11 + 120 = 231).

An example of when an administrator might need to lower the parameter's value is when memory resources on the server running the Oracle instance do not have enough memory available for each Oracle dedicated process.

To obtain the size of dedicated process memory requirements requires prototyping the application. Several Oracle parameters and the application's behavior, such as an SQL statement, can affect processes memory requirements.