Init parameterOptimizer & SQL

RESULT_CACHE_MAX_SIZE

What it controls

Memory for the server result cache.

Know this before you change it

Useful for small, frequently repeated lookups. Heavy use can cause latch contention.

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'result_cache_max_size';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'result_cache_max_size' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET result_cache_max_size = 64M SCOPE = BOTH SID = '*';11 12-- Remove it from the spfile to go back to the default at the next restart13ALTER SYSTEM RESET result_cache_max_size SCOPE = SPFILE SID = '*';

Open in the parameter referenceOracle's reference

More in Optimizer & SQL

• OPTIMIZER_FEATURES_ENABLEMakes the optimizer behave like an earlier release.
• OPTIMIZER_ADAPTIVE_STATISTICSAdaptive statistics features such as SQL plan directives and dynamic statistics (12.2 and later).
• OPTIMIZER_ADAPTIVE_PLANSLets the optimizer switch join methods during the first execution (12.2 and later).
• CURSOR_SHARINGWhether Oracle replaces literals with bind variables before parsing.
• STATISTICS_LEVELHow much performance data Oracle collects.
• OPTIMIZER_MODEWhether the optimizer aims for total throughput or fast first rows.
• DB_FILE_MULTIBLOCK_READ_COUNTBlocks read in one I/O during full scans.
• PARALLEL_MAX_SERVERSThe most parallel execution processes per instance.
• PARALLEL_DEGREE_POLICYWhether Oracle chooses the degree of parallelism itself.