Sizing Up PerformanceBy Mike Hichwa
Tips and techniques for optimal Oracle Application Express performance
As Oracle Application Express becomes more popular, many users are asking for guidance on sizing and performance tuning. In this column, I'll show you a quick and convenient way to estimate performance and sizing. I'll also demonstrate how to identify and deal with performance issues. Here are some of the more common questions this column will answer:
Let's start with some background.
Understanding Oracle Application Express Application Performance
The key to optimal performance for most Oracle Application Express applications is to keep the average page-view times relatively short. Scaling is linear: For example, an application with average page-view times of 10 milliseconds (ms) will be able to handle about 10 times as many concurrent users as an application with average page-view times of 100 ms.
You can use the performance statistics collected by Oracle Application Express to easily approximate how well an Oracle Application Express application will scale. The performance statistics are available on the Monitor Activity page. Assuming that the application is well tuned, with efficient SQL and PL/SQL, the single most important sizing factor is the CPU.
For example, suppose you are developing an Oracle Application Express application that is required to support 1,000 page views per minute. On a dual-CPU system, the application would need to achieve 500 page views per minute, per CPU, or 8.33 page views per CPU per second. Meeting this requirement dictates that the average page-view time in the application must not exceed 120 ms.
The relationship between the available CPUs and the required page views per minute results in the average response time per page, which can be expressed in the following equation:
where N is the number of CPUs, P is the number of page views per minute, and A is the average response time per page.
Using this simple equation, you can approximate the required average page-view time to support a targeted number of page views per minute. By altering the number of CPUs or page views per minute, you can establish clear performance targets for your application.
An application's average page-view time can also help you predict the impact a change in the size of your user community will have as that community grows. To determine how many users the application can support, start by first determining the number of page requests during a given time period.
For example, if an average session comprising 50 page views takes 10 minutes, then the application supports 5 page views per minute for a typical session. If you are sizing the application to allow for 1,000 page views per minute, the application will support 200 concurrent users per session.
Extrapolating this data to approximate the daily user community, let's further assume that all users are in the same time zone and that, on average, each user performs two sessions per eight-hour day, resulting in 100 page views (2 * 50 page views per session). Because you are sizing the application for 1,000 page views per minute, multiply the number of minutes in eight hours (480) by the page views per minute (1,000) to determine that the application can support 480,000 page views per eight hours, or 4,800 users.
As a rule of thumb, you should size for your busiest hour, because an application may serve only 100 page views per minute in the course of a day but may serve 1,000 page views per minute during the peak hour.
Another rule of thumb is that you should ensure an average page-view time of 300 milliseconds or less. For high-volume applications, with many hundreds or thousands of concurrent users, the goal should be an average page-view time of 150 milliseconds or less.
Finding and Eliminating the Bottlenecks
The overhead associated with the generation of a page view in Oracle Application Express is fairly static. Any other processing of developer-created SQL and PL/SQL in an Oracle Application Express application is in addition to this static overhead.
Then, using the Monitor Activity feature of Oracle Application Express, you can readily identify the poorest-performing pages in the application (see Figure 1).
Once you have identified the poorest-performing pages, you can examine them in Debug mode, by running the application and clicking the Debug link in the Developer Toolbar. Debug mode renders the page concurrently with timing information—you'll see time codes that correspond to specific Oracle Application Express actions as well as additional details about item names, computation, and processing points.
By identifying steep jumps in the elapsed time in the debug output, you will find the poorest-performing page elements. For example, here is a snippet from a page run in debug mode:
. . . 0.05: Region: Projects 0.06: Show report 0.06: Determine column headings 0.06: Activate sort 0.07: Parse query as: MIKE1 0.07: Binding: ":P24_SEARCH"="P24_SEARCH" value="" 0.07: Print column headings 0.07: Rows loop: 15 row(s) 0.18: Region: Icon View . . .
The most common area of tuning focus is SQL. The Oracle Application Express engine parses, binds, executes the statement, and fetches the results of the SQL for a reporting region. Use bind variables whenever possible to avoid unnecessary parsing and promote reuse of shared SQL by Oracle Database.
Additionally, ensure that an optimal query plan is being used for the query. Use Explain Plan from the SQL Commands menu to easily generate plans for a particular query.
For a thorough examination of every element of SQL and PL/SQL on a particular page, you can enable SQL tracing for the entire page view. SQL tracing generates a trace file on the server that you can analyze with the Oracle utility TKPROF. (See "Debugging an Application" in the Oracle Application Express User's Guide for more information about using TKPROF in Oracle Application Express.)
Tuning Page Elements
Page processes, computations, authorizations, validations, and conditions are other common tuning opportunities in Oracle Application Express applications. The performance of these elements on the page should be readily apparent when you run your application in Debug mode. Here are some guidelines for these page elements:
Set page processes to run on a per-page (rather than per-session) basis. If you are employing application-level processes, you can set the process to run once per session or per page view. Keep in mind that using the per-session option for an application process can affect all the other page views in the application. A poor-performing application-level process can affect every page view in an application.
Optimize page zero components. Page zero components are rendered on every page, so take special care to optimize all page zero logic. Consolidate numerous PL/SQL blocks into packages. If you are writing large PL/SQL blocks in a page, consolidate them into PL/SQL packages, which are then invoked from your application.
Use declarative conditions. Declarative conditions are faster than dynamic SQL and PL/SQL conditions; for example, using an "Item=Value" condition is faster than using the PL/SQL expression ":ITEM=value."
Use "Rows X to Y" pagination for reports that return numerous rows. "Rows X to Y of Z" takes longer to compute than the simple "Rows X to Y" pagination scheme. With "Rows X to Y of Z," if your report returns 900 rows, Oracle Application Express will need to fetch all rows to obtain the total row count; with "Rows X to Y," on the other hand, the reporting engine needs to fetch only Y + 1 rows.
Using the sizing guidelines presented in this column will help you estimate the performance and scalability of Oracle Application Express-based applications. If you're not happy with an application's performance, use these pointers to locate the slowest pages and then identify the poorly performing components within those pages. With this information, you can ensure that your Oracle Application Express applications meet performance expectations.
Mike Hichwa (firstname.lastname@example.org) is vice president of software development at Oracle and manages Oracle Application Express, Oracle SQL Developer, and other database development tools.