2 Replies Latest reply: Aug 23, 2013 12:39 PM by Skip - Absolute RSS

    Identify performance difference between APEX and SQL Developer for same query?

    Skip - Absolute

      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.190500.00028IR binding: "P707_INTERNAL_ORDER_FLAG" value="N"4


      0.19079372.06277...Session State: Save "P707_INTERNAL_ORDER_FLAG" - saving same value: "N"4


      372.253560.00086Printing rows. Row window: 1-10. Rows found: 74


      372.254410.00003Evaluate which sub regions should be rendered4



      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, 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? 





        • 1. Re: Identify performance difference between APEX and SQL Developer for same query?

          Hi Skip,


          1)  I think 1:31 is a long time to wait for a report to run, let alone 6:45.  Most users of Web applications expect responses in a second or two, not minutes.


          2)  Understand that bind-peeking and adaptive cursor sharing is not available for the user-parsed SQL in an APEX application - it is available in SQL Developer.


          3)  If I were you, I would compare the respective query plans, and if all else fails, run the APEX page view with Tracing turned on, run TKPROF over it, and figure out where all the time is being spent.


          4)  If this information is not changing by the second, you should think about having a job that completely refreshes materialized views and your APEX application would then query against the pre-aggregated materialized views.  It would be sub-second then.


          I hope this helps.



          • 2. Re: Identify performance difference between APEX and SQL Developer for same query?
            Skip - Absolute

            Hi Joel,



            Thanks for the guidance.



            1) At this stage in the application and our work with the customer, and the budgets for time and $, we can live with the minute and a half, since this is a once or twice a day report, if not a daily subscription.  Obviously it's not ideal, but perhaps a temporary compromise.



            2) This is the answer I was looking for. I just did a little reading, since bind-peeking and adaptive cursor sharing are totally new concepts to me.  Without my needing to understand all the mechanics, I gather it is not strictly correct to compare performance of a given query between SQL Dev and APEX user-parsed SQL, if they don't play by the same rules. 



            3) The tuning process will help the DBA if we get to the point of detailed performance analysis of this (or other pages). 



            4) The application works with EBS data and pulls together a great deal of complex data into a materialized reporting view.  So that's materialization is technique we're familiar with, and which has been a focus of much discussion with the customer.  We take advantage of the fact that indeed, the historical data does not change.  That has turned most of the application, where we look at details, into something that's practical for a web application. 


                    However, I have not given a thought to materializing the reporting aggregates.  This sounds like the right way to go once the customer gets familiar with the data and decides what's important.



            All your comments have been a huge help!  Thanks.