Cursor space for time parameter in Oracle

Cursor Space for time parameter is used for keep the SQL until its cursor is closed. It’s not aged out from the shared pool. By Default(value is FALSE), the SQL is aged out after the Parsed phase if Oracle required to accommodate the new SQL. Default value for the parameter CURSOR_SPACE_FOR_TIME is FALSE.

In DEFAULT, Oracle behavior as follows:
1. Check for the free space and if it don’t get then go to 2.
2. Checks the closed cursors and if don’t then go to 3.
3. Then Oracle check for open cursors which are not EXECUTED.

Parameter Value
FALSE: means SQL statement to age out before the cursor is closed. If Oracle required to accommodate new SQL.(DEFAULT)
TRUE: SQL is not aged out before the cursor is closed, It increase performance because each active cursor’s SQL area is present in memory, execution is faster.

Note:

• Set this parameter to TRUE only when the shared pool is large enough to hold all open cursors simultaneously.
• It is better to keep it FALSE until you not sure that shared pool is enough large because it not age out pinned open cursor the SQL for New SQL’s.

Check the Parameter Value

Show parameter CURSOR_SPACE_FOR_TIME

NAME                    TYPE        VALUE
----------------------- ----------- ------------------------------
cursor_space_for_time   boolean     FALSE

Change the value of parameter required DB reboot

ALTER SYSTEM SET CURSOR_SPACE_FOR_TIME=TRUE SCOPE=SPFILE;

–Restart the DB
Shutdown immediate
Startup