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

*******************************************************