7

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?

Andrey Adamovich
  • 20,285
  • 14
  • 94
  • 132
  • See also [How is Oracle's JDBC query timeout implemented?](https://stackoverflow.com/questions/2376615/how-is-oracles-jdbc-query-timeout-implemented) and [Setting Network Timeout for JDBC connection](https://stackoverflow.com/questions/18822552/setting-network-timeout-for-jdbc-connection) – Vadzim Sep 18 '19 at 17:10

4 Answers4

9

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
  • 76,006
  • 17
  • 150
  • 174
  • 1
    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. – Andrey Adamovich Sep 07 '09 at 12:10
  • 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. – Andrey Adamovich Sep 07 '09 at 12:13
  • 1
    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 Sep 07 '09 at 15:02
  • 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 Sep 08 '09 at 22:28
  • 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. – Andrey Adamovich Sep 09 '09 at 21:08
  • The JTA timeout value was actualy not that high in our environments and equals to 30 seconds. – Andrey Adamovich Sep 09 '09 at 21:09
  • 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 Sep 09 '09 at 21:13
5

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
  • 10,391
  • 5
  • 33
  • 46
1

According to http://www.javamonamour.org/2012/09/oraclenetconnecttimeout.html

oracle.net.READ_TIMEOUT for jdbc versions < 10.1.0.5 oracle.jdbc.ReadTimeout for jdbc versions >=10.1.0.5

So if you are using a JDBC driver version 10.1.0.5 or higher, then oracle.jdbc.ReadTimeout is the correct property.

Srini Karthikeyan
  • 701
  • 8
  • 14
0

Setting oracle.jdbc.ReadTimeout helped to timeout the jdbc calls. I have used it in a production spring boot app by specifying datasource properties as below

spring.datasource.hikari.connection-timeout=1000
spring.datasource.hikari.dataSourceProperties=oracle.jdbc.ReadTimeout=2000

Note: Earlier this app was using tomcat connection pool and after setting the above properties for the same, timeouts were happening but the pool was not able to handle the closed connection objects efficiently. Therefore, I switched to hikariCP and got lot better results. I have made a video simulating the slow jdbc calls which compares the results with tomcat and hikari connection pools while using the ReadTimeout property.

Vaibhaw K
  • 169
  • 2
  • 5