Tuning the _row_cache_cursors Parameter

The explanation of the row_cache_cursors parameter as "the maximum number of cached recursive cursors used by the dictionary cache manager for selecting rows from the data dictionary" was for years one of the more enigmatic notes in the Oracle documentation. Oracle eventually resolved the problem in release 8.1 by making the parameter a hidden and thus an undocumented parameter!

This was a long overdue change. There is normally little, if anything, to be gained by using a larger value than the default value of 10. Nevertheless, some Oracle consultants have the habit of setting this parameter to 200 or 300, and are not dissuaded by the fact that it is now a hidden parameter. However, such high values just waste memory, and while it is seldom more than a few megabytes, a more scientific approach to the correct setting of this parameter is in order.

What does it do?

From the explanation in the old documentation, or the description in X$KSPPI, you could be forgiven for thinking that this parameter sets a limit on the number of shared cursors for dictionary cache management that can be cached in the shared pool. However, a simple query against V$SQLAREA is normally enough to demonstrate that there is in fact no such limit. Row cache cursors get no special treatment in the shared pool.

In fact, the parameter limits the number of private SQL areas for row cache cursors that a process can cache during a call. Interestingly, this cache of private SQL areas, or shared cursor instantiations, is controlled from process memory (the PGA), rather than from session memory (the UGA) where the private SQL areas reside. The _row_cache_cursors parameter sets the size of an array in permanent PGA memory. Each slot in that array requires just 28 bytes. An unduly small value for this parameter causes otherwise unnecessary soft parse operations. An unduly large value wastes some memory in the PGA of every Oracle server process, and may reduce the caching of other cursors if the open_cursors parameter is modestly set.

How can you tune it?

The good news is that Oracle does keep some statistics about the caching of private SQL areas for row cache cursors. These statistics are visible in X$KQDPG. They report the cache limit, the number of times that a row cache cursor has been needed, the number of times that an instantiation (and possibly a parse) was required, and the number of times that the cache has overflowed. Of course, if the cache has overflowed then it is too small. If not, it is big enough. In general, the number of concurrently cached row cache cursors that might be useful is dependent on the duration and complexity of calls. Long-running PL/SQL procedures, that refer to many data dictionary objects of many different types, are most likely to benefit from increased caching of row cache cursor instantiations.

The bad news is that X$KQDPG is, of course, process specific and cannot be queried from another process. To tune the parameter correctly, if at all, you need the statistics from representative application processes, rather than those from the process being used for performance monitoring.

Fortunately, there is a solution. The data structure behind X$KQDPG is in the fixed PGA, and you can easily get a representative application process to dump its fixed PGA to its trace file using a global_area dump at level 1 from ORADEBUG (or an equivalent DBMS_SYSTEM.SET_EV call). From there is just a matter of finding the kqdpga_ data structure, skipping 12 bytes, and then reading the hexadecimal values that would have been returned had you been able to query X$KQDPG for the process. Easy!

If you find overflows, by all means increase the _row_cache_cursors parameter. That's what it's still there for. If open_cursors is modest, then that parameter should be increased by the same amount. A very small increase in these parameters is likely to be sufficient to prevent the overflows without any negative side effects.