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.
