Last updated on November 14th, 2015 at 02:33 pm
Why pinning objects into the shared pool?
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
WHERE type in (‘TRIGGER’,’PROCEDURE’,’PACKAGE BODY’,’PACKAGE’)
AND executions > 0
ORDER BY executions 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
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