|
|
|
sqlnet_expire_time and dead connection detection
Oracle Database Tips by Donald Burleson |
Detecting "dead connections" and disconnecting
Oracle sessions in performed in two places, the PMON process, and
via SQL*Net, by the sqlnet_expire_time
parameter.
Note that there are many caveats to dead
connection detection and session termination, depending on the
nature of the connection (SQL*Net, ODBC, OLE, OCI, &c).
Dead Connection detection in PMON
Oracle utilizes a background process knows as
PMON which is responsible for terminating killed connections.
Usually the answer to "how long" is whenever PMON gets to it. As it
detects aborted processes, it will rollback its transactions,
release locks, latches, and memory, and finally kill the process
itself.
Since PMON also has other tasks that it performs, I have seen this
take a while at times; however, the longest I have seen an aborted
process hold on before PMON got to it was about 10 minutes.
Dead Connection detection with SQL*Net
We also have the sqlnet_expire_time (in your
sqlnet.ora) and the dead connection cleanup utility. You can
specify a timeout interval by placing the sqlnet_expire_time
parameter in your sqlnet.ora file. For example, setting
sqlnet_expire_time=2 will poll the SQL*Net client every two minutes
and disconnect the session if there is no activity.
References on dead connection detection:
-
Oracle MOSC Note:151972.1 - "When the
timer expires, SQL*Net on the server sends a "probe" packet to
the client. 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."
-
MOSC Note:151972.1 - Orphaned resources
are not released if only the client application is terminated.
Only after the client PC has been rebooted does DCD release
these resources. For example, if a Windows application is killed
yet Windows remains running, the probe packet may be received
and discarded as if the connection is still active. As it
currently stands, it appears that DCD detects dead client
machines, but not dead client processes.
|
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|