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 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:

   select /*+ cardinality(t 10) */ 
       , 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; 

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
VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)

{ 5 comments… read them below or add one }

raoul October 12, 2012 at 7:06 pm

Thanks for the tip.


Uwe September 4, 2013 at 12:38 pm

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, slow on 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.


Patrick Sinke November 18, 2013 at 12:26 pm

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!


Ghislain November 14, 2013 at 6:11 pm

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.


Patrick Sinke November 18, 2013 at 12:22 pm

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!


Leave a Comment


Previous post:

Next post:

About Whitehorses
Company profile

Whitehorses website

Home page

Follow us
Blog post RSS
Comment RSS