Large Objects in Object Cache (vobj.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 analyzes large PL/SQL objects in the database object cache, focusing on packages, package bodies, procedures, and triggers that consume significant shared pool memory or have high activity levels. It helps identify objects that may be causing shared pool pressure, frequent reloading issues, or memory fragmentation problems. The script is essential for shared pool optimization and PL/SQL performance tuning.

The Script

rem  vobj.sql
rem
ttitle 'Large Objects in Object Cache'
rem
col owner format a8 heading 'OWNER'
col name format a30 heading 'NAME'
col namespace format a15 heading 'NAMESPACE'
col type format a14 heading 'TYPE'
col k format a1 heading 'K'
col sharable_mem format 9999999 heading 'SHARED|MEMORY'
col loads format 999 heading 'LOAD'
col executions format 99999999 heading 'EXECUTE'
col locks format 9999 heading 'LOCKS'
col pins format 999 heading 'PINS'
rem
break on report
compute sum of sharable_mem loads executions on report
rem
select owner,
       name,
       type,
       decode( kept, 'YES', '*' ) k,
       sharable_mem,
/*
       namespace,
       locks,
       pins,
*/
       loads,
       executions
  from v$db_object_cache
 where kept = 'NO'
   and type in ( 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER' )
   and ( loads >= &loads
      or sharable_mem >= &memory
      or executions >= &executions )
 order by owner, name
/

Key Features

• PL/SQL Object Focus: Analyzes packages, procedures, triggers, and package bodies
• Memory Usage Analysis: Shows shared memory consumption by objects
• Activity Metrics: Displays loads and execution counts
• Configurable Thresholds: Multiple filtering criteria for targeted analysis
• Kept Object Exclusion: Focuses on non-pinned objects that can be aged out
• Shared Pool Optimization: Supports memory management and performance tuning
• Summary Statistics: Provides totals for memory, loads, and executions

Usage

@vobj.sql

Input Parameters:

• loads: Minimum number of loads threshold
• memory: Minimum shared memory usage threshold (bytes)
• executions: Minimum execution count threshold

Required Privileges

SELECT on V$DB_OBJECT_CACHE

Sample Output

                                      Large Objects in Object Cache

OWNER    NAME                           TYPE           K SHARED  LOAD  EXECUTE
                                                         MEMORY
-------- ------------------------------ -------------- - ------- ---- --------
HR       EMPLOYEE_MANAGEMENT_PKG        PACKAGE          1234567   12  9876543
HR       EMPLOYEE_MANAGEMENT_PKG        PACKAGE BODY     2345678   15  9876543
HR       CALCULATE_SALARY               PROCEDURE         345678    8  1234567
HR       AUDIT_EMPLOYEE_CHANGES         TRIGGER           123456    5   567890

SALES    ORDER_PROCESSING_PKG           PACKAGE          3456789   23 12345678
SALES    ORDER_PROCESSING_PKG           PACKAGE BODY     4567890   25 12345678
SALES    VALIDATE_ORDER                 PROCEDURE         567890   12  2345678
SALES    UPDATE_INVENTORY               TRIGGER           234567    7   890123

FINANCE  ACCOUNTING_UTILS_PKG           PACKAGE          2345678   18  5678901
FINANCE  ACCOUNTING_UTILS_PKG           PACKAGE BODY     3456789   20  5678901
FINANCE  CALCULATE_TAX                  PROCEDURE         456789   10  1567890

                                                        -------- ---- --------
sum                                                     19740435  155 54000000

Key Output Columns

• OWNER: Schema owner of the object
• NAME: Object name
• TYPE: Object type (PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER)
• K: Kept status indicator (* = kept in memory, blank = not kept)
• SHARED MEMORY: Bytes of shared pool memory consumed
• LOAD: Number of times object has been loaded into memory
• EXECUTE: Number of times object has been executed

Understanding Object Cache Metrics

Memory Usage Analysis

Shared Memory Consumption

-- Memory usage interpretation:
-- Small objects: <100KB (100,000 bytes)
-- Medium objects: 100KB-1MB (1,000,000 bytes)
-- Large objects: 1MB-10MB (10,000,000 bytes)
-- Very large objects: >10MB (potential issues)

Memory Fragmentation Impact

-- Fragmentation considerations:
-- Large objects cause fragmentation
-- Frequent loading/aging of large objects
-- Impact on shared pool efficiency
-- Memory allocation difficulties

Load Pattern Analysis

Load Frequency Assessment

-- Load count interpretation:
-- Low loads (1-5): Good memory retention
-- Moderate loads (5-20): Some aging pressure
-- High loads (20-50): Frequent aging/reloading
-- Very high loads (>50): Memory pressure issues

Performance Impact

-- Load performance implications:
-- Each load requires parsing and compilation
-- CPU overhead for object loading
-- Shared pool latch contention
-- Application response time impact

Execution Pattern Analysis

Execution Frequency

-- Execution analysis:
-- High executions with low loads: Good efficiency
-- High executions with high loads: Memory issues
-- Low executions with high memory: Optimization candidates
-- Unbalanced patterns: Investigation needed

Common Use Cases

1. Shared Pool Optimization

   • Identify objects consuming excessive memory
   • Find candidates for memory pinning
   • Support shared pool sizing decisions
   • Optimize memory allocation efficiency

2. Performance Troubleshooting

   • Diagnose shared pool pressure issues
   • Identify frequently reloaded objects
   • Support parse-related performance problems
   • Resolve memory fragmentation issues

3. Capacity Planning

   • Assess shared pool memory requirements
   • Plan for application growth
   • Support infrastructure sizing
   • Monitor memory consumption trends

4. Application Analysis

   • Analyze PL/SQL usage patterns
   • Identify inefficient object designs
   • Support code optimization efforts
   • Plan application memory requirements

Advanced Analysis

Memory Optimization Strategies

Object Pinning Candidates

-- Pinning consideration criteria:
-- High execution count with high loads
-- Critical business functionality
-- Large memory consumption
-- Frequent access patterns

Pinning Implementation

-- Pin objects in shared pool:
EXECUTE DBMS_SHARED_POOL.KEEP('schema.package_name', 'P');
EXECUTE DBMS_SHARED_POOL.KEEP('schema.procedure_name', 'P');

-- Verify pinning status:
SELECT name, kept FROM v$db_object_cache WHERE kept = 'YES';

Load Pattern Optimization

Reducing Object Loads

-- Load reduction strategies:
-- Increase shared pool size
-- Pin frequently used objects
-- Optimize object design for smaller footprint
-- Reduce memory fragmentation

Application Design Improvements

-- Design optimization:
-- Smaller, more focused packages
-- Modular procedure design
-- Efficient PL/SQL coding practices
-- Memory-conscious development

Performance Impact Assessment

Shared Pool Pressure Indicators

-- Pressure indicators from high loads:
-- Increased parse times
-- Library cache misses
-- Shared pool latch contention
-- Application performance degradation

Memory Efficiency Calculation

-- Efficiency metrics:
-- Executions per load ratio
-- Memory usage per execution
-- Load frequency trends
-- Overall memory utilization

Filtering Examples

High Memory Consumers

-- Find memory-intensive objects:
Enter value for loads: 0
Enter value for memory: 1000000
Enter value for executions: 0

Frequently Loaded Objects

-- Find objects with loading issues:
Enter value for loads: 10
Enter value for memory: 0
Enter value for executions: 0

High Activity Objects

-- Find frequently executed objects:
Enter value for loads: 0
Enter value for memory: 0
Enter value for executions: 100000

Comprehensive Analysis

-- Broad analysis of significant objects:
Enter value for loads: 5
Enter value for memory: 500000
Enter value for executions: 10000

Optimization Strategies

Memory Management

Shared Pool Sizing

-- Sizing considerations:
-- Total memory consumption by objects
-- Growth trends and projections
-- Peak usage patterns
-- Memory allocation efficiency

Memory Pinning Strategy

-- Strategic pinning:
-- Critical business objects
-- Frequently used utilities
-- Large objects with high execution
-- Objects with loading issues

Object Design Optimization

Package Optimization

-- Package design improvements:
-- Reduce package size and complexity
-- Separate initialization code
-- Optimize variable declarations
-- Minimize global state

Procedure Optimization

-- Procedure optimization:
-- Reduce local variable usage
-- Optimize cursor declarations
-- Minimize exception handling overhead
-- Improve algorithm efficiency

Integration with Performance Monitoring

Automated Monitoring

Alert Configuration

-- Alert criteria:
-- High memory consumption thresholds
-- Excessive load counts
-- Memory efficiency degradation
-- Shared pool pressure indicators

Trend Analysis

-- Monitoring trends:
-- Memory consumption growth
-- Load frequency increases
-- Execution pattern changes
-- Performance impact correlation

Historical Analysis

Performance Correlation

-- Correlate with performance metrics:
-- Parse time increases
-- Library cache miss rates
-- Shared pool allocation failures
-- Application response time impact

Capacity Planning

-- Planning considerations:
-- Memory requirement projections
-- Object growth trends
-- Performance scaling requirements
-- Infrastructure capacity needs

Troubleshooting Applications

Shared Pool Issues

Memory Pressure Problems

-- Memory pressure symptoms:
-- Frequent object aging
-- Parse performance degradation
-- Library cache misses
-- Allocation failures

Resolution Strategies

-- Problem resolution:
-- Increase shared pool size
-- Pin critical objects
-- Optimize object design
-- Reduce memory fragmentation

Performance Degradation

Parse-Related Issues

-- Parse performance problems:
-- High load counts indicate recompilation
-- CPU overhead from frequent parsing
-- Latch contention on library cache
-- Application response time impact

Optimization Approaches

-- Performance improvement:
-- Object pinning for stability
-- Code optimization for smaller footprint
-- Memory allocation optimization
-- Application design improvements

Best Practices

Memory Management

Proactive Management

-- Management practices:
-- Regular memory usage reviews
-- Strategic object pinning
-- Shared pool sizing optimization
-- Performance impact monitoring

Development Guidelines

-- Development standards:
-- Memory-efficient PL/SQL design
-- Modular package architecture
-- Resource management practices
-- Performance testing requirements

Monitoring Strategy

Regular Assessment

-- Assessment schedule:
-- Daily shared pool health checks
-- Weekly object analysis
-- Monthly optimization reviews
-- Quarterly capacity planning

Performance Baselines

-- Baseline establishment:
-- Normal memory consumption patterns
-- Typical load frequencies
-- Expected execution ratios
-- Performance benchmarks

Related Scripts

• vlibcache.sql - Library cache analysis
• vsga.sql - SGA memory analysis
• vsgastat.sql - SGA statistics
• dobject.sql - Database object analysis

Security Considerations

Access Control

Information Sensitivity

-- Security considerations:
-- Object names may reveal application structure
-- Memory usage patterns expose system architecture
-- Execution patterns indicate business processes
-- Access should be limited to authorized personnel

Summary

This script is essential for:

1. Shared Pool Optimization - Identifying and managing objects that consume significant shared pool memory
2. Performance Tuning - Resolving parse-related performance issues and memory pressure problems
3. Capacity Planning - Understanding memory requirements and planning shared pool sizing
4. Application Optimization - Supporting PL/SQL code optimization and design improvements
5. System Monitoring - Proactive monitoring of object cache health and performance impact