V$SGA - Monitor SGA Memory Components, Resize Operations & Free Memory

V$SGA

Overview

V$SGA provides a high-level summary of the System Global Area (SGA), showing the major named components and their current allocated sizes in bytes. It is the quickest way to confirm total SGA allocation and the breakdown between Fixed SGA, Variable Size, Database Buffers, and Redo Buffers. For granular ASMM/AMM component sizing, DBAs pair it with V$SGA_DYNAMIC_COMPONENTS and V$SGA_RESIZE_OPS.

View Type: Dynamic Performance View Available Since: Oracle 7 Required Privileges: SELECT on V_$SGA or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

V$SGA

Column	Datatype	Description
NAME	VARCHAR2(20)	SGA component name: Fixed Size, Variable Size, Database Buffers, Redo Buffers
VALUE	NUMBER	Size of the component in bytes

V$SGA_DYNAMIC_COMPONENTS

Column	Datatype	Description
COMPONENT	VARCHAR2(64)	Component name (e.g., shared pool, buffer cache, large pool)
CURRENT_SIZE	NUMBER	Currently allocated size in bytes
MIN_SIZE	NUMBER	Minimum size this component has ever been resized to
MAX_SIZE	NUMBER	Maximum size this component has ever been resized to
OPER_COUNT	NUMBER	Number of resize operations performed on this component
LAST_OPER_TYPE	VARCHAR2(13)	Type of the last resize operation: GROW, SHRINK, STATIC, or INITIALIZING
LAST_OPER_MODE	VARCHAR2(9)	Mode: MANUAL or DEFERRED
LAST_OPER_TIME	DATE	Timestamp of the last resize operation
GRANULE_SIZE	NUMBER	Size of the SGA granule in bytes for this component
CON_ID	NUMBER	Container ID (Oracle 12c+)

V$SGA_RESIZE_OPS

Column	Datatype	Description
COMPONENT	VARCHAR2(64)	Component being resized
OPER_TYPE	VARCHAR2(13)	GROW or SHRINK
OPER_MODE	VARCHAR2(9)	MANUAL or DEFERRED
PARAMETER	VARCHAR2(80)	Initialization parameter controlling this component
INITIAL_SIZE	NUMBER	Component size before the operation (bytes)
TARGET_SIZE	NUMBER	Component size Oracle was targeting (bytes)
FINAL_SIZE	NUMBER	Component size actually achieved (bytes)
STATUS	VARCHAR2(9)	COMPLETE, PENDING, or ERROR
START_TIME	DATE	When the resize operation began
END_TIME	DATE	When the resize operation completed (NULL if still in progress)
CON_ID	NUMBER	Container ID (Oracle 12c+)

Essential Queries

Basic Usage

SGA high-level summary with percentages:

SELECT
    s.NAME,
    ROUND(s.VALUE / 1024 / 1024, 2)                      AS size_mb,
    ROUND(s.VALUE / SUM(s.VALUE) OVER () * 100, 1)       AS pct_of_total
FROM
    V$SGA s
ORDER BY
    s.VALUE DESC;

Total SGA allocation vs. SGA_TARGET:

SELECT
    ROUND(SUM(VALUE) / 1024 / 1024, 0)                   AS total_sga_mb,
    ROUND(SUM(VALUE) / 1024 / 1024 / 1024, 2)            AS total_sga_gb
FROM
    V$SGA;

Monitoring Query

Current sizes of all ASMM-managed SGA components — the primary view for tuning shared pool, buffer cache, and large pool:

SELECT
    c.COMPONENT,
    ROUND(c.CURRENT_SIZE  / 1024 / 1024, 0)              AS current_mb,
    ROUND(c.MIN_SIZE      / 1024 / 1024, 0)              AS min_mb,
    ROUND(c.MAX_SIZE      / 1024 / 1024, 0)              AS max_mb,
    c.OPER_COUNT                                          AS resize_ops,
    c.LAST_OPER_TYPE,
    c.LAST_OPER_MODE,
    c.LAST_OPER_TIME,
    ROUND(c.GRANULE_SIZE  / 1024 / 1024, 0)              AS granule_mb
