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