Pinning Oracle DB/EBS Objects for better Performance

Imagine a large package (or any object) has to be loaded into the shared pool.

Large PL/SQL objects present particular challenges. 

The database has to search for free space for the object. 

If it cannot get enough contiguous space, it will free many small objects to satisfy the request.

If several large objects need to be loaded, the database has to throw out many small objects in the shared pool. 

Finding candidate objects and freeing memory is very costly.  These tasks will impact CPU resources.

To view a list of all objects that are kept in the shared pool, one can query the v$db_object_cache:

Select owner, name, type, sharable_mem from v$db_object_cache where kept='YES';

In EBS application since 80-90% objects are owned by APPS

Select owner, name, type, sharable_mem from v$db_object_cache where kept='YES' and owner='APPS';

The SQL query above will list all of the objects that are 'kept' in the shared pool using dbms_shared_pool.keep.

One approach to avoiding performance overhead and memory allocation errors is to keep large PL/SQL objects in the shared pool at startup time. 

This process is known as pinning.

This loads the objects into the shared pool and ensures that the objects are never aged out of the shared pool.

If the objects are never aged out, then that avoids problems with insufficient memory when trying to reload them.

Following Package is used to PIN Objects

execute dbms_shared_pool.keep('owner. object');

(I Have generated dynamic sql for the list of objects to be pinned in PROD)

However there is a word of caution on this :

1.Be careful not to pin too many objects into memory and use up all of your available shared buffer space or the performance of the remainder of the applications will suffer.(we could consider filtering the list a bit)

2. The database startup routine should execute the following script after opening the database:

i.e.

execute dbms_shared_pool.keep (APPS.GL_CURRENCY_API) ;

execute dbms_shared_pool.keep (APPS.HR_SECURITY) ;

3. Oracle Corporation recommends that the following packages always be pinned into the shared pool at database startup (if you have installed them into the database):

SYS.STANDARD ,SYS.DBMS_LOCK

SYS.DBMS_STANDARD  ,SYS.DBMS_PIPE

SYS.DBMS_DESCRIBE ,SYS.DBMS_OUTPUT

SYS.DBMS_UTILITY

Pinning objects into memory yields best results for OLTP users. Because long-running jobs will load these packages into the SGA at the beginning of their job and hold them there for the duration of the task, performance improvements will be minimal.

(Prior to pinning the object the object should have executed at least once to make sure it exists in the shared pool)

The $AD_TOP/sql/ADXGNPIN.sql script is provided to pin the packages in Oracle Applications.  

This script pins all Oracle Applications objects, which is generally not completely necessary or advisable.

You should modify this script based on your analyses of v$db_object_cache and x$ksmlru to identify the right objects to be pinned for your environment.

It doesn’t matter whether it’s EBS or non-EBS because all loaded/executed objects are found in the view “v$db_object_cache” irrelevant of whether it’s an EBS/NON-EBS.

How these objects are loaded as in how many times executed/Loaded & memory used varies from environment to environment and also application to application.

So it finally comes down to one thing list out the objects based on no, of execution, Loads, memory.

Below query gives this extract of the objects to be pinned based on the no of executions, loads, etc.

Select 'execute dbms_shared_pool.keep (' ||''''|| substr (owner,1,10)||'.'||substr(name,1,35)  ||''''||',' ||''''|| decode(type,'PACKAGE','P','PROCEDURE','P','FUNCTION','P','Q','TRIGGER','R') ||'''' ||');'    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

Also in my experience it is good to avoid pinning triggers because triggers can behave very weirdly and can be a show stopper for the application functionality, so avoided pinning triggers.