3

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.

Improve this question
2
  • Make sure that the firewall or any other device involved in between the database server and client is not killing your connection.
    – atokpas
    Commented Feb 8, 2018 at 10:23
  • There is no firewall between the two servers in question.
    – Andrew Hangsleben
    Commented Feb 8, 2018 at 21:02

2 Answers 2

Reset to default
0

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.

Improve this answer
2
  • I realize I was missing a bit of information about the problem. Our environment needs to allow for any query to be created and run. Using stored procedures for this would be unduly cumbersome and require extra space for storing the output before pushing to the destination server. I've updated the question to reflect this.
    – Andrew Hangsleben
    Commented Feb 8, 2018 at 21:04
  • There are two aspects to what you want. How do you keep JDBC from timing out when waiting for a long query and how to tune the queries that take the most resources/time. If you post specific queries and explain plans, than people can help you to tune those queries. If you post your JDBC configuration, than people can help you to tune that. Even though individual queries may vary, you can create materialized views that are nearly current and take out much of the work for getting the data. You just need to think about how to do that and make them generic enough for all of your queries.
    – Gandolf989
    Commented Feb 9, 2018 at 13:33
0

When I try to run one of the long running queries with default jdbc parameters ...

Is there some reason you can't use the method ...

java.sql.Statement.setQueryTimeout()

... to extend the timeout for these particular queries?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.