I have a weird problem with a query running in my JDeveloper ADF web application. This is a simple search form that returns a select statement to an Oracle 10g database. When the search is sent, the ADF structure (first) launches the query and (second) executes the same query enclosed in " select count(1) from (...query...)" - the goal here is to get the total number of rows and display "Next" 10 results ".
So far so good. The problem arises from the outrageous work that I get from the second request (the one with the " count(1)"). To investigate the problem, I copied / pasted / executed a query in SQL Developer and was surprised to see a much better answer.
When comparing the query execution in ADF and SQL Developer, I took all measures to provide a representative environment for both executions: - a recently restarted database - the same for OC4J Thus, I can be sure that the difference is not related to caching and / or buffering , in both cases, db and the application server were started (repeatedly).
The tracks I took for both sessions illustrate the situation:
The request is made 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
The 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!