Hello all,
Asking why to use this parameter? Why this is set in your environment? Or even how it can hep you? Here we go:
As per Oracle documentation, this parameter is used to avoid unused sessions to be kept open in database and locking resources.
It describe in minutes how much time a client/probe can be inactive before be ended.
I recently found an environment where this parameter was too low (1 minute), potentially causing some overhead in communication only for validations. By documentation, if it’s decided to enable it, Oracle recommends value “10”.
This way, after checking and no one be aware about this parameter reason to be there, I just suppressed him from SQLNET.ORA (going back to default “0”, which is equivalent to “disabled”), so we could even reduce the network workload.
However, now we are aware that in case we have abnormal closure of clients, we can have some unused connections opened consuming resources… Not a problem at this point… 🙂
Be ware, if you find this parameter in your SQLNET.ORA: First check with DBA team if there is a reason to be there before disabling it. It can be there solving anything else. 🙂
Also be aware that in case of shared Oracle Home, any sqlnet.ora parameter change can take effect in more than 1 database.
And if you disable it, it’s not needed to restart sessions or services. Once sqlnet.ora is read whenever a client process starts up, all new sessions will take this in count.
And what are the recommended values for that?
# Default: 0
# Minimum Value: 0
# Recommended Value: 10
Take a look in official documentation about:
# SQLNET.EXPIRE_TIME:
(Database Net Services Reference 12c - 5 Parameters for the sqlnet.ora File) Use parameter SQLNET.EXPIRE_TIME To specify a time interval, in minutes, to send a check to verify that client/server connections are active. The following usage notes apply to this parameter: * 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, then it returns an error, causing the server process to exit. * This parameter is primarily 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. - 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 degraded network performance. # Default: 0 # Minimum Value: 0 # Recommended Value: 10
Some excellent links are:
– Database Net Services Reference 12c > 5 Parameters for the sqlnet.ora File > SQLNET.EXPIRE_TIME
– Terminating Oracle connections with connect_time, idle_time, expire_time and inbound_connect_timeout
– sqlnet.expire_time – to restart or not to restart
