Why pinning objects into the shared pool?

Last updated on November 14th, 2015 at 02:33 pm

Why pinning objects into the shared pool?

What¬† is the pinning in Shared poolOracle Applications requires space in the ORACLE System Global Area (SGA) for stored packages and functions. If SGA space is fragmented, there may not be enough space to load a package or function. You should pre-allocate space in the SGA shared pool for packages, functions, and sequences by “pinning” them.

Pinning objects in the shared pool can provide a tremendous increase in database performance, if it is done correctly. Since pinned objects reside in the SQL and PL/PLSQL memory areas, they do not need to be loaded and parsed from the database, which saves considerable time.

Also it may be good in case of sequence where you dont want the gap in the number.Pinning will make sure sequence are not aged out of shared pool.

How to choose the objects to be pinned

SELECT substr(owner,1,10)||’.’||substr(name,1,35) “Object Name”,
‘ Type: ‘||substr(type,1,12)||
‘ size: ‘||sharable_mem ||
‘ execs: ‘||executions||
‘ loads: ‘||loads||
‘ Kept: ‘||kept
FROM v$db_object_cache
AND executions > 0
ORDER BY executions desc,
loads desc,
sharable_mem desc;

Choose the objects with a high number of executions (EXECS) or very large (SPACE(K)), frequently used objects. If the decision is between two objects that have been executed the same number of times, then preference should be given to the larger object. From experience, very good results have been achieved with having pinned only about 10 packages

How to pin the objects in shared pool

Here is the sql statement to pin the package FND_PROFILE

execute dbms_shared_pool.keep(‘APPS.FND_PROFILE’);

Similary statement to pin the sequence

exec dbms_shared_pool.keep(‘OK_JOB_NUMBER_S’, ‘Q’);

These statement need to be executed after every startup of database and on each instance.
I would recommed creating a database startup trigger to perform all these  pinnings

Leave a Reply