Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle dbms_shared_pool Tips

Oracle Tips by Burleson Consulting

 


Inside the Oracle dbms_shared_pool



execute dbms_shared_pool.keep('SYS.DICTIONARY_OBJ_OWNER','P')
execute dbms_shared_pool.keep('SYS.DICTIONARY_OBJ_NAME','P')
execute dbms_shared_pool.keep('SYS.PLITBLM','P')
execute dbms_shared_pool.keep('SYS.DBMS_STANDARD','P')

The simple act of pinning a package, procedure or function using the Oracle dbms_shared_pool package will call it into memory.

The Oracle dbms_shared_pool package may have to be created in earlier releases of Oracle. The dbms_shared_pool package is built using the dbmspool.sql and prvtpool.plb scripts located in (UNIX) $ORACLE_HOME/rdbms/admin or (NT) x:\orant\rdbms\admin (where x: is the home drive for the install).

Description of the DBMS_SHARED_POOL package:

PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THRESHOLD_SIZE                 NUMBER                  IN

PROCEDURE DBMS_SHARED_POOL.KEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT

PROCEDURE DBMS_SHARED_POOL.PURGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT
 HEAPS                          NUMBER                  IN     DEFAULT

PROCEDURE DBMS_SHARED_POOL.SIZES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MINSIZE                        NUMBER                  IN

PROCEDURE DBMS_SHARED_POOL.UNKEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT

  

dbms_shared_poolprovides a simple but useful programmatic access to the Oracle SGA’s shared pool memory section, specifically where cursors and PL/SQL code are stored. The types of objects supported include:

  • P           =         package/procedure/function

  • Q          =         sequence

  • R           =         trigger

  • T           =         type

  • JS          =         java source

  • JC         =         java class

  • JR         =         java resource

  •  JD         =         java shared data

  • C           =         cursor

The PL/SQL programmatic API is as follows:

 

  • Show objects in shared pool > specified size
    procedure sizes(minsize number);

  • Keep or pin the object in the shared pool (immune to aging out)
    procedure keep(name varchar2, flag char DEFAULT 'P');

  • Unkeep or unpin the object in the shared pool (resume normal aging out)
    procedure unkeep(name varchar2, flag char DEFAULT 'P');

  • Forces the object to immediately agre out of the shared pool
    procedure purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);

  • Defines the effective size limit for a pin object request failing
    procedure aborted_request_threshold(threshold_size number);

 

A very useful example of using the dbms_shared_poolpackage would be to create a database startup trigger to “pin” the most frequently used PL/SQL packages into the shared pool, assuming there is sufficient memory allocation to support it. The pin_top4_procedures.sql PL/SQL code shown next is an example of such a trigger for the specific database where it is known that the four packages chosen see relatively heavy use due to the nature of the database’s PL/SQL code:

 

<  pin_top4_procedures.sql script

 

CREATE OR REPLACE TRIGGER SYS.PIN_HIGH_USE_PLSQL

AFTER STARTUP

ON DATABASE

BEGIN

  DBMS_SHARED_POOL.KEEP('DBMS_APPLICATION_INFO');

  DBMS_SHARED_POOL.KEEP('DBMS_SQL');

  DBMS_SHARED_POOL.KEEP('STANDARD');

  DBMS_SHARED_POOL.KEEP('DBMS_UTILITY');

END;

/

 

The following query can be run to help identify which packages might want to be included as pin candidates for the specific database.

SELECT owner, name, type, sharable_mem, loads, kept, executions, locks, pins
FROM v$db_object_cache outer
WHERE type in ('PROCEDURE','PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'TRIGGER', 'SEQUENCE')
  AND kept = 'NO'
  and executions > ( select 2*avg(count(executions))
                     FROM v$db_object_cache inner
                     WHERE type in ('PROCEDURE','PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'TRIGGER', 'SEQUENCE')
                     AND kept = 'NO'
                     group by executions)
  and loads      > ( select 2*avg(count(loads))
                     FROM v$db_object_cache inner
                     WHERE type in ('PROCEDURE','PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'TRIGGER', 'SEQUENCE')
                     AND kept = 'NO'
                     group by loads)
ORDER BY executions DESC;

 

 

 

  
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.