I have an APEX Interactive Report at a customer site that returns the page results in 6:45 (min:sec) elapsed time where SQL Developer does it in 1:31. (The times are very consistent from one run to another. This does not seem related to database cache not being loaded - I've discarded those long times that cause APEX Listener to timeout the page.)
I need to understand the root causes before they put the APEX application into production. I want to find out what causes the discrepancy and how to reduce the APEX IR elapsed time to closer to SQL Dev's. I have limited access to the customer's DBA team and other tools and system privileges.
APEX runs the query as APEX_public_user, SQL Dev runs under my schema, xxpva. The application's parsing schema is xxpva. I have captured the entire sql statement APEX submits to the database using SQL Dev and then run it myself. APEX adds a few artifacts to the region query to handle highlight formatting and column aggregate totals. These do not cause SQL Dev to take any additional time.
The region query summarizes 7 months of data in 2.7 million view rows, and returns 7 rows. Pagination type = Row Ranges X to Y, and Maximum Row Count is null. When the query filter parameters are set to summarize a much smaller number of view rows, or none (where the result is 'No data found'), the page can run in a second or two.
When I look at APEX's Debug report, it shows a total of 372.26 seconds elapsed and 372.06 Maximum Execution Time. In the series of steps, after APEX puts together the query, it goes through about 30 statements, most of which are IR binding for the highlights. Then it does IR binding of the page's parameter Items and ...Session State:Saves. The table below shows the lasst of these two. The final Session State: Save gets billed for the 372.06 seconds. My assumption is that is when the query is actually run by the database and rows are returned to APEX.
| 0.19050 | 0.00028 | IR binding: "P707_INTERNAL_ORDER_FLAG" value="N" | 4 | 
|
| 0.19079 | 372.06277 | ...Session State: Save "P707_INTERNAL_ORDER_FLAG" - saving same value: "N" | 4 |
|
| 372.25356 | 0.00086 | Printing rows. Row window: 1-10. Rows found: 7 | 4 |
|
| 372.25441 | 0.00003 | Evaluate which sub regions should be rendered | 4 |
|
Another observation with different filter parameters that return rows for only two months, summarizing 200 rows for each of the two, runs in 15 - 17 seconds in APEX, and 32 - 34 seconds in SQL Dev.
The primary filter parameters are the business operating unit and the date window. One of the operating units (the one in the above long running query) has half the data, and 40 others share the rest. The 15 second query above is one of these others.
Is the database picking different query plans depending on whether APEX or SQL Dev, with the different users, is submitting the query?
The environment is APEX 4.2.2.00.11, using the latest APEX Listener, and Oracle Database 11.2.0.
Does anyone know where such a big discrepancy can come from, how to find and fix it?
Is it likely this effect is happening for most of the pages, driving slow or erratic performance, but where it's not as noticable if the page returns data in 5 - 20 seconds?
Thanks.
Skip