A few weeks ago, I found myself on-site with a client who was facing a perplexing issue: their database connections were frequently timing out. These intermittent problems were causing significant disruptions, and the client was eager for a solution. Here’s the story of how we unraveled this mystery and restored smooth database operations
The Mystery of the Timed-Out Connections
The client's main complaint was that their database connections would sometimes take forever and then fail with a timeout error. Determined to get to the bottom of this, I started by diving into the sqlnet.ora file. This file contained the SQLNET.INBOUND_CONNECT_TIMEOUT parameter, which sets the timeout duration for clients attempting to connect to the database server.
In addition to this parameter, the sqlnet.ora file had the following entry:
NAMES.DIRECTORY_PATH= (TNSNAMES)
Digging Deeper
Next, I took a closer look at the various parameter values. Here’s what I found:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 20
session_max_open_files integer 10
sessions integer 109
shared_server_sessions integer
One parameter immediately caught my eye: sessions. Its value was surprisingly low. Typically, the number of sessions is calculated as:
Sessions = (1.1 * PROCESSES) + 5
With such a low value for "sessions", it was clear this could be a major factor contributing to the connection timeouts.
The Dispatcher Dilemma
But that wasn’t the only issue. Sometimes, shared connections can also cause trouble. If the instance’s dispatcher parameter is set too high, it can actually slow down connection times, sometimes by several minutes. I had encountered a similar issue in a large database previously, and reducing the max_dispatchers parameter had been the key to resolving it.
The Magic of SQLNET.INBOUND_CONNECT_TIMEOUT
To tackle the timeout problem, I focused on optimizing the SQLNET.INBOUND_CONNECT_TIMEOUT parameter. Here’s a bit more about how this parameter works:
It specifies the time (in seconds) allowed for a client to connect to the database server and complete authentication. If the client fails within this time-frame, the server terminates the connection and logs an ORA-12170 error (TNS:Connect timeout occurred). The client might also see errors like ORA-12547 (TNS:lost contact) or ORA-12637 (Packet receive failed).
While the default value of this parameter is generally suitable for most environments, there are times when adjustments are necessary. Oracle recommends starting with both SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listener_name parameters at low values and then incrementing them as needed.
Here’s how you can set these parameters:
Initialize both parameters with low values, ensuring that the INBOUND_CONNECT_TIMEOUT_listener_name is set a bit lower than the SQLNET.INBOUND_CONNECT_TIMEOUT.
For instance:
INBOUND_CONNECT_TIMEOUT_listener_name = 2 SQLNET.INBOUND_CONNECT_TIMEOUT = 3
If your system or network has normal delays, you might need to increase these values incrementally.
The Happy Ending
By carefully adjusting these settings, I managed to resolve the client's connection timeout issues. The database connections became much more reliable, and the client was thrilled with the improvement. This not only restored their confidence in the system but also significantly enhanced their overall productivity.
And that’s how we turned a frustrating series of database timeouts into a smooth, efficient operation. Solving technical problems can sometimes feel like detective work, but with a bit of persistence and the right approach, even the most baffling issues can be resolved.
Have a great day!
Best regards,
Nikhil Kotak
No comments:
Post a Comment