Shared Pool Reserved Area Analysis (vshpresrv.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 queries Oracle views to provide database information via the vshpresrv.sql script.

The Script

rem  vshpresrv.sql
rem
ttitle 'Shared Pool Reserved'

set linesize 85
rem
column FREE_SPACE       heading "FREE|SPACE"
column AVG_FREE_SIZE    heading "AVG|FREE|SIZE"
column FREE_COUNT       heading "FREE|COUNT"
column MAX_FREE_SIZE    heading "MAX|FREE|SIZE"
column USED_SPACE       heading "USED|SPACE"
column AVG_USED_SIZE    heading "AVG|USED|SIZE"
column USED_COUNT    heading "USED|COUNT"
column MAX_USED_SIZE    heading "MAX|USED|SIZE"
column REQUESTS    heading "REQUESTS"
column REQUEST_MISSES    heading "REQUEST|MISSES"
column LAST_MISS_SIZE    heading "LAST|MISS|SIZE"
column MAX_MISS_SIZE    heading "MAX|MISS|SIZE"
column REQUEST_FAILURES    heading "REQUEST|FAILURES"
column LAST_FAILURE_SIZE    heading "LAST|FAILURE|SIZE"
column ABORTED_REQUEST_THRESHOLD    heading "ABORTED|REQUEST|THRESHOLD"
column ABORTED_REQUESTS    heading "ABORTED|REQUESTS"
column LAST_ABORTED_SIZE    heading "LAST|ABORTED|SIZE"
select * from v$shared_pool_reserved
/

What This Script Does

This script provides detailed analysis of the Oracle shared pool reserved area, which is a dedicated portion of the shared pool designed to satisfy large memory requests (typically > 5KB). The reserved area helps prevent ORA-4031 errors for large allocations by keeping a separate memory pool that cannot be fragmented by smaller allocations.

Key Features

• Memory Utilization Analysis: Shows free and used space in the reserved area
• Allocation Statistics: Displays average and maximum allocation sizes
• Request Tracking: Monitors successful and failed allocation requests
• Failure Analysis: Tracks request misses and failures with size information
• Threshold Monitoring: Shows aborted request thresholds and counts
• Fragmentation Assessment: Provides insights into memory fragmentation

Usage

@vshpresrv.sql

No parameters required - displays current shared pool reserved area statistics.

Required Privileges

SELECT on V$SHARED_POOL_RESERVED

Sample Output

                                        Shared Pool Reserved

     FREE    AVG   FREE     MAX     USED    AVG   USED     MAX             REQUEST     LAST     MAX REQUEST      LAST ABORTED   ABORTED     LAST
    SPACE   FREE  COUNT    FREE    SPACE   USED  COUNT    USED REQUESTS   MISSES MISS SIZE MISS SIZE FAILURES FAILURE SIZE REQUEST REQUESTS ABORTED
                  SIZE                     SIZE                                                                    SIZE THRESHOLD              SIZE
--------- ------ ----- ------- --------- ------ ----- ------- -------- -------- --------- --------- -------- ------------ ------- -------- -------
2,097,152  8,192   256  65,536 1,048,576  4,096   256  32,768   12,345        5    24,576    32,768        2       28,672   4,096        0       0

Key Output Columns

Free Space Metrics

• FREE_SPACE: Total free space available in the reserved area (bytes)
• AVG_FREE_SIZE: Average size of free memory chunks (bytes)
• FREE_COUNT: Number of free memory chunks
• MAX_FREE_SIZE: Largest contiguous free memory chunk (bytes)

Used Space Metrics

• USED_SPACE: Total space currently allocated in the reserved area (bytes)
• AVG_USED_SIZE: Average size of allocated memory chunks (bytes)
• USED_COUNT: Number of allocated memory chunks
• MAX_USED_SIZE: Largest allocated memory chunk (bytes)

Request Statistics

• REQUESTS: Total number of allocation requests to the reserved area
• REQUEST_MISSES: Number of requests that couldn’t be satisfied from reserved area
• LAST_MISS_SIZE: Size of the most recent failed allocation request (bytes)
• MAX_MISS_SIZE: Largest allocation request that failed (bytes)

Failure Analysis

• REQUEST_FAILURES: Number of allocation requests that completely failed
• LAST_FAILURE_SIZE: Size of the most recent allocation failure (bytes)
• ABORTED_REQUEST_THRESHOLD: Size threshold above which requests are aborted
• ABORTED_REQUESTS: Number of requests aborted due to size exceeding threshold
• LAST_ABORTED_SIZE: Size of the most recent aborted request (bytes)

Understanding Shared Pool Reserved Area

Purpose and Function

Large Object Allocation

-- Reserved area is used for:
-- 1. Large PL/SQL packages and procedures (> 5KB)
-- 2. Large SQL statements and cursors
-- 3. Java objects in the database
-- 4. Large anonymous PL/SQL blocks
-- 5. Triggers and functions with significant code

Fragmentation Prevention

-- Benefits:
-- 1. Protects large allocations from fragmentation
-- 2. Prevents small objects from consuming space needed for large ones
-- 3. Reduces ORA-4031 errors for critical large objects
-- 4. Provides more predictable memory allocation

Configuration Parameters

SHARED_POOL_RESERVED_SIZE

-- Controls reserved area size:
-- Default: 5% of SHARED_POOL_SIZE
-- Minimum: 5KB
-- Maximum: 50% of SHARED_POOL_SIZE
-- Recommendation: 10-20% for systems with large packages

_SHARED_POOL_RESERVED_MIN_ALLOC

-- Hidden parameter controlling minimum allocation size:
-- Default: 5120 bytes (5KB)
-- Determines when reserved area is used
-- Allocations >= this size use reserved area

Performance Analysis

Health Indicators

Good Reserved Area Health

-- Positive signs:
-- REQUEST_FAILURES = 0 (no allocation failures)
-- REQUEST_MISSES < 5% of REQUESTS (low miss rate)
-- MAX_FREE_SIZE > typical large object size
-- ABORTED_REQUESTS = 0 (no size-based aborts)

Reserved Area Problems

-- Warning signs:
-- REQUEST_FAILURES > 0 (allocation failures occurring)
-- REQUEST_MISSES > 10% of REQUESTS (high miss rate)
-- MAX_FREE_SIZE < 10KB (severe fragmentation)
-- ABORTED_REQUESTS > 0 (objects too large for area)

Memory Utilization Assessment

Appropriate Sizing

-- Well-sized reserved area shows:
-- FREE_SPACE = 20-50% of total reserved size
-- AVG_FREE_SIZE > 5KB (adequate for new allocations)
-- FREE_COUNT < 100 (not overly fragmented)
-- USED_SPACE = 50-80% of total reserved size

Sizing Issues

-- Too small indicators:
-- FREE_SPACE < 10% of total (insufficient free space)
-- REQUEST_MISSES increasing over time
-- REQUEST_FAILURES > 0 (hard failures)

-- Too large indicators:
-- FREE_SPACE > 80% of total (wasted memory)
-- USED_SPACE very low compared to total size
-- Could allocate memory to main shared pool instead

Common Use Cases

1. ORA-4031 Troubleshooting

   • Diagnose shared pool memory allocation failures
   • Identify if large objects are causing fragmentation
   • Determine if reserved area sizing is appropriate
   • Plan memory allocation improvements

2. Performance Tuning

   • Optimize shared pool reserved area sizing
   • Reduce memory allocation latency
   • Improve large object compilation performance
   • Prevent application errors due to memory issues

3. Capacity Planning

   • Assess memory requirements for large objects
   • Plan for application deployment with large packages
   • Determine optimal shared pool configuration
   • Monitor memory usage trends over time

4. System Health Monitoring

   • Regular monitoring of memory allocation success
   • Track fragmentation trends
   • Identify memory allocation patterns
   • Prevent memory-related performance issues

Optimization Strategies

Reserved Area Sizing

Increase Reserved Area

-- Consider increasing when:
-- REQUEST_FAILURES > 0
-- REQUEST_MISSES > 5% of REQUESTS
-- MAX_FREE_SIZE consistently < 10KB
-- Large packages failing to compile

-- Increase SHARED_POOL_RESERVED_SIZE:
ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE = 50M SCOPE=SPFILE;

Decrease Reserved Area

-- Consider decreasing when:
-- FREE_SPACE consistently > 80%
-- USED_SPACE very low
-- No large objects in the system
-- Main shared pool experiencing pressure

-- But ensure adequate space for future needs

Fragmentation Management

Reduce Fragmentation

-- Strategies:
-- 1. Increase reserved area size
-- 2. Use SHARED_POOL_RESERVED_SIZE wisely
-- 3. Avoid frequent invalidation of large objects
-- 4. Consider shared pool flushing during maintenance

Monitor Fragmentation Trends

-- Track over time:
-- FREE_COUNT (should remain relatively stable)
-- AVG_FREE_SIZE (should not decrease significantly)
-- MAX_FREE_SIZE (should accommodate largest objects)

Advanced Analysis

Historical Monitoring

-- Track metrics over time:
-- Request miss rates trending upward
-- Failure rates increasing
-- Fragmentation worsening (smaller average free sizes)
-- Usage patterns changing

Correlation Analysis

-- Correlate with:
-- Library cache miss rates
-- Shared pool reload events
-- Large package compilation activity
-- Application deployment schedules

Workload Assessment

-- Consider workload characteristics:
-- Number and size of large PL/SQL packages
-- Frequency of large object compilation
-- Anonymous PL/SQL block usage
-- Java object usage in the database

Troubleshooting Common Issues

ORA-4031 in Reserved Area

Diagnosis Steps

-- 1. Check REQUEST_FAILURES (should be 0)
-- 2. Review LAST_FAILURE_SIZE (size of failed allocation)
-- 3. Compare with MAX_FREE_SIZE (largest available chunk)
-- 4. Assess overall FREE_SPACE availability

Resolution Strategies

-- Short-term:
-- Flush shared pool: ALTER SYSTEM FLUSH SHARED_POOL;
-- Compile specific large objects during low usage

-- Long-term:
-- Increase SHARED_POOL_RESERVED_SIZE
-- Review and optimize large PL/SQL packages
-- Consider application architecture changes

High Request Miss Rates

Analysis

-- High misses indicate:
-- Reserved area too small for demand
-- Fragmentation preventing allocation
-- Competition between large objects
-- Sizing parameters need adjustment

Solutions

-- 1. Increase reserved area size
-- 2. Monitor and reduce fragmentation
-- 3. Optimize large object design
-- 4. Schedule large object operations

Integration with Monitoring

Regular Health Checks

-- Include in daily monitoring:
-- REQUEST_FAILURES count
-- REQUEST_MISSES rate
-- Fragmentation metrics
-- Space utilization percentages

Alerting Thresholds

-- Set alerts for:
-- REQUEST_FAILURES > 0
-- REQUEST_MISSES > 10% of REQUESTS
-- FREE_SPACE < 20% of total reserved size
-- ABORTED_REQUESTS > 0

Trend Analysis

-- Monitor trends in:
-- Memory allocation success rates
-- Fragmentation levels over time
-- Usage patterns and growth
-- Performance impact correlation

Related Scripts

• vlibcache.sql - Library cache analysis
• vsgastat.sql - SGA memory component analysis
• show_sga.sql - SGA structure overview
• vsga.sql - SGA information summary

Best Practices

1. Sizing Guidelines

   • Start with 10-15% of shared pool size
   • Monitor and adjust based on workload
   • Ensure sufficient space for largest objects
   • Plan for growth and peak usage

2. Monitoring Strategy

   • Include in regular health checks
   • Set appropriate alerting thresholds
   • Track trends over time
   • Correlate with application deployments

3. Optimization Approach

   • Prevent rather than react to issues
   • Size appropriately from the start
   • Regular review and adjustment
   • Consider application design impact