For one of our applications (Pentaho Data Integration), we need to run queries through the oracle jdbc. Some of these queries are computation expensive and can take an hour or more to return any rows to the client. When I try to run one of the long running queries with default jdbc parameters, I inevitably get: "IO Error: Socket read timed out" after ~30 minutes even though the database is still processing the query (verified in V$SESSION and V$SQLAREA). I am using the newest ojdbc8.jar direct from Oracle and running Oracle Enterprise 12.1.0.2.0.
EDIT:
The output of these queries is being stored in a separate database. The nature of our environment is such that we have an arbitrary number of queries that could be created/run so using stored procedures would be cumbersome.
Best Answer
You can use PL/SQL and schedule jobs to populate tables or schedule jobs to update materialized views. Then once the data has been updated you can query as much as you want, since the heavy lifting will be done. You should also try tuning the queries. If you have an index with the filter columns and any other columns that you need, then you can avoid reading the index then the table. If you start indexes with the low cardinality columns going towards high cardinality columns, you can reduce the amount of rows looked at. Look at materialized views and including rowid so that you can try to do a fast refresh.