Oracle: Pinning Frequently Used Packages

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

Pinning Frequently Used Packages

The reason why we would consider pinning packages in memory is because if they are used frequently and aged out of memory, with the Least Recently Used (LRU) algorithm; then they need to be loaded into memory again.  Associated code, may also need to be reparsed.  So to improve performance, we can pin these frequently used packages into memory.

Firstly you will need to verify that the package sys.dbms_shared_pool exists.  From a SQ*Plus prompt:

SQL> select owner, object_type, object_name

          2 from dba_objects

          3 where object_name = ‘DBMS_SHARED_POOL’;

You should get something like the below.

SYS                            PACKAGE             DBMS_SHARED_POOL                                                                                                               

SYS                            PACKAGE BODY        DBMS_SHARED_POOL                                                                                                               

If the package does not exist, then you need to create it:

Go to $ORACLE_HOME/rdbms/admin, or %ORACLE_HOME%/rdbms/admin

Connect to SQL*Plus as sysdba and run the following

SQL> @dbmspool.sql

           @prvtpool.plb

This package has four procedures in it.  We only need one of them for our purposes:

PROCEDURE KEEP

PROCEDURE PURGE

PROCEDURE SIZES

PROCEDURE UNKEEP

There are now three steps to follow to achieve the goal.

Step 1.  Choose the packages to keep in Memory.

From a SQL*Plus prompt:

SQL> SELECT Owner, substr(name,1,20) "Name", type, kept, loads,executions

    FROM v$db_object_cache

    where type like 'PACKAGE%'

    and executions > 100000

    order by executions desc;

APPS                                                             FND_CACHE_VERSIONS_P                                                             PACKAGE BODY                 NO  5                                      2662144                              

SYS                                                              DBMS_LOB                                                                         PACKAGE BODY                 NO  2                                      1274938                              

SYS                                                              DBMS_UTILITY                                                                     PACKAGE BODY                 NO  1                                      1235305                              

SYS                                                              DBMS_ASSERT                                                                      PACKAGE BODY                 NO  1                                      1111957                              

APPS                                                             FND_PROFILE                                                                      PACKAGE BODY                 NO  4                                      746524                               

SYS                                                              STANDARD                                                                         PACKAGE BODY                 NO  1                                      261366                               

APPS                                                             FND_GLOBAL                                                                       PACKAGE BODY                 NO  4                                      254258                               

SYS                                                              DBMS_SPACE_ADMIN                                                                 PACKAGE BODY                 NO  1                                      223115                               

SYS                                                              DBMS_AQ                                                                          PACKAGE BODY                 NO  1                                      188273                                

SYS                                                              DBMS_METADATA_UTIL                                                               PACKAGE                      NO  5                                      116324                                

SYS                                                              DBMS_SESSION                                                                     PACKAGE BODY                 NO  5                                      101181                                

You may have to play around with the number of executions, until you have generated more or less the top 10 performers.  You will choose the packages where kept is ‘NO’, and with the highest executions to pin into the SGA memory.  Some of these packages may in fact be sys owned packages.

Step 2. Re-start the database, this is optional.  However it is good practice so that we can make sure that the system does not run out of memory, when we try to load the pinned objects in memory.

Step 3.  Call the procedure to pin the packages in memory:

SQL> execute dbms_shared_pool.keep(‘sys.dbms_lob’,’P’);

The parameter ‘P’ indicates package/procedure/ function

‘T’ indicates Type

‘R’ indicates trigger

‘Q’ indicates sequence

Now you can verify what is in memory by:

SQL> SELECT Owner, substr(name,1,20) "Name", type, kept, loads,executions

    FROM v$db_object_cache

    where type like 'PACKAGE%'

    and executions > 100000

    order by executions desc;

However you may want the packages to remain pinned in memory even after re-starting the database.  So you will create a trigger, for in this case the most frequently used packages, or packages with the most executions.  In a SQL*Plus prompt

create or replace trigger pin_packages

After startup on database

BEGIN

   dbms_shared_pool.keep(‘apps. FND_CACHE_VERSIONS_P’,’P’);

   dbms_shared_pool.keep(‘sys.DBMS_LOB’,’P’);

   dbms_shared_pool.keep(‘sys. DBMS_UTILITY’,’P’);

   dbms_shared_pool.keep(‘sys. DBMS_ASSERT’,’P’);

   dbms_shared_pool.keep(‘apps. FND_PROFILE’,’P’);

   dbms_shared_pool.keep(‘sys. STANDARD’,’P’);

   dbms_shared_pool.keep(‘apps. FND_GLOBAL’,’P’);

   dbms_shared_pool.keep(‘sys. DBMS_AQ’,’P’);

   dbms_shared_pool.keep(‘sys. DBMS_SESSION’,’P’);

END;

/

Now everytime you startup the database, these packages will be pinned into memory.  You can also use the DBMS_SHARED_POOL.unkeep() to unpin them again.

Franz Devantier,

Need a database health check?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.