Dynamic marking of Library Cache objects as Kept (PIND)

Toolkit for dynamic marking of Library Cache objects as Kept (PIND)
Doc ID:     301171.1     Type:     SCRIPT
Modified Date :     20-JAN-2009     Status:     PUBLISHED

Abstract
PIND
Pin Dynamically – Tool for automated pinning of often used objects into Shared Pool. Snapshot of most used objects is taken using shutdown trigger and found objects pinned using startup trigger. Cursors are pinned during instance lifetime via DBMS_JOB facility. Mechanism protects against false snapshots and against marking significant part of Shared Pool as kept. Standard DBMS_SHARED_POOL package is utilized for keeping.
Basic idea is based on Note 101627.1 How to Automate Pinning Objects in Shared Pool at Database Startup .
Tested on Oracle 9.2, Oracle 10.1.
Tool description and further information can be found in twin document Note 311689.1 ORA-4031 and DBMS_SHARED_POOL.KEEP remarks and PIND toolkit overview .

Product Name, Product Version
Oracle Server 9.2
Oracle Server 10
Platform     Platform Independent
Date Created     31.12.2004

Instructions

Execution Environment:
SQL*Plus, iSQL*Plus

Access Privileges:
Requires SYS

Usage:
sqlplus “/as sysdba” @PIND_install.sql

Instructions:
– copy&paste bellow appended script into file and run
– make sure startup/shutdown triggers are (“_system_trig_enabled=TRUE” – enabled by default)
– make sure the database jobs are enabled (job_queue_processes=1+)

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.
Description

· Description:  Tool for automated keeping/taking snapshot of objects upon startup/shutdown.
· Successful installation checks:
SQL>  select owner,object_name,object_type,status from dba_objects where owner=’SYS’ and (object_name like ‘PIND%’ or object_name like ‘GPIND%’) ;
OWNER                OBJECT_NAME                    OBJECT_TYPE         STATUS
——————– —————————— ——————- ——-
SYS                  GPIND$DB_OBJECT_CACHE          VIEW                VALID
SYS                  PIND$DB_OBJECT_CACHE           VIEW                VALID
SYS                  PIND_KEPT_OBJ                  TABLE               VALID
SYS                  PIND_OBJ                       PACKAGE             VALID
SYS                  PIND_OBJ                       PACKAGE BODY        VALID
SYS                  PIND_ON_SHUT                   TRIGGER             VALID
SYS                  PIND_ON_START                  TRIGGER             VALID

· Output file naming: PIND_install.lst
References

Script description and some notes releated to ORA-4031 and DBMS_SHARED_POOL are avialable in
Note 311689.1 ORA-4031 and DBMS_SHARED_POOL.KEEP remarks and PIND toolkit overview .

So there is that toolkit to do that cool trick. 🙂

I’m not going to include the script itself here, login to your metalink and grab the script.

Advertisements

About this entry