22.2 Identifying Performance Issues

Consider the following key strategies to help identify and resolve performance issues with your Oracle APEX application.

• About Troubleshooting Slow-Running Applications
  
• Diagnosing Performance Issues
  
• About Debugging Problematic SQL Queries
  
• About Addressing Slow Queries
  

22.2.1 About Troubleshooting Slow-Running Applications

The first step in troubleshooting a slow-running application is to determine the location of the bottleneck. You can evaluate application performance by reviewing the time it takes for the database to return a specific page request or submission. If this time is shorter than the time it takes to render the page on the user's browser, then other components are likely causing the perception that the application is slow. As a best practice, always check all of the components involved including client machines, the network, the middle-tier, the database, and disk and storage devices. Regarding the database, check the System Global Area (SGA), CPU utilization, locks, and database parameters. It is also important to determine if anything else is running within the database such as legacy applications, data warehouse, batch programs, and database jobs.

The majority of the time, performance issues with APEX applications are due to developer-authored SQL and PL/SQL. You should utilize standard database performance tuning techniques to address these issues. Performance issues are rarely related to a bug with APEX or the Oracle database.

See Also:

Oracle Database Performance Tuning Guide

22.2.2 Diagnosing Performance Issues

To diagnose performance issues, follow these steps:

1. Identify the time window.
2. Isolate the largest consumers of database time.
3. Identify the APEX workspace and application.
4. Correlate the offending SQL with a specific location within the application.

Once you have identified the slow running pages, you should run the application in debug mode to identify the specific components consuming the most time.

22.2.3 About Debugging Problematic SQL Queries

If your query does not seem to be running correctly, try running it in SQL*Plus, SQL Developer, or in SQL Commands. Any of these approaches will test your query outside the context of your application, making it easier to define the problem.

22.2.4 About Addressing Slow Queries

For optimized queries that still take more than a second to process, consider implementing a progress bar and preventing multiple page submissions. Users expect instant results and often resubmit a page if it does not return immediately. Unless managed properly, each page submission instigates a new session in the database and continues until completion. Multiple page submissions often degrade performance and may lead to database locks. This is especially true with interactive reports. As an alternative approach, try using APEX collections to hold query results. By using collections, expensive and time consuming queries are only executed once for each user rather than each time they perform pagination or create a filter.

Tip:

You control whether end users may submit the same page more than once by configuring the page attribute, Advanced, Enable duplicate page submissions.

See Also:

• Editing Page Attributes
• About Using Collections