Why is there such a big difference in the execution time of a query ran by ADF and in SQL Developer

Question

I have a strange issue with a query running in my JDeveloper ADF web application. It is a simple search form issuing a select statement to Oracle 10g database. When the search is submitted, ADF framework is (first) running the query, and (second) running the same query wrapped within "select count(1) from (...query...)" - the goal here is to obtain the total number of rows, and to display the "Next 10 results" navigation controls.

So far, so good. Trouble comes from the outrageous performance I am getting from the second query (the one with "count(1)" in it). To investigate the issue, I copied/pasted/ran the query in SQL Developer and was surprised to see much better response.

When comparing the query execution in ADF and SQL Developer, I took all measures to ensure representative environment for both executions: - freshly restarted database - same for the OC4J This way I can be sure that the difference is not related to caching and/or buffering, in both cases the db and the application server were freshly (re)started.

The traces I took for both sessions illustrate the situation:

Query ran in ADF:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.97       0.97          0          0          0           0
Fetch        1     59.42     152.80      35129    1404149          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     60.39     153.77      35129    1404149          0           1

Same query in SQL Developer:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.02       1.16          0          0          0           0
Fetch        1      1.04       3.28       4638       4567          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.07       4.45       4638       4567          0           1

Thanks in advance for any comments or suggestions!

Answer 1

Ok, I finally found the explanation of this ghastly behaviour. To make the long story short, the answer is in the definition (Tuning parameters) of my ViewObject in JDeveloper. What I was missing were these two important parameters:

• FetchMode="FETCH_AS_NEEDED"
• FetchSize="10"

Without them, the following happens - ADF runs the main query, binds the variables and fetches the results. Then, in an attempt to make an estimate of the rowcount, it launches the same query enclosed in "select count(1) from (my_query)", but ...(drum roll)... WITHOUT BINDING THE VARIABLES!!! It really beats me what is the use of estimating the rowcount without taking into account the actual values of the bind variables!

Anyway, it's all in the definition of the ViewObject: the following settings needed to be set, in order to get the expected behaviour:

• All Rows in Batches of: 10
• (checked) As Needed
• (unchecked) Fill Last Page of Rows when Paging through Rowset

The execution plan could not help me (it was identical for both ADF and SQL Developer), the difference was only visible in a trace file taken with binds.

So, now my problem is solved - thanks to all for the tips that finally led me to the resolution!