FROM
    V$SGA_DYNAMIC_COMPONENTS c
WHERE
    c.CURRENT_SIZE > 0
ORDER BY
    c.CURRENT_SIZE DESC;

Combined with Other Views

Compare SGA actual allocation against the configured SGA_TARGET and PGA_AGGREGATE_TARGET — verify memory parameters are correctly set:

SELECT
    ROUND(sga.total_sga_bytes  / 1024 / 1024, 0)         AS sga_actual_mb,
    ROUND(p1.VALUE             / 1024 / 1024, 0)          AS sga_target_mb,
    ROUND(p2.VALUE             / 1024 / 1024, 0)          AS pga_target_mb,
    ROUND(p3.VALUE             / 1024 / 1024, 0)          AS memory_target_mb,
    ROUND((sga.total_sga_bytes + p2.VALUE) / 1024 / 1024, 0)
                                                          AS total_db_memory_mb
FROM
    (SELECT SUM(VALUE) AS total_sga_bytes FROM V$SGA)    sga,
    (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'sga_target')            p1,
    (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'pga_aggregate_target')  p2,
    (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'memory_target')         p3;

Free (unallocated) SGA memory available for dynamic resizing:

SELECT
    ROUND(current_size / 1024 / 1024, 0)                 AS free_sga_mb
FROM
    V$SGA_DYNAMIC_COMPONENTS
WHERE
    COMPONENT = 'free memory';

Advanced Analysis

Review the SGA resize history from V$SGA_RESIZE_OPS to understand recent ASMM activity and identify components experiencing frequent churn:

SELECT
    r.COMPONENT,
    r.PARAMETER,
    r.OPER_TYPE,
    r.OPER_MODE,
    ROUND(r.INITIAL_SIZE / 1024 / 1024, 0)               AS initial_mb,
    ROUND(r.TARGET_SIZE  / 1024 / 1024, 0)               AS target_mb,
    ROUND(r.FINAL_SIZE   / 1024 / 1024, 0)               AS final_mb,
    r.STATUS,
    r.START_TIME,
    r.END_TIME,
    ROUND((r.END_TIME - r.START_TIME) * 86400, 1)        AS duration_sec
FROM
    V$SGA_RESIZE_OPS r
ORDER BY
    r.START_TIME DESC
FETCH FIRST 50 ROWS ONLY;

Identify which SGA components have been resized most often — a high OPER_COUNT on shared pool or buffer cache suggests the ASMM target is under-provisioned:

SELECT
    c.COMPONENT,
    c.OPER_COUNT                                          AS total_resize_ops,
    ROUND(c.CURRENT_SIZE / 1024 / 1024, 0)               AS current_mb,
    ROUND(c.MIN_SIZE     / 1024 / 1024, 0)               AS min_mb,
    ROUND(c.MAX_SIZE     / 1024 / 1024, 0)               AS max_mb,
    ROUND((c.MAX_SIZE - c.MIN_SIZE) / 1024 / 1024, 0)    AS swing_mb,
    c.LAST_OPER_TYPE,
    c.LAST_OPER_TIME
FROM
    V$SGA_DYNAMIC_COMPONENTS c
ORDER BY
    c.OPER_COUNT DESC;

Full SGA and PGA memory dashboard — a single-query snapshot suitable for monitoring scripts or OEM metrics:

SELECT
    'SGA Total'                                           AS component,
    ROUND(SUM(VALUE) / 1024 / 1024, 0)                   AS current_mb,
    NULL                                                  AS target_mb
FROM
    V$SGA
UNION ALL
SELECT
    c.COMPONENT,
    ROUND(c.CURRENT_SIZE / 1024 / 1024, 0),
    NULL
