PL/SQL Object Cache Analysis (vdbobjp.sql)

Script

rem  vdbobjp.sql
rem
set linesize 132
rem
ttitle 'DB Object Cache - PL/SQL'
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 kept = 'YES' )
   and type in ( 'TRIGGER', 'FUNCTION', 'PACKAGE BODY', 'PACKAGE' )
 order by owner, name, type;
rem
set linesize 80

What This Script Does

This script analyzes PL/SQL objects (triggers, functions, packages, and package bodies) in the Oracle shared pool’s object cache. It provides insights into:

Memory consumption by PL/SQL objects
Object load frequency and execution counts
Lock and pin statistics
Objects marked as “kept” (pinned) in memory
Overall library cache efficiency for PL/SQL code

Usage

Run this script from SQL*Plus or SQLcl as a user with SELECT privileges on V$DB_OBJECT_CACHE:

SQL> @vdbobjp.sql
Enter value for loads: 10
Enter value for executions: 100

Parameters

loads: Minimum number of loads to include an object (filters out rarely loaded objects)
executions: Minimum number of executions to include an object (filters out rarely executed objects)

Objects meeting either threshold OR marked as KEPT will be displayed.

Required Privileges

-- Direct privilege required
GRANT SELECT ON V$_DB_OBJECT_CACHE TO username;

-- Or through role
GRANT SELECT_CATALOG_ROLE TO username;

Sample Output

DB Object Cache - PL/SQL

                                                                 SHARED
OWNER        NAME                          K NAMESPACE       TYPE        MEMORY    LOADS    EXECUTE LOCK PIN
------------ ----------------------------- - --------------- ----------- --------- -------- ----------- ----- ---
HR           EMPLOYEE_PKG                  * TABLE/PROCEDURE PACKAGE       45,896       12      15,234    2   1
             EMPLOYEE_PKG                    TABLE/PROCEDURE PACKAGE BODY  78,432       12      15,234    2   1
             UPDATE_SALARY_PROC              TABLE/PROCEDURE FUNCTION      12,456       45       8,923    0   0

SALES        ORDER_PROCESSING              * TABLE/PROCEDURE PACKAGE       92,344        8     125,892    5   3
             ORDER_PROCESSING                TABLE/PROCEDURE PACKAGE BODY 145,678        8     125,892    5   3
             VALIDATE_ORDER_TRIG             TRIGGER         TRIGGER       23,456       89      45,678    1   0
                                                                         --------- -------- -----------
                                                                           400,262      174     365,841

Key Output Columns

OWNER: Schema that owns the PL/SQL object
NAME: Name of the PL/SQL object
K: Asterisk (*) indicates object is KEPT (pinned) in memory
NAMESPACE: Object namespace (usually TABLE/PROCEDURE for PL/SQL)
TYPE: TRIGGER, FUNCTION, PACKAGE, or PACKAGE BODY
SHARED MEMORY: Bytes of shared pool memory consumed
LOADS: Number of times object has been loaded into memory
EXECUTE: Number of times object has been executed
LOCK: Current number of locks on the object
PIN: Current number of pins on the object

Understanding the Metrics

Memory Usage

High Memory: Large packages consuming significant shared pool space
Memory Pressure: Many reloads indicate objects being aged out due to memory pressure

Load Statistics

High Loads: Frequent reloading indicates:
- Object being aged out of shared pool
- Possible shared pool sizing issue
- Need for pinning frequently used objects

Execution Counts

High Executions: Frequently called PL/SQL code
Low Executions with High Memory: Candidates for unpinning

Lock and Pin Counts

Locks: Sessions currently holding the object
Pins: Active executions of the object
High Pin Counts: Very active PL/SQL code

Common Use Cases

1. Identify Memory-Intensive PL/SQL

-- Focus on memory consumption
Enter value for loads: 1
Enter value for executions: 1

2. Find Frequently Reloaded Objects

-- High reload threshold
Enter value for loads: 100
Enter value for executions: 1

3. Monitor Heavily Used Code

-- High execution threshold
Enter value for loads: 1
Enter value for executions: 10000

4. Review Pinned Objects Only

-- Set both to 0 to see all pinned objects
Enter value for loads: 0
Enter value for executions: 0

Performance Analysis

Shared Pool Efficiency

Good: Low reload counts, high execution-to-load ratios
Poor: High reload counts indicate shared pool thrashing

Memory Optimization Strategies

Pin Critical Objects: Use DBMS_SHARED_POOL.KEEP for frequently used packages
Unpin Unused Objects: Remove KEPT status from rarely used large objects
Size Shared Pool: Increase if seeing excessive reloads
Package Consolidation: Combine related small packages to reduce overhead

Red Flags

High LOADS with low EXECUTIONS (thrashing)
Very large SHARABLE_MEM for rarely used objects
Consistently high LOCK/PIN counts (contention)
Many reloads of KEPT objects (severe memory pressure)

Troubleshooting

Common Issues

ORA-00942: Table or view does not exist
- Need SELECT privilege on V$DB_OBJECT_CACHE
No Rows Returned:
- Lower threshold values
- Check if PL/SQL objects exist
Excessive Output:
- Increase threshold values
- Add additional filters to WHERE clause

Performance Considerations

Query is lightweight on V$DB_OBJECT_CACHE
Suitable for production monitoring
Can be scheduled for regular analysis

Library Cache Analysis - Overall library cache statistics
Shared Pool Reserved Analysis - Reserved pool usage
SGA Component Sizes - Shared pool size and free memory
Object Pin Status - Detailed object pinning information
Top SQL by Memory - SQL statements consuming most memory

Version Information

Requires Oracle 8i or higher
V$DB_OBJECT_CACHE view structure unchanged through Oracle 21c
Enhanced statistics available in 12c+