Skip to content
Oracle DBA Scripts & Database Utilities

Database Object Cache Analysis (vdbobj.sql)

What This Script Does

Section titled “What This Script Does”

This script analyzes the database object cache (library cache) by:

  • Grouping cached objects by owner, namespace, and type
  • Showing memory usage for each object category
  • Displaying load and execution statistics
  • Tracking lock and pin activity
  • Providing totals for comprehensive analysis

Script

Section titled “Script”
rem  vdbobj.sql
rem
ttitle 'DB Object Cache'
rem
col owner format a8 heading 'OWNER'
col namespace format a15 heading 'NAMESPACE'
col type format a12 heading 'TYPE'
col object_count format 99999 heading 'OBJECT|COUNT'
col sharable_mem format 99999999 heading 'SHARED|MEMORY'
col loads format b9999 heading 'LOADS'
col executions format b9999999 heading 'EXECUTE'
col locks format b9999 heading 'LOCKS'
col pins format b999 heading 'PINS'
col kept format b999 heading 'KEPT'
rem
break on report on owner skip 1
compute sum of object_count sharable_mem loads executions locks pins kept -
     on report
rem
select owner,
       namespace,
       type,
       count(*) object_count,
       sum(sharable_mem) sharable_mem,
       sum(loads) loads,
       sum(executions) executions,
       sum(locks) locks,
       sum(pins) pins
  from v$db_object_cache
 group by owner, namespace, type;

Usage

Section titled “Usage”
SQL> @vdbobj.sql

Required Privileges

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

Sample Output

Section titled “Sample Output”
DB Object Cache


OWNER    NAMESPACE       TYPE         OBJECT  SHARED LOADS EXECUTE LOCKS PINS KEPT
                                      COUNT   MEMORY
-------- --------------- ------------ ------- ------- ----- ------- ----- ---- ----
SYS      BODY            PACKAGE          45  123456    89   45678    23   67    0
         SPEC            PACKAGE          67  234567   156   78901    45  123    0
         TABLE/PROCEDURE PROCEDURE       123  345678   234   90123    67  234    0
                                    ------- ------- ----- ------- ----- ---- ----
sum                                     235  703701   479  214702   135  424    0


SCOTT    BODY            PACKAGE           3   12345     5    2345     1    8    0
         SPEC            PACKAGE           3   23456     8    4567     2   12    0
         TABLE/PROCEDURE PROCEDURE        12   34567    15    6789     5   23    0
                                    ------- ------- ----- ------- ----- ---- ----
sum                                      18   70368    28   13701     8   43    0


HR       TABLE/PROCEDURE PROCEDURE         8   45678    12    8901     3   15    0
                                    ------- ------- ----- ------- ----- ---- ----
sum                                       8   45678    12    8901     3   15    0

Key Output Columns

Section titled “Key Output Columns”
  • OWNER: Schema that owns the cached objects
  • NAMESPACE: Library cache namespace (BODY, SPEC, TABLE/PROCEDURE, etc.)
  • TYPE: Object type (PACKAGE, PROCEDURE, FUNCTION, etc.)
  • OBJECT COUNT: Number of objects in this category
  • SHARED MEMORY: Memory used in bytes
  • LOADS: Number of times objects were loaded into cache
  • EXECUTE: Number of executions
  • LOCKS: Number of library cache locks held
  • PINS: Number of library cache pins held
  • KEPT: Number of objects marked to keep in cache

Understanding the Metrics

Section titled “Understanding the Metrics”

Object Cache Namespaces

Section titled “Object Cache Namespaces”
  • BODY: Package/procedure bodies
  • SPEC: Package specifications
  • TABLE/PROCEDURE: Procedures, functions, triggers
  • SQL AREA: SQL statements and cursors
  • TRIGGER: Trigger definitions

Performance Indicators

Section titled “Performance Indicators”
  • High loads: Objects frequently aged out and reloaded
  • High locks/pins: Potential contention issues
  • Large memory usage: May need shared pool tuning

Efficiency Analysis

Section titled “Efficiency Analysis”
  • Loads vs Executions: Should be much lower ratio
  • Memory per object: Identifies large objects
  • Keep status: Shows manually kept objects

Common Use Cases

Section titled “Common Use Cases”

  1. Shared Pool Analysis

    • Identify memory consumers
    • Find frequently reloaded objects
    • Assess cache efficiency

  2. Performance Troubleshooting

    • Locate library cache contention
    • Find objects causing reloads
    • Identify oversized objects

  3. Memory Management

    • Plan shared pool sizing
    • Identify objects for keeping
    • Monitor cache utilization

Tuning Recommendations

Section titled “Tuning Recommendations”

High Load Count

Section titled “High Load Count”
  • Consider increasing shared pool size
  • Pin frequently used packages
  • Optimize object sizes

Memory Issues

Section titled “Memory Issues”
  • Review large object definitions
  • Consider object redesign
  • Increase shared pool if needed

Lock/Pin Contention

Section titled “Lock/Pin Contention”
  • Identify blocking sessions
  • Review DDL timing
  • Consider object dependencies

Advanced Analysis

Section titled “Advanced Analysis”

To see individual objects rather than summaries:

SELECT owner, namespace, type, name,
       sharable_mem, loads, executions,
       locks, pins, kept
FROM v$db_object_cache
WHERE loads > 10
ORDER BY loads DESC;

To find objects consuming most memory:

SELECT owner, name, type, sharable_mem
FROM v$db_object_cache
WHERE sharable_mem > 100000
ORDER BY sharable_mem DESC;
Section titled “Related Scripts”