SGA Information (vsga.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides a simple overview of Oracle’s System Global Area (SGA) components showing the size allocation for each major memory component. It displays the current memory allocation in bytes for database buffer cache, shared pool, redo log buffer, and other SGA components with a total summary.

Script

rem  vsga.sql
rem
ttitle 'SGA Information'
rem
col name format a20 heading 'NAME'
col value format 999,999,999,990 heading 'VALUE'
rem
break on report
compute sum of value on report
rem
select name, value
  from v$sga
 order by name;

Usage

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

-- No parameters required
-- Shows current SGA component sizes
-- Values displayed in bytes

Required Privileges

• SELECT on V$SGA
• Available to most database users

Sample Output

SGA Information

NAME                        VALUE
-------------------- ---------------
Database Buffers          1,073,741,824
Fixed Size                    2,927,208
Redo Buffers                 15,728,640
Variable Size               503,316,480
-------------------- ---------------
sum                       1,595,714,152

Key Output Columns

• NAME: SGA component name
• VALUE: Size allocated in bytes

Understanding SGA Components

Major SGA Components

• Database Buffers: Buffer cache for data blocks
• Variable Size: Shared pool, large pool, Java pool, streams pool
• Fixed Size: Internal Oracle structures (fixed overhead)
• Redo Buffers: Redo log buffer cache

Memory Allocation

• Database Buffers: Typically largest component (30-60% of SGA)
• Variable Size: Shared pool and other variable components
• Fixed Size: Small, Oracle-managed overhead
• Redo Buffers: Usually small (few MB to 128MB)

Performance Analysis

SGA Sizing Guidelines

• Total SGA: Usually 60-80% of available system memory
• Buffer Cache: Size based on working set and read patterns
• Shared Pool: Based on SQL complexity and user count
• Redo Buffer: Typically 3MB per CPU core (max 128MB)

Memory Distribution Patterns

-- Calculate component percentages
SELECT name, value,
       ROUND(value / SUM(value) OVER () * 100, 1) pct_of_total
FROM v$sga
ORDER BY value DESC;

Common Use Cases

System Overview

-- Quick SGA sizing check
@vsga.sql
-- Verify total SGA size
-- Check component distribution

Memory Planning

-- Before parameter changes
@vsga.sql
-- Document current allocation
-- Plan memory adjustments

Troubleshooting

-- During memory-related issues
@vsga.sql
-- Compare with configured parameters
-- Verify expected sizes

Advanced Analysis

Convert to Readable Units

-- Show SGA components in MB and GB
SELECT name,
       value bytes,
       ROUND(value/1024/1024, 0) mb,
       ROUND(value/1024/1024/1024, 2) gb,
       ROUND(value / SUM(value) OVER () * 100, 1) pct
FROM v$sga
ORDER BY value DESC;

Compare with Parameters

-- Compare actual vs configured sizes
SELECT 'SGA_TARGET' parameter,
       TO_NUMBER(value) configured_bytes,
       (SELECT SUM(value) FROM v$sga) actual_bytes
FROM v$parameter WHERE name = 'sga_target'
UNION ALL
SELECT 'SGA_MAX_SIZE' parameter,
       TO_NUMBER(value) configured_bytes,
       (SELECT SUM(value) FROM v$sga) actual_bytes
FROM v$parameter WHERE name = 'sga_max_size';

Detailed Component Breakdown

-- More detailed SGA component view
SELECT component,
       current_size bytes,
       ROUND(current_size/1024/1024, 0) mb,
       ROUND(current_size/1024/1024/1024, 2) gb
FROM v$sga_dynamic_components
WHERE current_size > 0
ORDER BY current_size DESC;

Historical SGA Sizing

-- SGA resize operations history
SELECT component, oper_type, oper_mode,
       TO_CHAR(start_time, 'DD-MON-YY HH24:MI') start_time,
       initial_size/1024/1024 initial_mb,
       target_size/1024/1024 target_mb,
       final_size/1024/1024 final_mb,
       status
FROM v$sga_resize_ops
ORDER BY start_time DESC;

SGA Tuning Considerations

Automatic Memory Management

-- Check if using AMM (Automatic Memory Management)
SELECT name, value, description
FROM v$parameter
WHERE name IN ('memory_target', 'memory_max_target',
               'sga_target', 'sga_max_size',
               'pga_aggregate_target');

Component Sizing Guidelines

1. Database Buffers:

   • Size based on working set of frequently accessed data
   • Monitor buffer cache hit ratio and physical reads

2. Shared Pool:

   • Based on number of concurrent users and SQL complexity
   • Monitor library cache and data dictionary cache performance

3. Redo Buffer:

   • Usually 3-5MB per CPU core
   • Maximum effective size around 128MB

Memory Pressure Indicators

-- Check for memory allocation failures
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%memory%'
AND name LIKE '%fail%';

Related Scripts

• Detailed SGA Statistics (vsgastat.sql) - Comprehensive SGA component breakdown
• SGA Components RAC (sgacomponentsrac.sql) - RAC-specific SGA analysis
• Show SGA Statistics (show_sga.sql) - Enhanced SGA display
• PGA Memory Analysis (pga.sql) - PGA memory statistics

System Context

Operating System Memory

-- Check system memory (if available)
SELECT stat_name, value
FROM v$osstat
WHERE stat_name LIKE '%MEMORY%'
OR stat_name LIKE '%MEM%';

Instance Parameters

-- Related memory parameters
SELECT name, value, isdefault, description
FROM v$parameter
WHERE name LIKE '%memory%'
OR name LIKE '%sga%'
OR name LIKE '%target%'
ORDER BY name;