SHARED_POOL_SIZE - Tune Oracle Shared Pool & Library Cache

SHARED_POOL_SIZE

Overview

SHARED_POOL_SIZE specifies the minimum size (in bytes) of the shared pool, the SGA component that holds the library cache (parsed SQL and PL/SQL), the data dictionary cache, and control structures. When SGA_TARGET or MEMORY_TARGET is set, Oracle manages the shared pool automatically through ASMM or AMM and SHARED_POOL_SIZE acts as a lower bound — Oracle will never shrink the shared pool below this value. When auto-management is disabled, this parameter directly sets the shared pool size.

A correctly sized shared pool reduces hard parses, eliminates ORA-04031 shared memory errors, and is one of the highest-leverage tuning parameters for OLTP workloads with high SQL execution rates.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: 0 when SGA_TARGET > 0 (Oracle auto-sizes); otherwise platform-dependent minimum (~16–64 MB) Valid Range: 1 MB minimum (Oracle enforces an internal floor); OS-dependent maximum Available Since: Oracle Database 7 (parameter form varies by release) Modifiable: Yes — ALTER SYSTEM (increase is online; shrink may fail if memory in use) PDB Modifiable: No — SGA parameters are CDB-level only

Configuration

Viewing Current Value

-- Check current shared pool size and whether it is auto-managed
SELECT name,
       value                       AS bytes,
       value / (1024 * 1024)       AS mb,
       display_value,
       isdefault,
       description
FROM   v$parameter
WHERE  name = 'shared_pool_size';

-- Check the actual runtime shared pool size (may differ from parameter when auto-managed)
SELECT component,
       current_size / (1024 * 1024)       AS current_mb,
       min_size     / (1024 * 1024)       AS min_mb,
       max_size     / (1024 * 1024)       AS max_mb,
       user_specified_size / (1024*1024)  AS user_specified_mb,
       last_oper_type,
       last_oper_mode
FROM   v$sga_dynamic_components
WHERE  component = 'shared pool';

-- Check all SGA components for context
SELECT component,
       current_size / (1024 * 1024) AS current_mb
FROM   v$sga_dynamic_components
ORDER BY current_size DESC;

Setting the Parameter

-- When using ASMM (SGA_TARGET > 0): set a minimum floor to prevent over-shrinking
-- Oracle will auto-manage above this floor
ALTER SYSTEM SET shared_pool_size = 512M SCOPE = BOTH;

-- When NOT using ASMM (manual SGA): set a fixed shared pool size
ALTER SYSTEM SET shared_pool_size = 1G SCOPE = BOTH;

-- Increase immediately for an active instance (no restart needed)
ALTER SYSTEM SET shared_pool_size = 2G SCOPE = BOTH;

-- Persist to SPFILE only (takes effect at next restart)
ALTER SYSTEM SET shared_pool_size = 2G SCOPE = SPFILE;

-- To let ASMM manage it freely again, set to 0
ALTER SYSTEM SET shared_pool_size = 0 SCOPE = BOTH;

Tuning Guidance

Recommended Values

Environment	SGA_TARGET Mode	SHARED_POOL_SIZE Setting	Rationale
OLTP, < 500 concurrent sessions	ASMM or AMM	256–512 MB floor	ASMM handles fluctuations
OLTP, high SQL diversity, 500+ sessions	ASMM	1–2 GB floor	Prevent ASMM from shrinking pool
Data Warehouse (mostly static SQL)	ASMM	256–512 MB floor	Library cache not primary concern
Application with heavy PL/SQL packages	Manual or floor	1–4 GB	Large packages lock pool memory
No ASMM (SGA_TARGET = 0)	Manual	20–40% of total SGA	Manually balance with buffer cache

How to Size

Use V$SHARED_POOL_ADVICE for data-driven sizing. This view simulates what the library cache hit ratio would be at different shared pool sizes.

-- V$SHARED_POOL_ADVICE: simulate hit ratio at different pool sizes
SELECT shared_pool_size_for_estimate / (1024 * 1024) AS pool_size_mb,
       shared_pool_size_factor                        AS size_factor,
       estd_lc_size / (1024 * 1024)                  AS estd_lc_mb,
       estd_lc_memory_objects                         AS estd_cached_objects,
       estd_lc_time_saved                             AS time_saved_secs,
       estd_lc_time_saved_factor                      AS time_saved_factor,
       estd_lc_load_time                              AS load_time_secs,
       estd_lc_load_time_factor                       AS load_time_factor
FROM   v$shared_pool_advice
ORDER BY shared_pool_size_for_estimate;

Look for the knee of the curve: the smallest pool size where estd_lc_time_saved_factor approaches 1.0. Sizes beyond that point yield diminishing returns.

-- Library cache hit ratio (target > 99% for OLTP)
SELECT namespace,
       gets,
       gethits,
       ROUND(gethitratio * 100, 2) AS hit_ratio_pct,
       pins,
       pinhits,
       ROUND(pinhitratio * 100, 2) AS pin_ratio_pct,
       reloads,
       invalidations
FROM   v$librarycache
ORDER BY gets DESC;

-- Data dictionary cache hit ratio (target > 95%)
SELECT parameter,
       gets,
       getmisses,
       ROUND((gets - getmisses) / NULLIF(gets, 0) * 100, 2) AS hit_ratio_pct,
       modifications,
       flushes
FROM   v$rowcache
WHERE  gets > 0
ORDER BY getmisses DESC
FETCH FIRST 20 ROWS ONLY;

