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 ..
STANDARD
DBMS_STANDARD
DBMS_UTILITY
DBMS_DESCRIBE
DBMS_OUTPUT
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..
sqlplus /nolog
conn / as sysdba
begin
dbms_shared_pool.keep('SYS.STANDARD');
dbms_shared_pool.keep('SYS.DBMS_STANDARD');
dbms_shared_pool.keep('SYS.DBMS_DESCRIBE');
dbms_shared_pool.keep('APPS.ICX_UTIL');
dbms_shared_pool.keep('APPS.OWA_CUSTOM');
dbms_shared_pool.keep('FA.FA_ADDITIONS_S','Q');
end;
1 comment:
One could also create a startup database trigger to pin the objects you want in memory. The following is an example of portal objects. Extra attention for the use of the undocumented items of DBMS_SYSTEM (I guess officially not supported, but very useful)
CREATE or REPLACE trigger ILX$STARTUP_DB
after startup on database
-- ----------------------------------------------------------------------------
-- Created by: M.Gralike, 2003-10-10, The Netherlands
-- Purpose : Database startup trigger to pin SYS packages in memory
--
-- When created outside the SYS environment the following grants
-- are needed and dbms_shared_pool must be installed (dbmspool.sql)
-- Also grant explicit rights were needed to execute the PLSQL
-- objects. Needed for this trigger are:
--
-- > grant execute on sys.dbms_shared_pool to [schema];
-- > grant execute on sys.dbms_system to [schema];
-- > grant select on sys.v_$db_object_cache to [schema];
--
-- Also specify extra (application) objects, dynamically or
-- hardcoded, when needed, in your database
--
-- Tested in Oracle database version 8.1.7.X.X and up
--
-- ----------------------------------------------------------------------------
-- Created by: M.Gralike, 2003-10-10, The Netherlands
-- Purpose : Database startup trigger to pin SYS packages in memory
-- ----------------------------------------------------------------------------
-- Changed :
-- Altered by:
-- Change :
-- ----------------------------------------------------------------------------
declare
v_errornumber number;
v_errortext varchar2(200);
CURSOR get_pinned IS
SELECT owner
, name
, type
, sharable_mem
FROM v$db_object_cache
WHERE kept = 'YES'
order by owner, type, name
;
BEGIN
/*Undocumented feature of dbms_system: writing to oracle log files.
dbms_system.KSDWRT(number,[message]):
1 -> trace file
2 -> alert logfile
3 -> both.
*/
sys.dbms_system.KSDWRT( 2, 'Start loading packages via ILX$STARTUP_DB' );
sys.dbms_system.KSDWRT( 2, 'Started at '||to_char(sysdate,'Dy Mon DD HH24:MI:SS RRRR'));
-- # Pinning standard database objects in cache
sys.dbms_shared_pool.keep('SYS.DBMS_APPLICATION_INFO','P');
sys.dbms_shared_pool.keep('SYS.DBMS_ALERT','P');
sys.dbms_shared_pool.keep('SYS.DBMS_DDL','P');
sys.dbms_shared_pool.keep('SYS.DBMS_DESCRIBE','P');
sys.dbms_shared_pool.keep('SYS.DBMS_LOCK','P');
sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT','P');
sys.dbms_shared_pool.keep('SYS.DBMS_SESSION','P');
sys.dbms_shared_pool.keep('SYS.DBMS_SHARED_POOL','P');
sys.dbms_shared_pool.keep('SYS.DBMS_SQL','P');
sys.dbms_shared_pool.keep('SYS.DBMS_SYSTEM','P');
sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD','P');
sys.dbms_shared_pool.keep('SYS.DBMS_UTILITY','P');
sys.dbms_shared_pool.keep('SYS.STANDARD','P');
-- # Pinning standard web objects in cache
sys.dbms_shared_pool.keep('SYS.OWA','P');
sys.dbms_shared_pool.keep('SYS.HTP','P');
sys.dbms_shared_pool.keep('SYS.OWA_VPD_CTX','P');
sys.dbms_shared_pool.keep('SYS.PLITBLM','P');
sys.dbms_shared_pool.keep('SYS.UTL_RAW','P');
sys.dbms_shared_pool.keep('SYS.DBMS_OBFUSCATION_TOOLKIT','P');
sys.dbms_shared_pool.keep('SYS.OWA_UTIL','P');
sys.dbms_shared_pool.keep('SYS.DBMS_OBFUSCATION_TOOLKIT_FFI','P');
sys.dbms_shared_pool.keep('SYS.WPG_DOCLOAD','P');
sys.dbms_shared_pool.keep('SYS.OWA_COOKIE','P');
sys.dbms_shared_pool.keep('SYS.WPG_DOCLOAD','P');
sys.dbms_shared_pool.keep('SYS.OWA_CACHE','P');
-- # Objects with heavy executions per day
sys.dbms_shared_pool.keep('PORTAL30.WEBDB_VPD_SEC','P');
sys.dbms_shared_pool.keep('PORTAL30.WPG_SESSION','P');
sys.dbms_shared_pool.keep('PORTAL30.WWCTX_API','P');
sys.dbms_shared_pool.keep('PORTAL30.WWSEC_API','P');
sys.dbms_shared_pool.keep('PORTAL30.WWCTX_API_VPD','P');
sys.dbms_shared_pool.keep('PORTAL30.WEBDB_VPD_SEC','P');
sys.dbms_shared_pool.keep('PORTAL30.WWNLS_API','P');
sys.dbms_shared_pool.keep('PORTAL30.WWCTX_SSO','P');
sys.dbms_shared_pool.keep('PORTAL30.WWSEC_OBFUSCATION','P');
-- # Objects with heavy loads per day
-- > Not applicable
sys.dbms_system.KSDWRT( 2, 'End loading packages via ILX$STARTUP_DB' );
sys.dbms_system.KSDWRT( 2, 'Finished at '||to_char(sysdate,'Dy Mon DD HH24:MI:SS RRRR'));
EXCEPTION when others THEN
v_errornumber:= sqlcode;
v_errortext := substr(sqlerrm,1,200);
-- When error then spool messages to a user trace file
-- Show objects that are pinned in cache
sys.dbms_system.KSDWRT( 1, '----------------------------------------------------------------------');
sys.dbms_system.KSDWRT( 1, 'Start error summery:');
sys.dbms_system.KSDWRT( 1, '----------------------------------------------------------------------');
sys.dbms_system.KSDWRT( 3, 'ILX-20000 Unhandeled Oracle Error in ILX$STARTUP_DB trigger: '||v_errornumber);
sys.dbms_system.KSDWRT( 3, 'ILX-20000 Oracle Error Text: '||v_errortext);
sys.dbms_system.KSDWRT( 3, 'Time: '||to_char(sysdate,'Dy Mon DD HH24:MI:SS RRRR'));
sys.dbms_system.KSDWRT( 1, '----------------------------------------------------------------------');
sys.dbms_system.KSDWRT( 1, 'The following objects are pinned in memory:');
sys.dbms_system.KSDWRT( 1, '----------------------------------------------------------------------');
FOR rec IN get_pinned LOOP
sys.dbms_system.KSDWRT (1,
'- '
||rec.type
||' '
||rec.owner
||'.'
||rec.name
||'(Size '
||rec.sharable_mem ||' bytes)'
);
END LOOP;
sys.dbms_system.KSDWRT( 1, '----------------------------------------------------------------------');
END ilx$startup_db;
/
show errors
prompt
Post a Comment