FROM
    V$SGA_DYNAMIC_COMPONENTS c
WHERE
    c.CURRENT_SIZE > 0
UNION ALL
SELECT
    'PGA Aggregate Target',
    ROUND(p.VALUE / 1024 / 1024, 0),
    ROUND(p.VALUE / 1024 / 1024, 0)
FROM
    V$PARAMETER p
WHERE
    p.NAME = 'pga_aggregate_target'
ORDER BY
    1;

Common Use Cases

Post-startup verification — Confirm the database started with the expected SGA size by comparing V$SGA total against SGA_TARGET in V$PARAMETER.
ASMM tuning — Use V$SGA_DYNAMIC_COMPONENTS to see whether the buffer cache has shrunk to donate memory to the shared pool (or vice versa), indicating the ASMM target is too small for peak workloads.
Resize operation troubleshooting — V$SGA_RESIZE_OPS with STATUS = ‘PENDING’ indicates a stalled resize, often caused by pinned objects in the shared pool.
Granule size awareness — The GRANULE_SIZE column informs the minimum increment for any SGA component resize; for large SGAs (>1 GB) granules are 16 MB, affecting how precisely memory can be redistributed.
AMM vs ASMM determination — If MEMORY_TARGET > 0 in V$PARAMETER, Oracle manages both SGA and PGA automatically; V$SGA_DYNAMIC_COMPONENTS will show frequent bidirectional resizes between SGA components.
Capacity planning — Track MAX_SIZE across components over time to size SGA_TARGET for future workloads without over-provisioning.

V$SGASTAT — Granular breakdown of shared pool, large pool, and java pool sub-component allocations with free memory tracking.
V$BUFFER_POOL — Buffer cache pool details (DEFAULT, KEEP, RECYCLE) including current size and hit ratio statistics.
V$PGA_TARGET_ADVICE — Oracle’s advisor recommendations for PGA_AGGREGATE_TARGET sizing; pairs with SGA sizing decisions.
V$MEMORY_TARGET_ADVICE — For AMM environments, provides MEMORY_TARGET sizing recommendations analogous to PGA target advice.
V$PGASTAT — PGA usage statistics; together with V$SGA gives a complete picture of instance memory consumption.

Version Notes

Oracle 9i: V$SGA_DYNAMIC_COMPONENTS and V$SGA_RESIZE_OPS introduced with the Automatic Shared Memory Management (ASMM) feature.
Oracle 10g: SGA_TARGET parameter enabled ASMM by default; granule size changed from 4 MB to 16 MB for SGAs larger than 1 GB.
Oracle 11g: Automatic Memory Management (AMM) introduced via MEMORY_TARGET; SGA and PGA can both grow and shrink dynamically. V$MEMORY_DYNAMIC_COMPONENTS and V$MEMORY_RESIZE_OPS added as AMM equivalents.
Oracle 12c: In a RAC CDB, each instance has independent V$SGA rows; use GV$SGA to compare across instances. The streams pool is typically absorbed into the shared pool unless Streams/XStream replication is active.
Oracle 19c: No structural changes to these views. AMM (MEMORY_TARGET) remains unsupported on Linux with HugePages; ASMM (SGA_TARGET) is the recommended approach on Linux.
Oracle 21c / 23ai: No structural schema changes. Vector Memory Pool (for AI vector operations in 23ai) may appear as a component in V$SGA_DYNAMIC_COMPONENTS on databases using vector embeddings.

V$SGA - Monitor SGA Memory Components, Resize Operations & Free Memory

V$SGA

Overview

Key Columns

V$SGA

V$SGA_DYNAMIC_COMPONENTS

V$SGA_RESIZE_OPS

Essential Queries

Basic Usage

Monitoring Query

Combined with Other Views

Advanced Analysis

Common Use Cases

Related Views

Version Notes