Is it possible to specify connection/query timeout for the Oracle database queries? Either on Oracle side or in Oracle's JDBC driver (10.2.0.4)? So, that Java client just got an error back after, let's say, 2 minutes instead of waiting until Oracle finishes executing the query?
+2
A:
If you are executing the query in the context of a transaction, the transaction timeout value of the JTA transaction monitor will be the determinant to query timeout. The configuration for this depends from one application server to another.
At an individual query level (in the absence of a JTA transaction monitor), the setQueryTimeout method can be used to set the timeout on the execution of a Statement/PreparedStatement/CallableStatement object.
Update
setQueryTimeout is not to be relied on, although it works (atleast from a J2SE client). It works via the JDBC driver performing a full round-trip to the Oracle database server. Then, it is upto the database to halt execution of the query. Don't rely on it for time critical applications.
Vineet Reynolds
2009-09-07 11:23:42
We are having problems with JTA timeout as it does not affect running query. And actually WebLogic server which we run on tries to kill the long running connection and creates another thread to kill it, but it hits Java lock in Oracle JDBC driver thus causing both threads to wait. The problem becomes even worse as WebLogic tries to create even more killer-threads and eventualy runs out of them.
Superfilin
2009-09-07 12:10:50
The point about setQueryTimeout seems very intresting and I haven't thought of that before actuallly :). We don't really care if setQueryTimeout will take some time to cancel the query, the main thing here is the result :). I will get back on the test results.
Superfilin
2009-09-07 12:13:57
The reason setQueryTimeout() might not work in WLS, is more so because of JTA. I don't think the driver will respond to setQueryTimeout calls in a transaction context, but I might be incorrect.
Vineet Reynolds
2009-09-07 15:02:01
You were right setQueryTimeout didn't work in JTA context :(
Superfilin
2009-09-08 21:37:15
In case your transaction timeout value is too high, you could verify the timeouts set in the JTA service for WLS, and in trans-timeout-seconds property in ejb-jar-xml (for CMTs) and in UserTransaction.setTransactionTimeout() for BMTs.
Vineet Reynolds
2009-09-08 22:28:57
It seems that setQueryTimout actually works in JTA context, but not always. Sometimes it times out the query after 5 minutes (the value I set in the method), sometimes after 20 minutes, sometimes after 60 minutes.
Superfilin
2009-09-09 21:08:18
The JTA timeout value was actualy not that high in our environments and equals to 30 seconds.
Superfilin
2009-09-09 21:09:18
Oh well, when I said that it should not be relied on for time critical applications, I did not anticipate this kind of behavior. By the way, Weblogic and most other application servers will rollback the transaction (thus timing out the query) on transaction timeout, almost immediately. You might want to check if there is a bug related to your current setup, or whether the load on either the application server or database is higher than anticipated.
Vineet Reynolds
2009-09-09 21:13:47
+1
A:
Have a look at Oracle profiles. This allows you to specify several limits at the database level. One of them is a maximum CPU time per query.
If you have queries running for more than 2 minutes on a regular basis you might want to do some tuning of your queries first.
Rene
2009-09-07 13:30:47
Oracle profiles affect all queries for the user. The OP might be interested in just one particular query that needs to be cancelled.
Vineet Reynolds
2009-09-07 14:56:15
Oracle profiles will be our next step if the setQueryTimeout will not work :).
Superfilin
2009-09-08 10:40:02