-- Check free memory in the shared pool (low free = potential ORA-04031 risk)
SELECT name,
       bytes / (1024 * 1024) AS free_mb,
       ksmchsiz               AS largest_free_chunk_bytes
FROM   v$sgastat
WHERE  pool = 'shared pool'
AND    name = 'free memory';

-- Hard parse ratio (should be < 1% for well-tuned OLTP)
SELECT s1.value                                    AS hard_parses,
       s2.value                                    AS total_parses,
       ROUND(s1.value / NULLIF(s2.value, 0) * 100, 2) AS hard_parse_pct
FROM   v$sysstat s1, v$sysstat s2
WHERE  s1.name = 'parse count (hard)'
AND    s2.name = 'parse count (total)';

Monitoring

-- Monitor shared pool free memory over time (run periodically)
SELECT SYSDATE                              AS sample_time,
       bytes / (1024 * 1024)               AS free_mb
FROM   v$sgastat
WHERE  pool = 'shared pool'
AND    name = 'free memory';

-- Find SQL consuming the most shared pool memory
SELECT sql_id,
       sharable_mem / 1024          AS sharable_kb,
       persistent_mem / 1024        AS persistent_kb,
       runtime_mem / 1024           AS runtime_kb,
       loads,
       invalidations,
       parse_calls,
       executions,
       SUBSTR(sql_text, 1, 80)      AS sql_text
FROM   v$sql
ORDER BY sharable_mem DESC
FETCH FIRST 25 ROWS ONLY;

-- PL/SQL objects consuming shared pool memory
SELECT owner,
       name,
       type,
       sharable_mem / 1024    AS sharable_kb
FROM   v$db_object_cache
WHERE  type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
ORDER BY sharable_mem DESC
FETCH FIRST 20 ROWS ONLY;

-- Detect non-sharable SQL (literals not bind variables) — major pool consumers
SELECT COUNT(*)                                    AS non_sharable_sql_count,
       SUM(sharable_mem) / (1024 * 1024)          AS total_mb_wasted
FROM   v$sql
WHERE  executions = 1
AND    parse_calls = 1;

Common Issues

ORA-04031: Unable to allocate N bytes of shared memory

The most common consequence of an undersized or fragmented shared pool. Occurs when Oracle cannot find a contiguous free chunk large enough for a new SQL cursor, PL/SQL unit, or dictionary entry.

Immediate relief:

-- Flush the shared pool to reclaim fragmented memory (brief performance impact)
ALTER SYSTEM FLUSH SHARED_POOL;

-- Check if the pool has grown since the error (ASMM may have auto-expanded)
SELECT component, current_size / (1024 * 1024) AS current_mb
FROM   v$sga_dynamic_components
WHERE  component = 'shared pool';

Permanent fix:

-- Increase the shared pool size (or its floor when using ASMM)
ALTER SYSTEM SET shared_pool_size = 1G SCOPE = BOTH;

See the full ORA-04031 guide.

High hard parse rate causing latch contention

If the library cache latch or shared pool latch appears in V$LATCH with a high miss rate, the root cause is usually non-sharable SQL (literals instead of bind variables) filling the pool with single-use cursors.

-- Identify literals that should be bind variables
SELECT force_matching_signature,
       COUNT(*)           AS cursor_count,
       SUM(sharable_mem) / 1024 AS total_kb,
       MAX(sql_text)      AS sample_sql
FROM   v$sql
WHERE  force_matching_signature != 0
GROUP BY force_matching_signature
HAVING COUNT(*) > 100
ORDER BY cursor_count DESC
FETCH FIRST 20 ROWS ONLY;

Setting CURSOR_SHARING = FORCE is a workaround; fixing the application to use bind variables is the correct solution.

Shared pool shrinks unexpectedly under ASMM

When SGA_TARGET is set and memory pressure increases (e.g., buffer cache needs more memory), ASMM can shrink the shared pool below what the workload needs.

-- Set a floor to prevent ASMM from shrinking below a safe minimum
ALTER SYSTEM SET shared_pool_size = 512M SCOPE = BOTH;

-- Verify the floor is respected
SELECT component, current_size / (1024*1024) AS current_mb, min_size / (1024*1024) AS min_mb
FROM   v$sga_dynamic_components
WHERE  component = 'shared pool';

Parameter	Relationship
`SGA_TARGET`	When > 0, enables ASMM; `SHARED_POOL_SIZE` becomes a minimum floor
`MEMORY_TARGET`	When > 0, enables AMM; `SHARED_POOL_SIZE` still acts as a floor
`SHARED_POOL_RESERVED_SIZE`	Carves out reserved memory within the shared pool for large allocations
`CURSOR_SHARING`	Controls literal-to-bind-variable conversion; affects pool utilization
`OPEN_CURSORS`	Maximum open cursors per session; affects library cache pressure
`SESSION_CACHED_CURSORS`	Number of cursors cached per session; reduces parse calls

Version Notes

Version	Notes
9i	Automatic Shared Memory Management (ASMM) introduced with `SGA_TARGET`
10g	`V$SHARED_POOL_ADVICE` available; ASMM widely adopted
11g	AMM introduced (`MEMORY_TARGET`); `SHARED_POOL_SIZE` becomes floor under both
12c	In-Memory Column Store added as new SGA component competing for pool budget
19c+	No functional change; `SHARED_POOL_RESERVED_SIZE` still requires manual sizing