Power of DBMS_SHARED_POOL  Package

 www.SamOraTech.com                                                                                                                                   By   Sameer Wadhwa                                                                      

  In   this article I  have tried to explain the power and importance of DBMS_SHARED_POOL package  as follows :-

                       

·        Use of  DBMS_SHARED_POOL package

·        How we can determine efficiently which objects is to pinned or not?

·        How to create a DBMS_SHARED_POOL package?

·        How do I pin or unpin a database object ?

·        When do we use DBMS_SHARED_POOL.SIZE procedure ?

 

 

DBMS_SHARED_POOL  package is used for

 

(1) Pin or  Unpin 

 

·     Cursor

·     Procedure , function or package

·     Trigger 

·     Sequence.

 

Pinned object do not need to be loaded and parsed from the database and hence increase performance. We should only choose those objects for pining, which are frequently accessed by the application.  By pining the objects we will   pre-allocate SGA for them to avoid fragmentation.

 

 How we can determine efficiently which objects is to pinned or not?

 

To determine which objects is to pin let us execute the following SQL when you think your database has reached in steady state.

 

 SQL > SELECT owner||'.'||name "Object Name",

              substr(type,1,12) Type,

              sharable_mem      Size,

              executions execs,

              loads loads,

              kept Kept  FROM v$db_object_cache

    WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')

     AND  executions > 0  ORDER BY executions desc,

     loads desc,  sharable_mem desc;

    

The above sql result will tell you which objects has loaded how many times, how many times it is executed and its size.

 

The suitable object for pining is having a high number of executions (EXECS) or very large (SPACE (K)).

 

You also have to set you shared pool size according to number and size of pinning objects.

 

How to create a DBMS_SHARED_POOL package?

 

To create a dbms_shared_pool package , you have to run  dbmspool.sql script found in $ORACLE_HOME/rdbms/admin directory. Also you have to execute $ORACLE_HOME/rdbms/admn/prvtpool.plb.

 

If you want to execute dbms_shared_pool package from another user, that user should have execute privilege on this package.

 

How do I pin or unpin a database object ?

 

To pin or unpin the following  procedure is used

 

PROCEDURE KEEP

 Argument Name                  Type                    In/Out  Default?

 ------------------------------  ----------------------- ------ --------

 NAME                            VARCHAR2                IN

 FLAG                            CHAR                    IN     DEFAULT

 

 

PROCEDURE UNKEEP

 Argument Name                  Type                    In/Out Default?

 ------------------------------  ----------------------- --------------

 NAME                            VARCHAR2                IN

 FLAG                             CHAR                   IN     DEFAULT

 

Name : The name of the object

Flag   :  This is an optional parameters. 

 

For Package , procedure or function
Flag is ‘P’ or ‘p’

For Trigger

Flag  is ‘R’ or ‘r’

For Sequence

Flag is ‘Q’ or ‘q’

For Cursor

Flag is ‘C’

 

For example

 

---  To pin  package ,procedure or function

 

1.    SQL> Execute dbms_shared_pool.keep ( ‘< PROCEDURE_NAME>’ , ‘P’);

 

It is necessary to call pl/sql object before pinning  because dbms_shared_pool only pins those object  which are available in SGA.

 

--- To pin a trigger

 

1.    SQL> Execute dbms_shared_pool.keep (‘< TRIGGER_NAME >’ ,’R’);

 

--- To pin a sequence

 

1.     SQL > execute dbms_shared_pool.keep (‘ <sequence_name>’,’q’);

 

----To pin a cursor

 

1)    You have to select a cursor to put in a SGA as

               SQL > select empno,ename from scott.emp where ename=’SAMEER WADHWA’;

 

2)    Find out the address and hash value of this cursor from $sqlarea as

 

           SQL > select address,hash_value from v$sqlarea where sql_text

                        like ‘select empno,ename from scott.emp where ename=%’;

 

3)  Execute DBMS_SHARED_POOL  with the address and hash value which you just found 

 

         SQL> execute dbms_shared_pool.keep (‘<ADDRESS>’,’HASH VALUE >, ‘C’);

 

When do we use DBMS_SHARED_POOL.SIZE procedure ?

 

This procedure is used to find all objects currently in the Oracle shared pool using a specified size.

The definition of this procedure is as follows.

 

PROCEDURE SIZES

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ -------->

 MINSIZE                        NUMBER                  IN

 

For example  you want to determine all the  objects having using memory more than 100 killobytes.

 

SQL > set serveroutput on size 100000

 

SQL > execute SYS.DBMS_SHARED_POOL.SIZES(100)

 

The result of the above sql will also tell you weather the particular object is pinned or not.

 

Conclusion : Dbms_shared_pool is a powerful package  in an Oracle rdbms

 

Reference:                                                   

Oracle PL/sql  Reference manual

Author:

Sameer wadhwa ,

Please send your comments at  Wadhwa_S@Hotmail.com

Copyright ©   Sameer Wadhwa   (All rights  reserved)