Wednesday, 27 June 2012

Solution for connection time taking long in oracle

I was at the client side back in november 2011 and repeatingly the same month i got few queries, client was complaining that some times the connection to database take time and give time out error , how i can check this problem in database . so to begin with i looked at the contents of SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora file, in addition to that this is what was there in sqlnet.ora - NAMES.DIRECTORY_PATH= (TNSNAMES).

Getting more details i saw some parameter values and and process parameter is 1500 fore each node :=

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     49
shared_server_sessions               integer

Sometimes what happens is that if you have a shared connection, and the instance has a dispatcher parameter set higher than needed, connection time is affected inversely(sometimes on the order of minutes). I faced such a problem in a big database and it was actually solved by reducing the max_dispatchers parameter. Moreover if you see above in the client's system the value for sessions parameter seems to be very low. Normally its calculated as Sessions = (1.1 * PROCESSES) + 5

Just to explain the above SQLNET.INBOUND_CONNECT_TIMEOUT parameter in details, we use it to specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.

If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection. In addition, the database server logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file. The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.

The default value of this parameter is appropriate for typical usage scenarios. However, if you need to explicitly set a different value, Oracle recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying the values for these parameters, note the following 2 recommendations:

1) Set both parameters to an initial low value.

2) Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and SQLNET.INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed... Have a nice day