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.
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.
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.
|Copyright © Ixora Pty Ltd||