Slow view execution using ADF and OracleSQL

Question

I have an unusual problem when trying to execute a view object in a Oracle database. The query itself executes using Toad for Oracle within 10 seconds, while executing the same query in a view object takes more than a minute. I am using JDeveloper 10.1.3.3.0.4157 with ADF, and am connecting to an 10g Oracle database.

What I found is that while the execution is taking place, the database server is constantly being "pinged" with 128 bytes of data (I've done this using Wireshark). This, in turn, clogs the connection and is the reason it takes long for the data to get to the application. I've tested this theory with session tracing, and it turns out the query itself executes in the same time-frame as the one from Toad.

I suppose the problem is somewhere in the database driver, but since I cannot debug it, I'm left empty handed.

Any suggestion would be appreciated!

MORE INFO:

The query in question is somewhat large. It is also built in run-time and is variable (however, the number, name and type of the attributes is constant). The interesting part is that when the query is small(er), the execution time falls to the one present in the Toad execution.

A similar "pinging" effect was detected in another view that belongs to the same application module, but testing implied that this was not a problem with the AM configuration, since it's identical to all the other ones, and they are working fine. Plus, other views in the same AM as the query in question do not show any signs of the same problem.

I've been playing with the view Tuning settings, but with no difference to the execution time.

Answer 1

Try creating your view query as database view. Then create view object on that view and use view criteria to shape data. Only simple select * from complex_view would be in ViewObject. Also you could try materialize that database view to improve performance.