Creation Date: 16-AUG-2000 Applications Performance Troubleshooting ---------------------------------------- 1. Is there any pattern to the Performance issue? Many performance problems manifest patterns. These are usually time of day or situational. Document whether the problem occurs at a particular time each day or if it only occurs when a particular set of circumstances apply. Many performance problems occur around month-end or at the start of business. 2. Is there a resource issue that can be easily identified? Sometimes the cause of performance problems is a lack of resources. Check that there is enough Disk resources or Memory available at the time that the performance issue occurs. Sometimes even though there are what appears to be adequate resources available when the process starts the process may consume all available resources. 3. Has there been any changes to the environment? Often changes to an established environment can impact the performance of the environment. Check for any changes to the Network, introduction of new Forms or Reports or implementation of new procedures. 4. What is the Applications environment? The applications environment will make a difference in how you pursue a solution to a performance problem. Character Mode performance issue will reside on the same Host as the Applications APPL_TOP where NCA or 10SC issues can reside on any of the many Tiers. Reports 5. Has the report been updated recently? Many times, new reports or forms can result in performance issues. Check to see if there have been any patches applied or any forms or Reports moved into the environment. 6. Does the report exhibit the same behavior when run outside of Applications? Sometimes a problem with the Applications environment can be identified by removing the Applications environment as a wrapper. Oracle reports can be run outside of Applications using AR25RUN on UNIX or R25RUN32.EXE on Windows NT. The CONCSUB utility can also be used to run a report outside of Applications. 7. How many rows does the underlying table contain? Most reports are simply queries against data stored in Tables. The number of rows in the Tables can often be a factor in how fast a report can run. Usually when the number of rows grows into the hundreds of Thousands one can expect some performance degredation. Concurrent Managers 8. Are the Concurrent Managers up? Sometimes a preformance problem with the Concurrent Manager can be simply a downed Concurrent Manager. Issues with orphaned processes can sometime make it appear that the Concurrent Managers are stil up when infact there are down. Check both Applications and the Host environment in order to determine if the Concurrent Manager are indeed up. 9. How is the performance problem manifesting itself? Concurrent Manager performance issues can manifest themselves as a problem with Reports executing slowly. Run known short running reports to see if the problem is with the Concurent Managers or the report. A performance problem with the Concurrent Managers should be seen as slow processing of the jobs with jobs in a Hold status. 10. Has the customer tried shutting down and restarting the Concurrent Managers? If access to logfiles or Memory leaks are the cause of a performance issue, simply shutting down the Concurrent Manager can alleviate the immediate situation and also assist in pointing to a cause. 11. Have more processes been added to increase throughput? If the concurrent Manager does not have enough processes defined to handle the number of jobs that waiting to run there will always be jobs on hold. The number of processes for the Manager can be increased in the Workshift Screen. The recommended value for "Cache Size" is at least twice the number of processes defined for the Manager. "Cache Size" controls the number of pending requests that the Manager picks up when it awakens from its Sleep Cycle. 12. How many rows are there in the FND Tables? When the number of rows in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K there will be preformance issues. In order to alleviate the problems that arise from having to scan through a large number of entries in these tables, it is recommended that the "Purge Concurrent request and /or Manager Data" concurrent program be scheduled to run on a regular basis. Applications 13. How is the problem manifesting itself? Applications performance issues can show up as access to the forms being slow or in the return of information to the screen being slow. Another manifestation, is the appearance of Characters in Data entry long after they have been entered. 14. Are all clients experiencing the same performance? Depending on the Applications environment, the answer to this question will determine the next step. In character Mode environemnts, all processing is being done on one Host with the exception of installations where the Database is at a Remote location. In a 10SC environment the issue would most likely reside on the local Client except in a Shared install environment. In an NCA environment the Client is usually not the source of the performance issue unless it is severly undersized. Performance issues in a NCA environment usually would reside on the Forms Server tier. RELATED DOCUMENTS ----------------- Note 169935.1 Practical guide in Troubleshooting Oracle ERP Applications Performance Issues SCRIPTS ------- Note 156958.1 coe_xplain.sql - Enhanced Explain Plan for given SQL Statement (8.1-9.0) Note 156959.1 coe_xplain_80.sql - Enhanced Explain Plan for given SQL Statement (8.0) Note 156960.1 coe_xplain_73.sql - Enhanced Explain Plan for given SQL Statement (7.3) Note 174603.1 bde_x.sql - Simple Explain Plan for given SQL Statement (8.1-9.0) Note 156972.1 coe_view.sql - Clones views across instances for SQL tuning exercises (8.0-9.0) Note 156964.1 coe_import_stattab.sql - Imports CBO Stats from COE_STATTAB_XYZ into Dictionary Note 156967.1 coe_sqlarea.sql - Top 10 Expensive SQL from SQL Area (8.1-9.0) Note 163209.1 coe_sqlarea_80.sql - Top 10 Expensive SQL from SQL Area (8.0) Note 169630.1 bde_session.sql - Expensive SQL and resources utilization for given Session ID Note 174605.1 bde_chk_cbo.sql - Current, required and recommended Apps 11i init.ora params Note 163208.1 bde_last_analyzed.sql - Verifies Statistics for all installed Apps modules 11.5 Note 156968.1 coe_stats.sql - Automates CBO Stats Gathering using FND_STATS and Table sizes Note 157276.1 bde_chg_stats.sql - Changes CBO Stats Selectivity for given Index and Column Note 156969.1 coe_trace.sql - SQL Tracing Apps online transactions with Event 10046 (11.5) Note 156970.1 coe_trace_11.sql - SQL Tracing Apps online transactions with Event 10046 (11.0) Note 156971.1 coe_trace_all.sql - Turns SQL Trace ON for all open DB Sessions (8.0-9.0) Note 156966.1 coe_event_10046.sql - SQL Tracing online transactions using Event 10046 7.3-9.0 Note 156965.1 coe_locks.sql - Session and serial# for locked Rows (7.3-9.0) Note 174607.1 bde_gen_indexes.sql - Drop and Create non-partitioned B*Tree Indexes (8.1-9.0) Note 171647.1 - Tracing Oracle Applications using Event 10046