<<Back to DB Administration Main Page
What is Dead Connection in Oracle Database
A dead connection is a connection which is holding the resources and hanging around because the client has been abnormally terminated
What is Dead Connection Detection?
Dead Connection Detection (DCD) is a SQL*Net/Net8 feature to identify connections that have been left hanging by the abnormal termination of a client. The PMON process then free up the resources from these connections.How to enable Dead Connection Detection feature?
DCD is enabled on the server side by setting a parameter in the sqlnet.ora file in $ORACLE_HOME/network/admin called SQLNET.EXPIRE_TIME. The value is in minutes SQLNET.EXPIRE_TIME= <# of minutes>
How does Dead Connection Detection Work?
DCD is initiated on the server when a connection is established. At this time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.When the timer expires, SQL*Net on the server sends a "probe" packet to the client. (In the case of a database link, the destination of the link constitutes the server side of the connection.) The probe is essentially an empty SQL*Net packet and does not represent any form of SQL*Net level data, but it creates data traffic on the underlying protocol.
If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources
The DCD mechanism does NOT terminate any sessions (idle or active). It merely marks the "dead" session for deletion by PMON background process.
Pre-12c Mechanism
Pre-12c Server process sends SQL*Net probe packets to verify connectivity if the connection has been ideal for more than the time specified in SQLNET.EXPIRE_TIME parameter. If sending the probe fails an error is returned causing the server process to exit. the time taken for send failures depends on system wide tcs parameters
tcp_retries1
tcp_retries2
12c Mechanism
Dead connection detection has been enhanced in oracle 12c database to reduce the amount of time taken to detect the terminated connection by using tcp keep-alive. The following 3 tcp keep-alive parameter are tuned at a per-connection level to detect dead connection.
TCP_KEEPIDEAL <= specifies the timeout , with no activity until the first keep-alive packet is sent
default value is 2Hrs but this parameter takes its value from SQLNET.EXPIRE_TIME
TCS_KEEPCNT <= count of keep-alive prob sent (always set to 10)
TCP_KEEPINTVL <= interval between keep-alive packets sent but no acknowledgement received (always set to 6)
Comments
Post a Comment