The idea is to keep cursors until the percentage of never-used memory falls below 20%. If the threshold were based on the amount of free memory, instead of the amount of never-used memory, then there would be circumstances under which a manual flush of the shared pool or severe memory pressure that flushed out a lot of recreatable chunks would cause the script to begin keeping cursors again, which is not what we want.

Incidentally, the reason for the generous threshold of 20% is to allow for instances that have different working sets of SQL statements during different phases of their duty cycle. For example, consider a financials system that does OLTP work during the day and batch processing at night. If we allowed too many OLTP cursors to be kept, then the batch workload would not have enough room to move in the shared pool.

Just a quick question on one of your scripts (keeper.sql). I'm curious as to why you use sum of free, freable and recr chunks to determine used memory as opposed to shared_pool_size - shared_pool_reserved_size - free memory (from V$SGASTAT).

The reason I'm asking is that I can create a situation where I've got 30% or so free memory (as reported by V$SGASTAT), but the used memory as calculated by keeper.sql is 97% and thus no further cursor keeping would be attempted.

Am I missing something obvious?