Init parameterProcesses & sessions

SESSION_CACHED_CURSORS

What it controls

How many closed cursors each session keeps cached, which saves repeated soft parses.

Know this before you change it

For the whole instance it needs a restart; ALTER SESSION changes it straight away for one session.

Default: 50

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'session_cached_cursors';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'session_cached_cursors' AND isspecified = 'TRUE';8 9-- Static: saved in the spfile, takes effect after a restart10ALTER SYSTEM SET session_cached_cursors = 200 SCOPE = SPFILE SID = '*';11 12-- Or for your own session only13ALTER SESSION SET session_cached_cursors = 200;14 15-- Remove it from the spfile to go back to the default at the next restart16ALTER SYSTEM RESET session_cached_cursors SCOPE = SPFILE SID = '*';

Open in the parameter referenceOracle's reference

More in Processes & sessions

• PROCESSESThe most operating system processes that can connect to the instance, including background processes.
• SESSIONSThe most sessions the instance allows.
• OPEN_CURSORSThe most cursors one session can have open at once.
• JOB_QUEUE_PROCESSESThe most job slave processes for Scheduler and DBMS_JOB jobs.
• RESOURCE_LIMITWhether profile resource limits such as idle time and CPU per call are enforced.
• DDL_LOCK_TIMEOUTSeconds a DDL statement waits for a lock before failing with ORA-00054.