Oracle EBS applications performance issue checklist
Check all the servers that are part of the applications are accessible.
-> login to the servers that confirms servers are up and accessible.
-> verify the load on the servers to see if any hung processes.
-> verify disk spaces.
-> verify if disks are accessible.
->Verify database health check.
-> check any blocking locks in the database.
-> check any tablespace running out of space.
-> check Gather schema statistics running periodically and verify if any scheduled job failed.
set linesize 200
col table_name for a30
select table_name, last_analyzed from dba_tables order by last_analyzed desc;
-> check the alert log for errors.
-> check temporary space, and undo retention.
-> verify if any recent changes to database configuration parameters.
-> verify if any recent changes to the database Oracle home like patching/upgrade ..
-> If any changes to Oracle home consider applying CPU/PS patches to Oracle home to make sure the latest bugs are addressed.
-> verify if any indexes status is invalid
select index_name , status, last_analyzed from dba_indexes where STATUS != 'VALID';
select index_name , status, last_analyzed from dba_indexes where STATUS = 'UNUSABLE';
-> verify if any recent jobs scheduled in the database like Mviews refresh
-> Verify SGA configuration is appropriate.
-> Check if sufficient database sessions, processes, and cursors are configured. It not it may throw an error into the alert log.
Find out what is slow?
entire system?
the subsystem is slow?
form-based applications
web-based applications
concurrent manager
the particular process is slow?
For particular processes like an invoice–> take a trace and tkprof
Verify 1
was re-org and gather done??
Verify 2
-->resource at desktop
-->check enough CPU/ram is there
-->ensure that no heavy applications are running when accessing applications.
-->required network
Verify 3
concurrent manager
-->tune concurrent manager(NOTE ID 1057802.1)
-->ensure enough CPU/RAM is available on the DB tier
-->check concurrent manager definition for Sleep time and Cache are set correct,,
the recommended value for sleep is between 20-30 and cache (If standard managers 10 then the value should be 10-15)
-->check if resource-intensive batch requests are scheduled during peak hours.
resolution:- Separate manager for long running process with less number of managers.
-->check FND_CONCURRENT_REQUESTS & PROCESSES are purged & defragmented regularly.
-->check if there are short-running requests waiting for a longer time to process
Resolution:- specialized managers
-->avoid enabling an excessive number of standard or specialized managers.
Note 1057802.1 Best Practices for Performance for Concurrent Managers.
Verify 4
=======
Forms Server
— Memory intensive. Ensure RAM & CPU
— check SAR & TOP
— check CATCHTERM, FORMS_RECORD_GROUP_MAX=10000
==>Debug/FRD enabled ??
==>Forms Patch set level with IO patches.
Verify 5
======
Applications:
How many concurrent users are there?
what are all the modules used?
any resource-intensive module is used
— when was gather schema statistics request run last–> this should be run regularly.(% should be 10-35)
— Ensure Diagnostics profiles are disabled as otherwise, this will consume lots of resources.
— Sign-on Audit is disabled if not needed.
— Check if Audit on other tables has been enabled.
Additional overhead
— Ensure Purging & defragment of interface/transaction tables is done periodically. (NOTE ID 752322.1)
Verify 6
Database
–check adequate resources i.e. CPU/RAM is available.
Monitor SAR/VMSTAT output
if I/O is a high need to check I/O setup & SQL queries
if CPU is high then check for CPU-intensive queries.
Monitor top OS process at OS level
init.ora setup (Note 216205.1) init.ora check
174605.1
bde_chk_cbo script file —> checking init.ora file contents
Verify 7
Web Tier
–check if JVM is hanging (due to Out of Memory issue)
–check Heap Size and how many JVM threads are defined
–Disable statement/debug level logs when not needed
–Review DBC file configuration
–Review OHS configuration
Verify 8
Network
–Check for sufficient Bandwidth (ping host -l 1024 -t)
–Network Test(DOC ID 556738.1)
**************************************
Additional Information:
**************************************
Enabling Trace
Four Possible levels
Level 1: Regular trace/SQL trace
Level 4: Trace with Binds
Level 8: Trace with Waits –> Preferred
Level 12:Trace with binds and Waits –> Very Expensive
Form Specific
open forms–>Help–>Diagnostics–>Trace–> ??(choose)
then perform the activity/transaction
disable the trace.
Concurrent Request taking a long time ??
Concurrent Report Tracing
Set Enable Trace in Concurrent Program Definition form.
program–>define window–>enable trace check box (Not recommended)
Enable trace for a specific request (SRS form–>Debug options)
— the profile “Concurrent: Allow Debugging” should be set to yes
then that option gets enabled else it stays greyed out, this is a recommended way as only this
the particular request will have trace enabled not all the programs being run by different
user in case of program-level trace enabling.
after you set this profile option, go to submit the concurrent request .. one new option
will appear Debug Option–>click–>Pop up will show–>choose which trace option you would prefer.
Trace enabling at the User Level
Initialization SQL Statement – Custom
Profile–>user name then –>Initialization SQL Statement – Custom set at the user level
tkprof tracefile.trc output.txt sort=fchela,exeela,prsela
in the output, you will find out the culprit dml statement
take the culprit sql statement in a text file
gather enhanced Explain Plan using SQLTXPLAIN.SQL Note 215187.1
note down all the tables used in that dml query
--> exec fnd_stats.gather_table_stats(‘APPLSYS’,’FND_CONCURRENT_REQUESTS’,PERCENT=>99);
*******************************************************
Comments
Post a Comment