Pinning Oracle Applications Objects into the shared pool
Oracle Applications DBAs who want to improve database performance by pinning packages into shared pool.
Why pinning objects into the 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.
What objects to be pinned into the shared pool?
Most performance improvement can be gained from pinning large, frequently used packages. Pinned objects are expensive in terms of memory space, since other not-pinned objects need this memory space, too. In general do not pin all objects or rarely used objects – this could even decrease database performance.
As a general rule, you should always pin the following packages which are owned by SYS:
(see Note 61623.1 SHARED POOL TUNING)
and maybe other SYS packages that are often used (DBMS_LOCK, DBMS_ALERT, etc.).
The Applications objects that should be pinned are harder to identify and will vary from site to site, depending on what the users are doing. To identify good candidates for pinning, you need to know which objects are being executed the most. To do this, let the system run long enough to reach a steady state (several days to a week). Then initiate a SQL*Plus session as system (or sys or apps) and run the following script $AD_TOP/sql/ADXCKPIN.sql. This will spool object execution and reload statistics into the output file ADXCKPIN.lst.
OBJECT TYPE SPACE(K) LOADS EXECS KEPT
———————————- ———— ——– —— ——- —-
APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE 15.2 1 9 NO
APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE BODY 13.7 1 8 NO
APPS.FND_CLIENT_INFO PACKAGE 2.7 1 206 NO
APPS.FND_CLIENT_INFO PACKAGE BODY 13.0 1 206 NO
APPS.FND_CONCURRENT PACKAGE 15.2 1 199 NO
APPS.FND_CONCURRENT PACKAGE BODY 24.2 1 197 NO
Another handy script:
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,
Note: The Oracle8 documentation says that the column EXECUTIONS is “Not used. To see actual execution counts, see “V$SQLAREA” “. But it is still used in Oracle8.
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 object into shared pool?
The pl/sql scripts $AD_TOP/sql/ADXGNPIN.sql (packages, functions) and ADXSPPNS.sql (sequences) generate pinning scripts, which can be executed in Sql*Plus. Do not run them without having edited them, otherwise the scripts would try to pin all objects. Create your own script to pin the packages and pin them in a descending order according to their size.
The pl/sql command to pin a package (i.e. FNDCP_TMSRV) manually is:
SQL> execute dbms_shared_pool.keep(‘APPS.FNDCP_TMSRV’);
Note: The objects have to be pinned after each instance startup, and ideally immediately after the startup. Prior to Oracle 7.2 DBMS_SHARED_POOL.KEEP does not actually load all of the object to be KEPT into the shared pool. It is advisable to create a dummy procedure to execute each package to be KEPT. This dummy procedure can then be called after calling DBMS_SHARED_POOL.KEEP to ensure that the object is fully loaded. This is not a problem from 7.2 onwards.
Note 62161.1 Systemwide Tuning using UTLESTAT Reports in Oracle7/8
Note 68307.1 STEPS TO RESOLVE APPLICATIONS LATCHING ISSUES