When you are experiencing performace trouble with your EBS, a dba should investigate your environment.
Here is something you could do to speed up the EBS a little....
Oracle keeps SQL statements, packages, object information and many other items in an area in the SGA known as the shared pool
Oracle Applications requires space in the ORACLE System Global Area (SGA) for stored packages and functions
To improve the performance using the Oracle EBS, it’s useful to load some important (sys) packages into the shared pool, when starting up the database.
This is called ‘pinning’…
The advantage of pinning these packages is that they are already in memory and they do not have to be loaded from the database, which saves time.
Most time is saved when large, frequently used packages are pinned.
The following (sys) packages should always be pinned ..
To determine which Oracle EBS packages should be pinned, depends on what the users are doing. You need to find out which packages are being executed the most.
You can run the following script, as sys or apps, $AD_TOP/sql/ADXCKPIN.sql
This sql script will return an output file ADXCKPIN.lst, what will look like this…
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
Now you should look for those packages with a high number of executions (EXEC) or with a large space (SPACE K).
Don’t pin too many objects. According to Oracle, only 10 packages give a very good result.
If you are not sure whether or not you are already pinning packages, run the following query and find out.
SELECT owner, name, type, kept FROM v$db_object_cache WHERE kept='YES' AND TYPE like 'PACKAGE%';
How should you pin a package in the shared pool ?
You can use a standard Oracle sql script located in the $AD_TOP/sql…ADXGNPIN.sql. This sql script will generate a pinning script. You must edit this script before running it…otherwise all objects will be pinned !!
Another way to pin these packages is to adjust your database startup script and put the following lines into your script after you started the database.
Your script could look something like this..
conn / as sysdba