Follow Us on Twitter

Slow loading of tabular forms in Application Express

by Patrick Sinke on October 11, 2012 · 5 comments

Under some circumstances, there is an issue (observed in Application Express 4.1.1.00.23) that page tabular forms take very long to load, up to several minutes. Even if there is a very small dataset in the underlying table.  When I encountered this issue, the report ran fine on Development en Test environments, but in the almost identical production environment the page took minutes to load.

When examining the debug, I found the following line of Apex generated code, taking up to 30 seconds:

begin
   select /*+ cardinality(t 10) */
         disp
       , val
         bulk collect
   into wwv_flow_utilities.g_display
      , wwv_flow_utilities.g_value
   from table(wwv_flow_utilities.get_temp_lov_data(4)) t
   order by insert_order, disp;
end;

The main culprit appears to be the wwv_flow_utilities.get_temp_lov_data. This function is called whenever you create an item of the type “Select List (Named LOV)”. The workaround I found is to change the type to “Select List (Query Based LOV)” or “Select List (Static LOV)”. After altering the report attribute type, the report is loading normally.

Report Attributes

 

 

 

 

 

 

Downside is that it will make your code less maintainable. Hopefully the problem is fixed in Apex 4.2.

Slow loading of tabular forms in Application Express, 4.7 out of 5 based on 3 ratings
Ratings:
VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)

5 comments on “Slow loading of tabular forms in Application Express

  1. Thanks for the tip.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  2. Patrick,
    thanks for sharing! Could you meanwhile find out if this problem is gone in Apex 4.2?
    Or have you been able to track this bug further down?

    I just ran into the same scenario, but on the application’s login screen (guess I can’t change the report type, there). Only significant difference between the two systems is the DB version (smooth on 10.2.0.4, slow on 10.2.0.5). One single login produces some 1000 calls to wwv_flow_utilities.get_temp_lov_data which in turn produces 1000 different versions in the SQL area. Ouch.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • Patrick Sinke on said:

      Apologies for my late response. I haven’t been able to test this in 4.2, because I currently have no testcase where the problem occurs. The underlying code must have a serious issue when such small datasets generate that amount of SQL. I also find it odd that the DB version makes a difference. This might point us to a solution some day, hopefully!

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  3. Patrick,
    Thank you for the insight. I was hired by USG and directly I was asked to support two APEX application without APEX training or experience. My first encounter with users was”This Apps is a crap, it takes too long to access and it is inconsistant, good luck …” Wow that was my response. How do you debug APEX Application to find out the root cause of the application. I agree with you, when I use the application in Development it is fast but in production, it is slow and it hang when the user click next on pagination to go to the next report page.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • Patrick Sinke on said:

      Debugging must be enabled in the Application definition (go to the Application Builder, select your application and click the “Edit Application Properties” button on the top right. Set Debugging to Yes in the Properties section).
      When you start the application from the Application Builder, there will be a developer toolbar on the bottom of every page. Click on “debug”, the page will reload. Then click “View Debug” and a popup window will appear. In the debug tab, click on one of the View Identifiers and you’ll see a detailed view of all actions during page load and the time it took. Here you can quickly identify performance bottlenecks.
      Hope that helps!

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter