Database Object Cache Analysis with Thresholds (vdbobja.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 vdbobja.sql script.

The Script

rem  vdbobja.sql
rem
set linesize 132
rem
ttitle 'DB Object Cache'
rem
col owner format a12 heading 'OWNER'
col name format a30 heading 'NAME'
col namespace format a15 heading 'NAMESPACE'
col type format a12 heading 'TYPE'
col sharable_mem format 99999999 heading 'SHARED|MEMORY'
col loads format b999999 heading 'LOADS'
col executions format b999999999 heading 'EXECUTE'
col locks format b9999 heading 'LOCK'
col pins format b99 heading 'PIN'
col kept format a1 heading 'K'
rem
break on report on owner
compute sum of sharable_mem loads executions locks pins -
     on report
rem
select owner,
       name,
       decode( kept, 'YES', '*' ) kept,
       namespace,
       type,
       sharable_mem,
       loads,
       executions,
       locks,
       pins
  from v$db_object_cache
 where ( loads >= &loads
      or executions >= &executions
      or sharable_mem >= &memory )
 order by owner, name;
rem
set linesize 80

What This Script Does

This script analyzes the database object cache (library cache) with configurable thresholds to identify objects that have high memory usage, frequent loads, or high execution counts. It helps DBAs understand shared pool memory utilization, identify frequently used objects, and optimize cache performance.

Key Features

• Threshold-Based Filtering: Shows only objects exceeding specified thresholds
• Memory Analysis: Displays shared memory consumption per object
• Usage Metrics: Shows loads, executions, locks, and pins
• Cache Status: Identifies kept objects (pinned in memory)
• Organized Output: Groups results by owner with summary totals

Usage

Run the script and provide threshold criteria when prompted:

@vdbobja.sql

Input Parameters:

• Loads: Minimum number of loads threshold (e.g., 5, 10, 100)
• Executions: Minimum number of executions threshold (e.g., 100, 1000, 10000)
• Memory: Minimum shared memory threshold in bytes (e.g., 10000, 100000, 1000000)

Required Privileges

SELECT on V$DB_OBJECT_CACHE

Sample Output

                                            DB Object Cache

OWNER        NAME                           K NAMESPACE      TYPE         SHARED  LOADS    EXECUTE LOCK PIN
                                                                          MEMORY
------------ ------------------------------ - --------------- ------------ ------- ------ --------- ---- ---
SYS          DBMS_OUTPUT                    * SQL AREA       PACKAGE      458,752     12    1,245,678   45  12
             DBMS_STATS                     * SQL AREA       PACKAGE      324,576      8      567,890   23   8
             STANDARD                       * SQL AREA       PACKAGE      789,456     15    2,345,123   67  15
                                                                          ------- ------    ---------  --- ---
sum                                                                     1,572,784     35    4,158,691  135  35

HR           EMPLOYEE_PKG                     SQL AREA       PACKAGE      156,432     25      123,456   15   8
             GET_EMPLOYEE_DETAILS             SQL AREA       PROCEDURE     89,765     45      567,891   12   6
             SALARY_CALCULATIONS              SQL AREA       FUNCTION      45,678     67      234,567    8   4
                                                                          ------- ------    ---------  --- ---
sum                                                                       291,875    137      925,914   35  18

SALES        COMMISSION_CALC                  SQL AREA       FUNCTION      67,890     23      345,678    9   5
             ORDER_PROCESSING                 SQL AREA       PACKAGE      123,456     34      789,123   18  10
             VALIDATE_CUSTOMER                SQL AREA       PROCEDURE     34,567     56      123,789    6   3
                                                                          ------- ------    ---------  --- ---
sum                                                                       225,913    113    1,258,590   33  18

                                                                          ======= ======    ========= ==== ===
sum                                                                     2,090,572    285    6,343,195  203  71

Key Output Columns

• OWNER: Schema that owns the cached object
• NAME: Name of the cached object
• K (Kept): ’*’ indicates object is kept (pinned) in memory
• NAMESPACE: Type of namespace (SQL AREA, TABLE/PROCEDURE, etc.)
• TYPE: Object type (PACKAGE, PROCEDURE, FUNCTION, etc.)
• SHARED MEMORY: Bytes of shared memory used by the object
• LOADS: Number of times object has been loaded into cache
• EXECUTE: Number of times object has been executed
• LOCK: Number of locks currently held on the object
• PIN: Number of pins currently held on the object

Understanding Object Cache Metrics

Memory Usage

• SHARED MEMORY: Amount of library cache memory consumed
• High values: Large procedures, packages, or frequently used objects
• Total memory: Sum shows overall shared pool consumption

Load Analysis

• LOADS: How many times object loaded from disk into cache
• High loads: May indicate cache pressure or object aging out
• Multiple loads: Could suggest insufficient shared pool size

Execution Patterns

• EXECUTE: Number of times object has been executed
• High execution: Frequently used objects, good candidates for keeping
• Execution vs Loads: High ratio suggests good cache efficiency

Cache Management

• KEPT objects (*): Objects pinned in memory (won’t age out)
• LOCKS: Current lock holders (active usage)
• PINS: Current pin holders (preventing aging)

Performance Analysis

Memory Efficiency

-- Analyze memory usage patterns:
-- High memory + high executions = efficient use
-- High memory + low executions = potential waste
-- Many loads = possible cache thrashing

Cache Effectiveness

-- Good cache performance indicators:
-- Low LOADS relative to EXECUTIONS
-- Stable high-usage objects remain cached
-- System packages are kept in memory

Shared Pool Optimization

-- Identify optimization opportunities:
-- Large objects with low execution counts
-- Objects with excessive load counts
-- Candidates for KEEP pool placement

Common Use Cases

1. Shared Pool Analysis

   • Identify memory-intensive objects
   • Find frequently used procedures and packages
   • Analyze cache efficiency

2. Performance Optimization

   • Identify candidates for KEEP pool
   • Find objects causing cache pressure
   • Optimize shared pool sizing

3. Memory Management

   • Monitor object memory consumption
   • Plan shared pool allocation
   • Identify memory leaks or growth

4. Application Analysis

   • Understand application object usage patterns
   • Identify critical business logic objects
   • Optimize code deployment strategies

Threshold Selection Guidelines

Memory Thresholds

• Small systems: 10,000 - 50,000 bytes
• Medium systems: 100,000 - 500,000 bytes
• Large systems: 1,000,000+ bytes

Load Thresholds

• Stable cache: 5-10 loads
• Moderate pressure: 20-50 loads
• High pressure: 100+ loads

Execution Thresholds

• Light usage: 100-1,000 executions
• Moderate usage: 10,000-100,000 executions
• Heavy usage: 1,000,000+ executions

Advanced Analysis

Cache Efficiency Calculation

-- Calculate cache hit ratio for objects:
-- Efficiency = EXECUTIONS / LOADS
-- Higher ratios indicate better cache performance
-- Look for objects with low ratios (frequent reloading)

Memory Pressure Analysis

-- Identify potential memory issues:
-- Objects with many loads may be aging out
-- Large objects with low usage waste memory
-- High total memory consumption may need tuning

Keep Pool Candidates

-- Objects suitable for KEEP pool:
-- High execution count
-- Low load count (stable in cache)
-- Critical system packages
-- Frequently used application objects

Optimization Strategies

Object Pinning

-- Pin important objects in memory:
EXEC DBMS_SHARED_POOL.KEEP('HR.EMPLOYEE_PKG', 'P');
EXEC DBMS_SHARED_POOL.KEEP('SALES.ORDER_PROCESSING', 'P');

Shared Pool Sizing

-- Adjust shared pool parameters:
ALTER SYSTEM SET SHARED_POOL_SIZE = 512M;
ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE = 51M;

Memory Management

-- Flush shared pool if needed (use carefully):
ALTER SYSTEM FLUSH SHARED_POOL;

Filter Examples

High Memory Objects

Enter value for loads: 1
Enter value for executions: 1
Enter value for memory: 1000000

Frequently Loaded Objects

Enter value for loads: 50
Enter value for executions: 1
Enter value for memory: 1

High Execution Objects

Enter value for loads: 1
Enter value for executions: 100000
Enter value for memory: 1

Troubleshooting

No Results Returned

• Lower the threshold values
• Check if shared pool contains cached objects
• Verify database activity level

Too Many Results

• Increase threshold values
• Focus on one metric at a time
• Filter by specific owners

High Load Counts

• May indicate shared pool pressure
• Consider increasing shared pool size
• Identify candidates for KEEP pool

Integration with Other Analysis

Shared Pool Monitoring

-- Monitor overall shared pool health:
SELECT pool, name, bytes
FROM v$sgastat
WHERE pool = 'shared pool';

Cache Miss Analysis

-- Analyze library cache miss ratios:
SELECT namespace, gethits, getmisses,
       gethits/(gethits+getmisses)*100 hit_ratio
FROM v$librarycache;

Related Scripts

• vlibcache.sql - Library cache statistics
• vlibrary.sql - Library cache performance
• vsgastat.sql - SGA memory statistics
• show_sga.sql - SGA component analysis

Best Practices

1. Regular Monitoring

   • Run weekly during peak usage periods
   • Track memory consumption trends
   • Monitor cache efficiency metrics

2. Threshold Tuning

   • Adjust thresholds based on system size
   • Focus on relevant metrics for your environment
   • Use multiple threshold combinations

3. Optimization Planning

   • Identify KEEP pool candidates
   • Plan shared pool sizing changes
   • Schedule memory optimization activities