SGA Size Breakdown (vsgasize.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 comprehensive SGA memory analysis by:

• Displaying high-level SGA component sizes from V$SGA
• Breaking down detailed memory allocation from V$SGASTAT
• Excluding free memory to focus on actual usage
• Computing total memory consumption
• Showing both summary and detailed views

Script

rem  vsgasize.sql
rem
ttitle 'SGA Information - Breakdown'
rem
col name format a30 heading 'NAME'
col bytes format 9,999,999,990 heading 'BYTES'
rem
break on report
compute sum of bytes on report
rem
select name, value bytes
  from v$sga
 order by name;
rem
set embedded on
rem
select name, bytes
  from v$sgastat
 where name not in ( 'free memory' )
 order by name;
rem
set embedded off

Usage

SQL> @vsgasize.sql

Required Privileges

• SELECT on V$SGA
• SELECT on V$SGASTAT

Sample Output

SGA Information - Breakdown

NAME                                 BYTES
------------------------------ -----------
Database Buffers               1,073,741,824
Fixed Size                         8,944,472
Redo Buffers                      33,554,432
Variable Size                    419,430,400
                               -----------
sum                            1,535,670,728

NAME                                 BYTES
------------------------------ -----------
ASH buffers                       16,777,216
KCLS trace buffer                    524,288
KGLH0                              8,388,608
KQR L PO                          33,554,432
buffer_cache                   1,073,741,824
db_block_buffers               1,073,741,824
java pool                         16,777,216
large pool                        33,554,432
library cache                     50,331,648
log_buffer                        33,554,432
shared pool                      134,217,728
sql area                          83,886,080

Key Output Sections

V$SGA Summary

• Database Buffers: Buffer cache memory
• Fixed Size: Fixed SGA structures
• Redo Buffers: Log buffer memory
• Variable Size: Shared pool and other variable components

V$SGASTAT Details

• buffer_cache: Database buffer pool
• shared pool: Shared SQL and PL/SQL area
• large pool: Large pool for backup/restore operations
• java pool: Java virtual machine memory
• log_buffer: Redo log buffer
• sql area: SQL statement cache
• library cache: Parsed SQL and PL/SQL

Understanding SGA Components

Buffer Cache

• Purpose: Caches data blocks from disk
• Performance Impact: Larger cache reduces physical I/O
• Tuning: Monitor buffer hit ratios

Shared Pool

• Components: Library cache, data dictionary cache
• Purpose: Caches parsed SQL, PL/SQL, and metadata
• Performance Impact: Reduces parsing overhead

Large Pool

• Purpose: Large memory allocations
• Uses: RMAN, parallel execution, shared servers
• Sizing: Based on backup and parallel requirements

Java Pool

• Purpose: Java virtual machine in the database
• Uses: Java stored procedures, SQLJ
• Sizing: Based on Java usage

Memory Analysis

Total SGA Usage

-- Calculate SGA utilization
SELECT
  ROUND(SUM(value)/1024/1024, 2) AS total_sga_mb,
  ROUND(SUM(value)/1024/1024/1024, 2) AS total_sga_gb
FROM v$sga;

Component Ratios

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

Common Use Cases

1. Memory Tuning

   • Analyze SGA component sizes
   • Identify over/under-allocated areas
   • Plan memory redistribution

2. Capacity Planning

   • Monitor memory growth trends
   • Plan for increased workload
   • Size new environments

3. Performance Analysis

   • Correlate memory allocation with performance
   • Identify memory bottlenecks
   • Validate tuning changes

Performance Indicators

Buffer Cache Analysis

• Hit Ratio: Should be >95% for OLTP
• Size: Typically 60-80% of SGA
• Free Buffers: Monitor V$BUFFER_POOL_STATISTICS

Shared Pool Analysis

• Library Cache: High reload ratio indicates sizing issues
• Dictionary Cache: Miss ratio should be <15%
• Free Memory: Check for fragmentation

Memory Tuning Recommendations

Automatic Memory Management

-- Enable automatic memory management
ALTER SYSTEM SET sga_target = 2G;
ALTER SYSTEM SET memory_target = 4G;

-- Check automatic tuning
SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components;

Manual Tuning

-- Increase buffer cache
ALTER SYSTEM SET db_cache_size = 1200M;

-- Increase shared pool
ALTER SYSTEM SET shared_pool_size = 200M;

-- Increase large pool
ALTER SYSTEM SET large_pool_size = 50M;

Monitoring Scripts

Memory Resize Operations

-- Check recent memory operations
SELECT component, oper_type, oper_mode,
       parameter, initial_size, target_size, final_size,
       start_time, end_time
FROM v$memory_resize_ops
WHERE start_time > SYSDATE - 1
ORDER BY start_time DESC;

SGA Advisor Recommendations

-- Check SGA advisor recommendations
SELECT sga_size, sga_size_factor, estd_db_time, estd_physical_reads
FROM v$sga_target_advice
ORDER BY sga_size;

Memory Pressure Indicators

Low Memory Symptoms

• High library cache reload ratio
• Frequent aging out of shared pool objects
• Buffer cache hit ratio decline
• ORA-04031 errors (unable to allocate memory)

Diagnostic Queries

-- Check for memory pressure
SELECT pool, name, bytes
FROM v$sgastat
WHERE name = 'free memory'
ORDER BY pool;

-- Library cache statistics
SELECT namespace, reloads, invalidations
FROM v$librarycache
WHERE reloads > 0 OR invalidations > 0;

Best Practices

1. Sizing Guidelines

   • Start with automatic memory management
   • Monitor and adjust based on workload
   • Reserve 10-20% for OS and other processes

2. Regular Monitoring

   • Check memory utilization weekly
   • Monitor for ORA-04031 errors
   • Track component growth trends

3. Change Management

   • Test memory changes in development
   • Implement changes during maintenance windows
   • Document baseline and changes

Related Scripts

• SGA Component Analysis
• SGA Statistics
• Memory Resize Operations