Skip to content
Oracle DBA Scripts & Database Utilities

SGA Component Resize Operations (vsga_resize.sql)

What This Script Does

Section titled “What This Script Does”

This script displays the history of SGA component resize operations by:

  • Showing which SGA components have been resized
  • Displaying the operation type (GROW, SHRINK)
  • Indicating the final size after each operation
  • Providing timestamps of when resizes occurred

Script

Section titled “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

Section titled “Usage”
SQL> @vsga_resize.sql

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$SGA_RESIZE_OPS

Sample Output

Section titled “Sample Output”
COMPONENT                 OPER_TYPE         Final STARTED
------------------------- --------- ------------- -------------------------
buffer cache              GROW        536,870,912 15-jan 09:15:23
shared pool               SHRINK      268,435,456 15-jan 09:15:23
large pool                GROW        134,217,728 15-jan 09:20:45
java pool                 SHRINK       67,108,864 15-jan 09:20:45
buffer cache              SHRINK      402,653,184 15-jan 14:30:12
shared pool               GROW        335,544,320 15-jan 14:30:12
streams pool              GROW         33,554,432 15-jan 16:45:30

Key Output Columns

Section titled “Key Output Columns”
  • COMPONENT: SGA component that was resized
  • OPER_TYPE: Type of operation (GROW or SHRINK)
  • Final: Final size of component in bytes after resize
  • STARTED: Timestamp when resize operation began

Understanding SGA Components

Section titled “Understanding SGA Components”

Buffer Cache

Section titled “Buffer Cache”
  • Caches data blocks from datafiles
  • Most frequently resized component
  • Critical for I/O performance

Shared Pool

Section titled “Shared Pool”
  • Contains library cache and data dictionary
  • Stores parsed SQL and PL/SQL
  • Important for parsing performance

Large Pool

Section titled “Large Pool”
  • Used for backup/restore operations
  • Parallel execution message buffers
  • RMAN and shared server processes

Java Pool

Section titled “Java Pool”
  • Java Virtual Machine memory
  • Only relevant if using Oracle JVM
  • Can be set to minimal if not using Java

Streams Pool

Section titled “Streams Pool”
  • Oracle Streams operations
  • Logical standby and replication
  • Advanced Queuing operations

Operation Types

Section titled “Operation Types”

GROW Operations

Section titled “GROW Operations”
  • Component size increased
  • Usually due to memory pressure
  • Indicates higher demand for that component

SHRINK Operations

Section titled “SHRINK Operations”
  • Component size decreased
  • Memory moved to other components
  • Part of automatic rebalancing

Common Use Cases

Section titled “Common Use Cases”

  1. Memory Management Analysis

    • Track automatic memory adjustments
    • Understand workload patterns
    • Validate memory configuration

  2. Performance Troubleshooting

    • Correlate resize events with performance issues
    • Identify memory pressure periods
    • Analyze component usage trends

  3. Capacity Planning

    • Observe memory usage patterns
    • Plan total SGA sizing
    • Understand component requirements

Automatic Memory Management

Section titled “Automatic Memory Management”

MEMORY_TARGET

Section titled “MEMORY_TARGET”

When set, Oracle automatically manages:

  • SGA size allocation
  • Component sizing within SGA
  • PGA memory management

SGA_TARGET

Section titled “SGA_TARGET”

When set without MEMORY_TARGET:

  • Automatic SGA component management
  • Manual PGA management required
  • Component resizes within SGA only

Analysis Tips

Section titled “Analysis Tips”

Frequent Resizing

Section titled “Frequent Resizing”
  • May indicate undersized total memory
  • Consider increasing MEMORY_TARGET or SGA_TARGET
  • Review workload characteristics

Component Patterns

Section titled “Component Patterns”
  • Buffer cache grows: More data access
  • Shared pool grows: More parsing activity
  • Large pool grows: More parallel operations

Time Correlation

Section titled “Time Correlation”
  • Match resize times with application events
  • Correlate with performance problems
  • Identify peak usage periods

Monitoring Recommendations

Section titled “Monitoring Recommendations”

Regular Checks

Section titled “Regular Checks”
-- Check recent resize activity
SELECT component, oper_type, final_size,
       start_time, end_time
FROM v$sga_resize_ops
WHERE start_time > sysdate - 7
ORDER BY start_time DESC;

Current SGA Sizing

Section titled “Current SGA Sizing”
-- Compare with current component sizes
SELECT component, current_size, min_size, max_size
FROM v$sga_dynamic_components
ORDER BY current_size DESC;

Memory Parameters

Section titled “Memory Parameters”
-- Check memory management settings
SELECT name, value
FROM v$parameter
WHERE name IN ('memory_target','memory_max_target',
               'sga_target','sga_max_size')
ORDER BY name;

Troubleshooting

Section titled “Troubleshooting”

No Resize Operations

Section titled “No Resize Operations”
  • Check if automatic memory management is enabled
  • Verify SGA_TARGET or MEMORY_TARGET is set
  • May indicate fixed component sizes

Excessive Resizing

Section titled “Excessive Resizing”
  • Consider increasing total memory allocation
  • Review application workload patterns
  • Check for memory-intensive operations

Failed Resizes

Section titled “Failed Resizes”
  • Check alert log for ORA-00371 errors
  • Verify sufficient memory available
  • Review OS memory limits
Section titled “Related Scripts”