Oracle DBMS_SHARED_POOL Package

Power of DBMS_SHARED_POOL Package

• 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