Skip to content

Generate Unkeep Shared Pool Objects (unkeep.gen)

What This Script Does

This generator script creates DBMS_SHARED_POOL.UNKEEP statements to unpin objects from the shared pool. It filters by execution count to identify rarely used pinned objects.

The Script

rem  unkeep.gen
rem
select 'execute sys.dbms_shared_pool.unkeep( ''' ||
       owner || '.' || name || '''' ||
       decode( type, 'TRIGGER', ', ''R'' )', ' )' )
  from v$db_object_cache
 where type in ( 'TRIGGER', 'FUNCTION', 'PROCEDURE', 'PACKAGE' )
   and kept = 'YES'
   and executions <= &executions
 order by owner, name
/

Usage

-- Basic usage
@unkeep.gen

-- When prompted, enter:
-- executions: Maximum execution count threshold

Parameters

The script prompts for:

&executions - Maximum execution count (unkeep objects with executions <= this value)

Required Privileges

SELECT ON V$DB_OBJECT_CACHE
EXECUTE ON DBMS_SHARED_POOL

Sample Output

execute sys.dbms_shared_pool.unkeep( 'HR.OLD_PROCEDURE' )
execute sys.dbms_shared_pool.unkeep( 'SCOTT.UNUSED_PACKAGE' )
execute sys.dbms_shared_pool.unkeep( 'APP.LEGACY_TRIGGER', 'R' )

Key Features

Execution-Based Filtering: Only unkeep objects with low execution counts
Object Type Support: Handles procedures, functions, packages, and triggers
Trigger Flag: Correctly uses ‘R’ flag for trigger objects
Performance Optimization: Helps free shared pool memory

Common Use Cases

Unkeep Unused Objects

@unkeep.gen
-- Enter: 0 for executions (never executed)

Unkeep Rarely Used Objects

@unkeep.gen
-- Enter: 100 for executions (less than 100 executions)

Show SGA - View shared pool usage
PGA Analysis - Memory usage analysis