Dead Client Detection (DCD) in Oracle

A connection in Oracle might be one of many types — most popular begin DEDICATED server and SHARED server. We will discuss the “DEDICATED” mode only in this post.

Sid and Serial# jointly identify a record in the v$session view. Suppose the client application makes a connection and starts a transaction and holds locks and just then, the network connectivity is lost or a power-failure occurs on the machine from where the client application is running from (assuming that it is not running on the DB Server), in all such scenarios, the client is not able to communicate to the DB that the connection is going away and hence leaves hung sessions and if those sessions were holding locks, open locks on the DB. Such sessions are called as orphaned sessions. PMON has no visibility into these and hence it is not able to clean this session up (nor it’s resources).

Issues with Orphaned Sessions:

Orphaned sessions use server resources, and may have open cursors, temporary tables, or locks. These locks may block other connections from performing useful work, and can sometimes be the result of a major “pile up” of locks. In severe cases, it can appear that Oracle has stopped working.

Resolutions:

You can enable dead client detection (DCD) in the sqlnet.ora file on the DB Server (not on the client application machines). It is done by setting the SQLNET.EXPIRE_TIME setting in the sqlnet.ora file on the DB Server. The time specified is in minutes and what it does is that it sends a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This setting is intended for the database server, which typically handles multiple connections at any one time.

Limitations on using this terminated connection detection feature are:

* It is not allowed on bequeathed connections.
* Though very small, a probe packet generates additional traffic that may downgrade network performance. So, if you put a lower number for the time parameter, it can create issues for you.
* Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in downgrading network performance.

The advantage of setting this parameter is that you won’t have “dead” clients hogging resources on your DB.