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
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
No comments:
Post a Comment