Raise maxopencursors when Compiling Pro*C/C++

David Clement

September 2004

Oracle versions 7-8

The Pro*C/C++ precompiler option, maxopencursors, specifies how many cursors a session should expect to keep open for concurrent use. In spite of its name, this precompiler option is not a maximum but a target that Oracle will aim at, and its default value of 10 is almost always too low.

The true maximum number of cursors per user session is set by an Oracle initialization file parameter, open_cursors. That is, Oracle will not give any session more entries in the cursor cache in the PGA (process global area) of the server process than the value of open_cursors. The value of maxopencursors, on the other hand, tells the process how many cursor cache entries to request initially, and when to try to reuse a cache entry: it will try to reuse a cache entry whenever the number of actually open cursors exceeds the value of maxopencursors.

Therefore, a process compiled with maxopencursors = 10 and executed in an environment where open_cursors = 100 may still open 100 cursors, but it will keep trying to hold the number of cursors down to 10 by reallocating cursor cache entries. This might well involve repeatedly hard-parsing many SQL statements, for no actual savings in memory.

The Pro*C/C++ Precompiler Programmer's Guide advises that maxopencursors should be set to "no more than 6 less than the database parameter open_cursors to allow for the cursors used by the data dictionary.... [A] value of 45-50 is not uncommon."