Normally I would not interview end users as they expect reports and processes to complete instantaneously (i.e. have unrealistic expectations about performance).
Things are look for in a health check are as follows:
- Database errors in the alert log (grep for ORA-)
- Excessive redo log group switching in the alert log (every minute of less, ideally would be about 20 minutes)
- Database is on a current/supported version on a recent patch set update (PSU), (observe performance improvements on higher PSU versions on the same release)
- Database init parameters are set correctly for EBS - bde_chk_cbo.sql - EBS initialization parameters - Healthcheck (Doc ID 174605.1)
- Requests are completing in reasonable times (no glaring problems in Statspack or AWR reports)
- Filesystems have sufficient free space for datafiles and log files
- There are cleanup scripts for database trace and audit log files after a certain number of days
- Concurrent requests are not ending in error or warning
- There are not too many rows in FND_CONCURRENT_REQUESTS - Health Check Alert: Purge the eligible records from the FND_CONCURRENT_REQUESTS table (Doc ID 1095625.1)
- Concurrent managers are properly tuned, not overwhelming the database server at certain times
- Users are not submitting Workflow Background Process for Deferred, Timeout and Stuck all Yes to run every minute
- Database server is not swapping heavily during operation
- Operating system of database and application servers have been patched and restarted after patching
Thank you so much Michael, and ALL
Please share script on how to check the following:
1. How to check if Concurrent managers are overwhelming the database server.
2. How to check if Workflow Background Process for Deferred, Timeout and Stuck all Yes to run every minute
3 Assuming I have AWR report, which part here shows > Requests are completing in reasonable times ?
On the database server, you can type sar -q and look for periods with high load average (like 2X higher than number of CPUs), then use your SQL for completed concurrent requests to see what was running during that time. You can run sar for a certain date with sar -q -f /var/log/sa/sa30 to show the data from the 30th.
Query Workflow Background Process in View Concurrent Requests and see what the parameters are (,,,Y,Y,Y) means all item types for deferred, timeout and stuck, then hit the schedule button and see how often it repeats.
It is hard to tie AWR to concurrent requests, but drill into SQL Statistics and view by elapsed time and CPU time to see what SQLs are taking the most time and usually there are enough clues about what the SQL is to tell what process might be generating it.