Dead Connection Detection is a useful feature of the Oracle database: it allows for the cleanup of “dead” sessions so they don’t linger around consuming memory and other system resources. The idea is simple: if the database detects that a client process is no longer connected to its server process, it cleans up. This can happen in many ways, in most cases this kind of problem is triggered by an end user.
A dead connection shouldn’t be confused with idle connections: an idle connection still maintains the network link between client and server process, except that there is no activity. Idle connections aren’t maintained/controlled via DCD, there are other tools in the database handling such cases.
As a by product, DCD can also help with overly eager firewalls forcibly removing seemingly idle network connections. I found the following posts and the references therein very useful:
- http://sve.to/2016/04/07/dead-connection-detection-oracle-database-12c/
- http://sve.to/2016/03/31/oracle-tns-12535-dead-connection-detection/
With Oracle 12c Oracle changed the way DCD works by no longer relying on its network layer but rather pushing the functionality into the TCP stack on platforms that support it. This change in behaviour is also explained in an Oracle white paper from 2014.
For the first part of this post I set sqlnet.expire_time to 1 as per a MOS note I found, your value is probably different. The parameter is documented in the Net*8 reference, please use it to work out what the best value is for you. As others have pointed out, this parameter has to go into the RDBMS home, more specifically $ORACLE_HOME/network/admin/sqlnet.ora. I am using Oracle 19.4 on Oracle Linux 7.7 for this blog post. I have seen the same behaviour in 12.1.0.2 as well in my lab.
Background
In addition to the proven methods of checking whether TCP_KEEPALIVE is enabled for a given session I wanted to show another one. Using the ss(8) utility it is possible to show socket options. I also tried lsof but on my system I couldn’t get it to print the options:
SQL> select spid from v$process where addr = (select paddr from v$session where username = 'MARTIN'); SPID ------------------------ 13656 SQL> exit ... [root@server2 ~]# lsof -nP -p 13656 -T f lsof: unsupported TCP/TPI info selection: f lsof 4.87
Although the man-page for lsof reads:
-T [t] controls the reporting of some TCP/TPI information, also reported by net‐
stat(1), following the network addresses. In normal output the information
appears in parentheses, each item except TCP or TPI state name identified by
a keyword, followed by `=', separated from others by a single space:
<TCP or TPI state name>
QR=<read queue length>
QS=<send queue length>
SO=<socket options and values>
SS=<socket states>
TF=<TCP flags and values>
WR=<window read length>
WW=<window write length>
Not all values are reported for all UNIX dialects. Items values (when avail‐
able) are reported after the item name and '='.
When the field output mode is in effect (See OUTPUT FOR OTHER PROGRAMS.)
each item appears as a field with a `T' leading character.
-T with no following key characters disables TCP/TPI information reporting.
-T with following characters selects the reporting of specific TCP/TPI infor‐
mation:
f selects reporting of socket options,
states and values, and TCP flags and
values.
So let’s try something else: ss(8) – another utility to investigate sockets
Revealing socket options
I have used ss(8) in the past when I didn’t have netstat available, which is more and more common now that netstat is deprecated and its designated successor is ss :)
As far as I know you can’t limit ss to show information just for a PID, I use grep to limit the output. The output is in fact very wide, which is why this might not look pretty on the blog depending on whether the renderer decides to wrap output or not.
[root@server2 ~]# ss -nop | egrep 'NetidState|13656'
NetidState Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,4.412ms,0)
[root@server2 ~]#
I used the following options:
- -n for “Do not try to resolve service names”
- -o for “Show timer information” and finally
- -p to “Show process using socket”.
The main option here is -o. As per the man page:
-o, --options
Show timer information. For tcp protocol, the output format is:
timer:(<timer_name>,<expire_time>,<retrans>)
<timer_name>
the name of the timer, there are five kind of timer names:
on: means one of these timers: tcp retrans timer, tcp early retrans timer and tail loss probe timer
keepalive: tcp keep alive timer
timewait: timewait stage timer
persist: zero window probe timer
unknown: none of the above timers
<expire_time>
how long time the timer will expire
<retrans>
how many times the retran occurs
With a little shell loop I can show how that timer is decrementing:
[root@server2 ~]# for i in $(seq 1 5); do ss -nop | grep 13656; sleep 1 ; done
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,20sec,0)
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,19sec,0)
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,18sec,0)
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,17sec,0)
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,16sec,0)
[root@server2 ~]#
Summary
Using the ss utility it is possible to check if a keepalive timer is implemented as a means to support DCD with 12.1 and later releases. Invoking ss(8) hasn’t caused any problems on my system, but as with every such tool you need to ensure it’s safe to use before attempting to look at an important system.
Martins Blog 
You must be logged in to post a comment.