Pinning Oracle E-Business Suite Objects Into The Shared Pool (Doc ID 69925.1)
PURPOSE
Explain why, what and how to pin objects in the shared pool for Oracle Applications.
SCOPE
Oracle Applications DBAs who want to improve database performance by pinning packages into shared pool.
DETAILS
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 (for packages, functions)
$AD_TOP/sql/ADXGNPNS.sql (for 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.
PURPOSE
Explain why, what and how to pin objects in the shared pool for Oracle Applications.
SCOPE
Oracle Applications DBAs who want to improve database performance by pinning packages into shared pool.
DETAILS
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 (for packages, functions)
$AD_TOP/sql/ADXGNPNS.sql (for 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.
