SGA Memory Breakdown (vsgastat.sql)

What This Script Does

This script provides a detailed breakdown of Oracle’s System Global Area (SGA) memory allocation. It shows how memory is distributed across different pools and components, which is essential for memory tuning, troubleshooting memory issues, and capacity planning.

Script

rem  vsgastat.sql
rem
ttitle 'SGA Information - Breakdown'
rem
col pool format a11 heading 'POOL'
col name format a26 heading 'NAME'
col bytes format 9,999,999,990 heading 'BYTES'
rem
break on report on pool skip 1
compute sum of bytes on report pool
rem
select pool, name, bytes
  from v$sgastat
 order by pool, name;

Usage

-- Run the script in SQL*Plus or SQLcl
@vsgastat.sql

-- No parameters required
-- Shows current SGA memory allocation

Required Privileges

SELECT on V$SGASTAT
Generally available to most database users

Sample Output

SGA Information - Breakdown

POOL        NAME                              BYTES
----------- ---------------------------- ----------
            fixed_sga                       123,456
            variable header                  67,890
            library cache                12,345,678
            data transfer cache                1,024
            UNNAMED00                         45,678
                                        ----------
sum                                    12,583,726

java pool   free memory                 33,554,432
                                        ----------
sum                                    33,554,432

large pool  free memory                134,217,728
            PX msg pool                   4,194,304
            UNNAMED02                       524,288
                                        ----------
sum                                   138,936,320

shared pool buffer_handles               1,048,576
            db_files                        32,768
            dictionary cache             16,777,216
            free memory                  67,108,864
            library cache               125,829,120
            miscellaneous                 8,388,608
            sql area                     33,554,432
                                        ----------
sum                                   252,739,584

Key Output Columns

POOL: Memory pool name (shared pool, large pool, java pool, etc.)
NAME: Specific memory component name
BYTES: Size in bytes allocated to this component

Understanding SGA Pools

Shared Pool Components

library cache: SQL statements, packages, procedures
dictionary cache: Data dictionary information
sql area: SQL and PL/SQL code areas
free memory: Available space in shared pool

Large Pool Components

free memory: Available space
PX msg pool: Parallel execution message buffers
backup/restore: RMAN backup operations
shared server: Shared server processes

Java Pool Components

free memory: Available Java memory
Java objects: Java stored procedures and objects

Buffer Cache (not pooled)

db_block_buffers: Data block cache
buffer_handles: Buffer management structures

Performance Analysis

Memory Utilization Patterns

High free memory: Under-utilized pools
Low free memory: Potential memory pressure
Zero free memory: Memory exhaustion risk
Large unnamed areas: Fragmented memory

Pool Sizing Assessment

Shared Pool: Should have 10-20% free memory
Large Pool: Size based on parallel operations
Java Pool: Size based on Java usage
Buffer Cache: Maximize within available memory

Common Use Cases

Memory Tuning

-- Analyze current memory distribution
@vsgastat.sql
-- Identify pools needing adjustment

Troubleshooting Memory Issues

-- During ORA-4031 errors (shared pool)
@vsgastat.sql
-- Check free memory in affected pools

Capacity Planning

-- Baseline current memory usage
@vsgastat.sql
-- Plan memory allocation for growth

Performance Optimization

-- Analyze memory efficiency
@vsgastat.sql
-- Optimize pool sizes for workload

Memory Tuning Guidelines

Shared Pool Tuning

-- If shared pool free memory < 10%
ALTER SYSTEM SET SHARED_POOL_SIZE = <larger_value>;

-- For library cache issues
ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE = <5-10% of shared_pool>;

Large Pool Tuning

-- Size based on parallel operations
ALTER SYSTEM SET LARGE_POOL_SIZE = <based_on_px_usage>;

-- For RMAN operations
-- Size = parallel channels × tape buffer size

Buffer Cache Tuning

-- Increase buffer cache (primary performance factor)
ALTER SYSTEM SET DB_CACHE_SIZE = <larger_value>;

-- For multiple block sizes
ALTER SYSTEM SET DB_2K_CACHE_SIZE = <value>;
ALTER SYSTEM SET DB_4K_CACHE_SIZE = <value>;

Troubleshooting Memory Problems

ORA-4031 Shared Pool Errors

Check free memory:
- If < 5%, increase SHARED_POOL_SIZE
- If fragmented, consider SHARED_POOL_RESERVED_SIZE

Analyze large objects:

SELECT name, bytes
FROM v$sgastat
WHERE pool = 'shared pool'
AND bytes > 1000000
ORDER BY bytes DESC;

Memory Fragmentation

Look for many small unnamed areas

Consider memory advisor recommendations:

SELECT * FROM v$shared_pool_advice
WHERE shared_pool_size_for_estimate > 0;

Java Pool Issues

-- If Java operations failing
ALTER SYSTEM SET JAVA_POOL_SIZE = <larger_value>;

-- Check Java memory usage
SELECT * FROM v$sgastat WHERE pool = 'java pool';

Advanced Analysis

Memory Efficiency Metrics

-- Calculate pool utilization percentages
SELECT pool,
       SUM(bytes) total_bytes,
       SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) free_bytes,
       ROUND((1 - SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) /
              SUM(bytes)) * 100, 2) utilization_pct
FROM v$sgastat
WHERE pool IS NOT NULL
GROUP BY pool;

Memory Growth Analysis

-- Track memory changes over time
-- (Run periodically and compare results)
SELECT pool, name, bytes, SYSDATE snapshot_time
FROM v$sgastat
WHERE bytes > 1000000
ORDER BY pool, bytes DESC;

SGA Summary

-- Overall SGA summary
SELECT name, value/1024/1024 mb
FROM v$sga
UNION ALL
SELECT 'Total SGA', SUM(value)/1024/1024
FROM v$sga;

Buffer Pool Statistics - Buffer cache hit ratios
Shared Pool Analysis - Shared pool efficiency
Memory Parameters - PGA memory usage
Database Parameters - Memory-related parameters