Can a killed application process cause blocking of the server

blockingoracleoracle-11g-r2

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 ?

Best Answer

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.

Related Solutions

Sql-server – how to find the T-SQL of a sleeping session that is blocking some other process

How can I find the code of the blocking session, in this case, the session 75 when the session's status is 'sleeping'?

New version this new version shows also the blocking session, however, I could not find out how to get the database name and other data from a sleeping session.

You can use

SELECT db_name(S.database_id) AS DatabaseName,
        ST.text
FROM   sys.dm_exec_connections AS C
       JOIN sys.dm_exec_sessions AS S ON S.session_id = C.session_id
       OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE  C.session_id = 75;

Sql-server – What could be preventing updates and inserts to a table without anything visibly blocking the process

reads: 25,507,251
writes: 9
start_time: 2015-12-15 10:44:49.200
collection_time: 2015-12-15 10:45:18.237

Your session is not blocked. It just have to do a tonne of reads before applying any write. Large data, perhaps a blocking operator like a sort or a HT. It likely didn't yet got to the INSERT part.

This is basic performance issue. Start from How to analyse SQL Server performance.

PS. Is this a cartesian join?

from inserted [BlockedTable] , dbo.MSmerge_partition_groups msp 
where  (msp.publication_number = 1 and ( 
   ((blocked_table_id in (... )) and for_replication = 1)) ))

Best Answer

Related Solutions

Sql-server – how to find the T-SQL of a sleeping session that is blocking some other process

Sql-server – What could be preventing updates and inserts to a table without anything visibly blocking the process

Related Question