There are a number of factors that should be reviewed when facing performance issues with Oracle Application Express applications.
The first step in reviewing slow running applications is to determine where the bottleneck is located. The performance of an Oracle Application Express application can be determined by reviewing the time it takes for the database to return a specific page request or submission. If this time is short compared to how long it takes to render the page on the user's browser then other components are likely causing the perception that the application itself is slow. You should check all of the components involved including the client machine(s), network, middle-tier, database, and disk/storage devices. With respect to the database check the SGA, CPU utilization, locks and database parameters. It is also important to determine what else is running within the database such as legacy applications, data warehouse, batch programs, and database jobs.
The majority of the time, performance issues within Application Express applications are due to developer-authored SQL and PL/SQL. You should utilize standard database performance tuning techniques to address such issues. Very seldom is the issue related to a bug with Application Express or the Oracle Database. The steps to diagnosing performance issues are to identify the time window, isolate the largest consumers of database time, identify the Application Express workspace and application, and then correlate the offending SQL with a specific location within the application. Once you have identified the slow running page(s), you should run the application in debug mode to identify the specific components consuming the most time.
For queries that have been optimized but still take more than a second processing, you should consider implementing a "progress bar" and preventing multiple page submissions. Users expect instant results and will often resubmit a page if it does not return immediately. Each page submission will instigate a new session in the database and continue until completion unless managed properly. This will often degrade performance and may lead to database locks. This is especially true with interactive reports. An alternative may be to consider using Application Express collections to hold query results. Using collections the expensive, time consuming, query is only executed once for each user rather than each time they perform pagination, filters, etc.
Activity logs are recorded by the Application Express engine to allow administrators to review usage and performance using various reports. If a particular page is accessed extensively or a page renders slowly then these reports will often help identify the pages. Such pages are candidates for further analysis, such as running the report query in SQL Developer so that you can review the Explain Plan. Another method of determining expensive elements within a page or process is to review the Application Express Debug mode. The activity logs switch by default every 14 days, but this setting can be modified by Instance administrators. If you wish to keep the log files indefinitely it will be necessary to create a batch job to copy records from the activity log table to your own table.
There are a number of activity reports available to Workspace administrators including:
Page Views - Contains reports of page views organized by view, user, application, or application and page.
Developer Activity - Offers reports of developer activity organized by developer, day, application, application changes, and day or month.
Page View Analysis - Contains reports analyzing page views, such as top page views by application.
Sessions - Lists active sessions with the current workspace.
Login Attempts - Offers reports listing login attempts.
Environment - Contains reports of environments organized by user agent, browser, external clicks, or operating system.
Application Errors - Contains a report of application errors.
Workspace Schema Reports - Offers summaries of schema tablespace utilization and database privileges by schema and a list of all database schemas available in the current workspace.
For Instance administrators the following reports are available:
Page Views - View activity by application, user, workspace, day, or REST access.
Workspace Purge - View a dashboard summary, inactive workspaces, workspaces purged, workspaces that became active, or a workspace purge log.
Environment Reports - View a summary of used operating systems, browser types, user agent, or external sites.
Calendar Reports - View workspaces by date last used, page views by day and then by application and user, or by hour.
Service Requests - View new service requests or signup survey activity.
Logs - View the mail log, jobs log, automatic delete log, or monitor packaged application installations.
Login Attempts - View login attempts or developer last login.
Developer Activity - View application changes by developer or workspace.
Application Express establishes a physical connection from the database pool as APEX_PUBLIC USER, which calls the Application Express engine. The Application Express engine utilizes SQL.DBMS_SYS_SQL to parse SQL as another user, the parsing schema. Database V$SESSION records contain useful information for each SQL executed including Application Express specific information for the client information, client identifier, and module. Such information allows you to identify the associated SQL when you generate Oracle Trace files and use TKPROF to analyze them. You can add &p_trace=YES to the end of the Application Express URL or use ALTER SESSION.
Automatic Workload Repository (AWR) reports collects performance statistics every hour, by default. Active Session History (ASH) reports are a system-wide record of database activity. Such reports can be utilized to identify resource intensive SQL statements. For PL/SQL packages, procedures and functions that in turn call other PL/SQL programs you can use PL/SQL Hierarchical Profiler in 11g (PL/SQL Profiler in 10g) which will account for execution times separately. There are also a number of Application Express views, which are a database catalog of everything related to Application Express. These views will enable you to access information from Application Express using tools such as SQL Developer, SQL*Plus or command line. If you are granted the APEX_ADMINISTRATOR_ROLE then you will be able to query across the entire instance, rather than just the workspace your schema user is associated with.
There are certain database parameters that can also impact performance. In particular you should review the SHARED_POOL_SIZE and JOB_QUEUE_PROCESSES parameters to ensure they are sized correctly. Another concern is to ensure the database System Global Area (SGA) fits in real memory. An undersized SGA can dramatically hamper Application Express performance. Their needs to be sufficient memory in the Shared Pool for PL/SQL and in Buffer Cache for the Application Express metadata. If the SGA is sized correctly it should not be necessary to pin PL/SQL programs from the Application Express engine as they are accessed regularly, and should not be swapped out of memory. Given that Application Express engine resides in the database it is strongly recommended that you gather statistics on the APEX_040200 schema. Accurate statistics will allow the cost-based optimizer to develop better query plans for accessing the application metadata.
Database Resource Manager, an Oracle Database Enterprise Edition option, is exceedingly useful for maintaining an Application Express instance. Using resource manager you can limit the types of system resources (CPU_PER_CALL and LOGICAL_READS_PER_CALL) available to each user request. By setting up multiple resource profiles, with automatic switches between the profiles, as a user request exceeds a certain threshold you can reduce the CPU available to that transaction. For example you may define a default profile APEX_HIGH which can utilize 70% of CPU for 10 seconds, APEX_MEDIUM with 8% CPU for 120 seconds, APEX_LOW with 2% CPU for 1800 seconds, and OTHERS with 20% CPU. As a specific transaction exceeds 10 seconds it is switched to the APEX_MEDIUM profile which drastically reduces the CPU available. If the transaction switches to APEX_LOW and exceeds 1800 seconds then you can cancel the SQL or kill the session. This capability will ensure that one specific user or poorly written application cannot monopolize the CPU causing other users and applications to respond poorly.
To improve performance you should place static files on the Web server. You should also enable gzip compression on the Web server so that the file size is minimized when transmitting the static files to the browser. Furthermore, enable file caching for browsers by sending expiration headers.