Multiple choice

An application that uses a database keeps cursors open. What should be confirmed before changing the CURSOR_SPACE_FOR_TIME parameter in the database initialization file to TRUE?

  1. The TIMED_STATISTICS parameter is set to TRUE.

  2. The hit percentage in the buffer cache is at least 95%.

  3. The OPEN_CURSOR parameter is set to at least twice the default value.
  4. The value in the RELOADS column of V$LIBRARYCACHE is consistently zero or close to zero.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

CURSOR_SPACE_FOR_TIME set to TRUE tells Oracle to keep cursors in the shared SQL area even after they're closed, trading memory for faster execution. This only makes sense if cursors are being frequently re-executed without needing reload. The V$LIBRARYCACHE view's RELOADS column shows how many times library cache pins had to be reloaded - if this is consistently zero or close to zero, it means the current cache size is sufficient and there's no benefit to changing this parameter. The other options are unrelated to cursor caching behavior.

Keep practicing — related questions