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)

STANDARD

DBMS_STANDARD

DBMS_UTILITY

DBMS_DESCRIBE

DBMS_OUTPUT

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.

Example output:

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

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 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');