Troubleshooting ADF ViewObject Range Paging Issues

2

ADF BC ViewObjects provide a very valuable mechanism to page through large data sets so that a user can navigate to a specific page in the results. Range Paging fetches and caches only the current page of rows in the ViewObject row cache (at the cost of another query execution) to retrieve each page of data. Range paging is very performing when your ViewObject has access to (hundreds of) thousands of database rows, and if you want to avoid JVM memory overconsumption. In my current project I ran into two severe performance problems that I will discuss in this blog. 

Issue one: Range Paging generates a SQL hint that can be very inefficient

The main search screen of our application uses ViewObject Range Paging. Extensive tuning and testing by our database experts proved that the query was extremely performing in the database when the concept of range paging was used (we only query around 40 – 50 rows each time). On our development environment everything worked fine. We always do performance analysis and regression analysis after new patches/releases to our test/acceptation environment. To our surprise, after a new release we detected a very slow ViewObject query and a very slow ‘SELECT COUNT(1)’ query in the ADF Performance Monitor, that was generated by our ViewObject with Range Paging. These queries correspond to the ViewObject methods executeQueryForCollection and getQueryHitCount():

getQueryHitCount2

After analyzing, we noticed that the ADF framework adds a hint to the ViewObject SQL:  /* FIRST_ROWS */

see_page_9_range_paging2

and this is exactly the problem in our case. First of all, the hint performs better when the range size (50) would be added to the SQL that we configured on our ViewObject:    /* FIRST_ROWS (50) */

range_range_paging

But in our case (to my own surprise) without the added SQL hint, the query is much faster: 100 milliseconds instead of 20 seconds! Our database expert explained that this was due to the execution plan that was chosen by the database. We searched and found a way to prune the SQL hint out of our query. The regex pattern for an SQL hint can be:

private Pattern pattern = Pattern.compile("/\\*\\+.*?\\*/", Pattern.DOTALL);

and by overriding the ViewObject method buildQuery() we can replace the hint with an empty string:

buildQuery

In this way the query performs really well – under 100 milliseconds.

Issue two: the SELECT COUNT(1) can be very slow

Frequently a ‘SELECT COUNT(1)’ query is executed to manage the number of rows displayed in a table, tree or treetable. When ViewObject Range paging is used, the framework automatically executes this query because it needs this information to manage the user scrolling up and down the table. In some cases this query can be very expensive and troublesome. In some case a RowCountThreshold at the iterator can be useful to avoid a full table scan:

rowcountthreshold

But in our case we had to create a dedicated ViewObject with a different, more efficient query to count the number of selected rows. We created a method on the ApplicationModule that executes this ‘CounterViewObject’. Now, the original ViewObject calls this method instead of the standard ‘SELECT COUNT(1)’ query. We do this in the overridden method getQueryHitCount():

getQueryHitCopunt_override

And in the counter method on the ApplicationModule the ‘CounterViewObject’ is executed:

executeQuerySearchShipmentsHitCount
In this way we could solve our performance issues with ViewObject Range Paging; now we have a very performing search screen with Range Paging:

searchscreen

Share.

About Author

Frank Houweling is an Oracle ADF and Java specialist with AMIS (The Netherlands). He focuses mainly on Oracle Fusion ADF, Java Enterprise development and performance management. During the past years he has been requested several times as troubleshooter of ADF projects with bad performance. As such he has been performing performance analysis, bottleneck detection and developing mitigating solutions based on these analysis. He is also the creator of the AMIS ADF Performance Monitor, an advanced monitor that can identify, report and help solve performance bottlenecks in ADF applications.

2 Comments

  1. Hello,
    is it possible to publish the version(s) of JDeveloper/ADF where this issue was found and resolved?
    Mostly I’m interested where it was resolved.
    JDeveloper versions are different, something works in one version something in another, something does not work at all.
    Also it will be really helpful to play with working example based on simple emp and dept tables.
    Thanks for interesting article!

    • Frank Houweling on

      Alexander, our current project is in ADF11g version 11.1.2.3
      Just to be clear; in our case we had problems with standard VO range paging queries because we have very large tables to query from (> 30 million records).

      -This first issue of this blog (generating /* FIRST_ROWS */ hint) is ViewObject Range Paging specific. As far as I know generated by all ADF versions. Oracle could improve this leaving out the hint at all, or by adding how many rows are in range ( /* FIRST_ROWS (50) */)
      -The second issue is more general; if you have a page with a table or tree on it, ADF queries automatically the SELECT COUNT(1) query. In our case this query was very inefficient.

      Thanks, Frank

Leave a Reply