Document ID:        13393.1
Subject:            TECH: SQL*Net TCP/IP and Keepalive
Last Modified:      16 Jul 96 
Author:             SDENVERS


SQL*Net TCP/IP and Keepalive
---------------------------

What is Keepalive?
------------------

It is an extension to TCP/IP which enables the closing of dead
connections which are no longer being used.   Usually, only sites
running DOS or Windows PC clients connecting to Unix Oracle databases
find that they have to set up Keepalive.

Problems can occur when the server does not close a connection after a
PC client process has disappeared.  This typically happens when a PC
user switches off or reboots their machine while still connected to
Oracle.  Note that this is not an Oracle problem, but a limitation of
TCP/IP, which has no way of knowing for sure whether a remote connection
has disappeared.

When configured correctly, Keepalive enables dead connections to be
discovered and closed more quickly, thus freeing resources used on the
server more quickly.

Scenario
--------

A user accidentally runs a SQL statement from a PC connected to a Unix
Oracle server, which might take several minutes or even hours to finish
(for example select * from bigtable, hugetable, massivetable).

Realising their error, they decide that they would rather not wait for the
statement to finish.  Instead of interrupting the query, they do as many
PC users do and simply press the reset button, or switch the machine off
and on. The client process has now obviously died, but the background
server process continues to run, thinking the client is still connected.
This results in a lot of wasted processing on the server.

Suppose that the user then reconnects to Oracle and performs another
long-running SQL statement, decides that this is also taking too long
and reboots their machine again, and you can see that the performance of
the server could soon become a problem.

This is an extreme example, but in everyday use you may notice shadow
processes hanging around, consuming resources on your server because your
PC users are not disconnecting from Oracle properly.  Configuring
Keepalive will reduce the length of time the shadow processes hang
around.

Implementing Keepalive
----------------------

When enabled, Keepalive enables periodic polling of all inactive TCP/IP
connections, checking that the client processes really are still there.
After a certain period of inactivity on an established connection, the
server's TCP/IP software will begin to send test packets to the client,
which must be acknowledged.  After a preset number of 'probe' packets
has been ignored by the client, the server assumes the worst and the
connection is closed.  Oracle can then safely remove the shadow process
associated with that connection.

Note that this is not a feature implemented by Oracle, but an extension
to the functionality of TCP/IP provided by your Unix vendor.  Because
this feature is classed as an extension to TCP/IP, not all vendors
include this feature, but almost all do.  Oracle can only make use of
this feature if it is available on your Operating System.  You should
also note that Keepalive has to be implemented at both ends of the
connection - not all DOS TCP/IP vendors include this feature in their
products.

Refer to your Operating System documentation or contact your Unix vendor
for more information on configuring Keepalive, as configuration methods
vary considerably.

SQL*Net 2.1
-----------

As Keepalive is not available on all Unix platforms and all protocols
(although similar features are provided in other protocols), Oracle has
built this capability directly into the SQL*Net product.  This
feature, known as Dead Connection Detection, is included in SQL*Net 2.1.
It is generic to all protocols and platforms, thereby eliminating
any dependency on the OS and protocol being used.

Notes
-----

o Keepalive is enabled by default in SQL*Net TCP/IP 1.2.7.1.2 onwards,
  if the Unix server is capable of supporting it.

o See Internet RFC 1122 section 4.2.3.6 written by the Internet
  Engineering Task Force for a more technical discussion of Keepalive.

Keywords:             TCP;IP;TCPIP;SQL;NET;UNIX;DOS;WINDOWS;KEEPALIVE;
                      SHADOW;DEAD;CONNECTION

-----------------------------------------------------------------------------
                                            Oracle Worldwide Customer Support