The Problem
A page takes 5 to 15 seconds to load. Users complain. You are not sure whether the bottleneck is in the database, the PL/SQL processes, the network, or the browser rendering.
Step 1: Enable Debug Mode
Add &debug=YES to the URL or click the Debug button in the Developer Toolbar. APEX logs every processing step with timestamps. Look at the Debug output for the page show (rendering) and identify which component takes the most time. The debug log shows SQL execution times, PL/SQL process times, and region rendering times.
Step 2: Check SQL Execution Times
In the Debug output, find lines like “Execute Statement” with execution times. If a region’s SQL takes 3 seconds, that is your primary target. Copy the SQL into SQL Developer, add the bind variable values, and run EXPLAIN PLAN to identify missing indexes, full table scans, or expensive joins.
Step 3: Check PL/SQL Processes
Before Header and After Header processes run during page rendering. If a process calls a slow procedure or runs a heavy query, it blocks the entire page. Move expensive computations to On Demand processes called asynchronously after the page loads, or cache the results in application items.
Step 4: Check Browser Rendering
If the Debug output shows the server rendered the page in 200ms but the user experiences 5 seconds, the bottleneck is client-side. Open the browser DevTools Network tab to check for large JavaScript or CSS files, slow image loading, or many AJAX calls. The Performance tab shows JavaScript execution time and layout/paint costs.
Common Fixes
Add indexes on columns used in WHERE clauses of report queries. Replace PL/SQL function calls in SELECT lists with JOINs. Reduce the number of regions on the page. Use Lazy Loading on regions below the fold. Set “Maximum Row Count” on Interactive Reports to prevent expensive COUNT queries. Move large LOV queries to cached Shared LOVs. Reduce the number of page items with dynamic LOVs that each execute a separate query during page load.
Step 5: APEX Advisor
Run APEX Advisor (Utilities menu) which checks for common performance anti-patterns: report regions without pagination limits, page items with expensive LOV queries, redundant computations, and more.