1

We use an application made by HPE which blocks completely from time to time. The application comes with Java 6, uses ojdbc6 and connects to Oracle 11. HPE claims that when one of our 6 application servers dies when it runs a blocking update to a central table this causes the Oracle server to wait infinitely effectively rendering all servers defunct. I can see the blocking statement (our DBA showed me) and can see that the connection pool of all servers grows until it hits a defined maximum. But I have doubts that Oracle does not detect the died connection.

Can someone confirm HPEs theory or give me a hint to further diagnose this ?

2

By default, an Oracle database does not detect dead clients, you need to enable it explicitly by setting SQLNET.EXPIRE_TIME in sqlnet.ora.

SQLNET.EXPIRE_TIME

Since the database does not detect dead clients, it maintains its session and locks, so the answer is yes, killed application processes can cause blocking.

This parameter is often misinterpreted. Setting this parameter to 10 does not mean dead connections will disappear at most in 10 minutes. This means the database server sends a probe packet every 10 minutes. The time required to detect a dead client depends on the operating system as well. For example on Linux, an already established TCP connection is valuable, so even if there is no answer from the other side, the server keeps trying to reach it. When the database server sends the probe packet and receives no answer, it will try again, 15 times in total (because of the tcp_retries2 kernel parameter), and the total time needed to detect a dead client can be way more than 10 minutes.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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