Monday, September 17, 2012

oracle.net.CONNECT_TIMEOUT

A Oracle Consultant suggested to set the property

oracle.net.CONNECT_TIMEOUT=10000
for every ConnectionPool

What is this?

http://docs.oracle.com/cd/E25178_01/core.1111/e10106/dbac.htm

__________________________________


Another useful property is:

http://docs.oracle.com/cd/B28359_01/rac.111/b28252/configwlm.htm

"Set TimeToLiveTimeout to m seconds
Set PropertyCheckInterval to n seconds

if the network call is blocked, then just setting the TimeToLive timeout alone won't work. In that case, an additional step is needed -- setting the JDBC driver connection properties as follows:

for the Thin driver: oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR
for the OCI driver: sqlnet.outbound_connection_timeout
"

oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR

There is a Oracle KB doc:
"How to Ensure that JDBC Queries are Always Timed Out [ID 559564.1]"

For more info on the TimeToLiveTimeout and PropertyCheckInterval :

http://docs.oracle.com/cd/B13789_01/java.101/b10979/conncache.htm#sthref460

_____________________________

set a timeout on Socket level for the jdbc thin driver:

oracle.net.READ_TIMEOUT (in ms)

in reality:

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

see http://docs.oracle.com/cd/E17904_01/web.1111/e13737/generic_oracle_rac.htm

"In some deployments of Oracle RAC, you may need to set parameters in addition to the out of the box configuration of a data source in an Oracle RAC configuration. The additional parameters are:

Set oracle.jdbc.ReadTimeout=300000 (300000 milliseconds) for each data source."

_____________________________


The Oracle Consultant says:
"I would set the read and connect timeouts to slightly longer than the stuck thread time setting.
This ensures you get stuck thread warnings but the thread is released soon after "


_________________


Related Oracle KB docs:

ORACLE.NET.READ_TIMEOUT Property Does Not Work On 11g JDBC Driver [ID 1341966.1]


WebLogic Server: Intermittent Stuck Threads Caused Due to the 11.1.x Oracle JDBC Thin Driver [ID 1083794.1] (this one seems to be pretty old)


How to Ensure that JDBC Queries are Always Timed Out [ID 559564.1], the suggest using

  • for the Thin driver:  oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR
  • for the OCI driver:  sqlnet.outbound_connection_timeout






No comments: