What is the pinning in Shared pool
Oracle 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
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
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','P');
Similarly statement to pin the sequence
exec dbms_shared_pool.keep('OK_JOB_NUMBER_S', 'Q');
A cursor can be also pinned using the address and the hash_value of the cursor which can be found in the V$SQL view. To identify the cursor a select can be used similar to:
select address,hash_value from v$sql where sql_text like '%&IDENTIFIABLE_SQL_TEXT%'; exec DBMS_SHARED_POOL.KEEP( 'ADDRESS,HASH_VALUE' , 'C' );
We can unkeep the cursor using
exec DBMS_SHARED_POOL.UNKEEP('ADDRESS,HASH_VALUE','C');
These statement need to be executed after every startup of database and on each instance.
I would recommend creating a database startup trigger to perform all these pinning
How to unpin the Objects in Shared pool
exec dbms_shared_pool.unkeep('<SEQUENCE_NAME>', 'q')
exec dbms_shared_pool.unkeep('<PACKAGE_NAME>', 'P')