SGA Resize Operations History (vsga_resize.sql)

What This Script Does

This script displays the history of automatic SGA (System Global Area) resize operations, showing:

Which SGA components were resized
Whether components grew or shrank
Final size after the operation
When the resize operation occurred

The Script

set linesize 90
set pagesize 60
column component format a25
column Final format 99,999,999,999
column STARTED format A25

SELECT COMPONENT ,OPER_TYPE,FINAL_SIZE Final,to_char(start_time,'dd-mon hh24:mi:ss') STARTED FROM V$SGA_RESIZE_OPS;

Usage

-- Basic usage
@vsga_resize.sql

-- No parameters required

Required Privileges

SELECT ON V$SGA_RESIZE_OPS

Sample Output

COMPONENT                 OPER_TYPE          FINAL STARTED
------------------------- ----------- -------------- -------------------------
shared pool               GROW           536,870,912 06-dec 10:15:32
buffer cache              SHRINK         805,306,368 06-dec 10:15:32
shared pool               GROW           671,088,640 06-dec 11:45:18
buffer cache              SHRINK         671,088,640 06-dec 11:45:18
large pool                GROW            67,108,864 06-dec 14:22:45
shared pool               SHRINK         603,979,776 06-dec 14:22:45
java pool                 GROW            67,108,864 06-dec 15:30:12
buffer cache              SHRINK         603,979,776 06-dec 15:30:12

Key Output Columns

COMPONENT - SGA component that was resized:
- shared pool
- buffer cache
- large pool
- java pool
- streams pool
OPER_TYPE - Operation type:
- GROW - Component size increased
- SHRINK - Component size decreased
FINAL - Final size in bytes after the operation
STARTED - Timestamp when the resize operation began

Common Use Cases

Monitor Automatic Memory Management

-- Check recent SGA adjustments
@vsga_resize.sql
-- Look for frequent resize operations

Troubleshoot Memory Issues

-- When experiencing ORA-04031 errors
@vsga_resize.sql
-- Check if shared pool is being shrunk too small

Capacity Planning

-- Analyze resize patterns over time
@vsga_resize.sql
-- Identify components that frequently need more memory

Understanding SGA Resize Operations

Automatic Memory Management:

When SGA_TARGET is set, Oracle automatically adjusts component sizes
Components grow when they need more memory
Components shrink to provide memory to others
Operations occur in pairs (one shrinks, another grows)

Common Patterns:

Frequent resizing may indicate memory pressure
Large resize operations may cause temporary performance impact
Consistent growth of one component suggests it needs more minimum memory

Additional Analysis

-- Current SGA component sizes
SELECT component, current_size/1024/1024 size_mb
FROM v$sga_dynamic_components;

-- SGA parameters
SHOW PARAMETER sga_target
SHOW PARAMETER sga_max_size
SHOW PARAMETER memory_target

-- Check for ORA-04031 errors
SELECT * FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-04031%'
ORDER BY originating_timestamp DESC;

Troubleshooting Tips

Excessive Resizing:

Consider increasing SGA_TARGET
Set minimum sizes for critical components (e.g., SHARED_POOL_SIZE)
Monitor workload patterns causing resizes

Performance Impact:

Resize operations can cause brief pauses
Monitor during resize operations for impact
Schedule large memory changes during maintenance windows

Show SGA Statistics - Current SGA breakdown
PGA Memory Analysis - PGA memory usage
Database Status - Overall database status