Very long SQL connection opening time

Question

Recently, We have migrated our Oracle 9.2.0.6 production database from Windows 32bit RAC (2 nodes) to Linux 64 bit environment. Linux 64 bit environment has 8 times more memory than Windows 32 bit environment and Linux 64 bit has faster disk ( RAID 10 compare to RAID 5 of windows).

However, after this migration, we have noticed sql connection time has been increased significantly. Some time, it is taking 16 seconds to open a connection. We have used different driver (JDBC, ODBC, OCI) and SQLPlus. But, we have noticed no difference in sql connection time. We have investigated network, but noticed no problem with network.

Some time, it is taking around 16 seconds to open a single connection. This database has around 50000 user schema.

Can anybody tell me what can be done to reduce the connection opening time? Your help would be appreciated.

Answer 1

There are many possible reasons for this, and without more information its difficult to determine the root cause.

Having said that, check your sqlnet.ora file located on the machine your connecting from (i.e. <oracle_home>/network/admin/sqlnet.ora) and see if you have a line such as:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

If so, try changing it to:

SQLNET.AUTHENTICATION_SERVICES= (NONE)

Save and retry your connection via SQLPlus.

Also, check the value you have set for NAMES.DIRECTORY_PATH. Make sure the method you're using to connect to your instance is the first in the list. For example, we use TNSNAMES and ours reads:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Hope this helps.

Answer 2

Things that might indicate problem areas:

From the client:

• are all clients exhibiting the problem?
• is ping server reasonably fast?
• is tnsping dbname slow?
• can you connect normally with telnet?

From the server:

• is ping client reasonably fast?
• is nslookup of client by name and IP fast?
• is tnsping dbname slow?
• is sqlplus user@dbname fast or slow?
• is a direct connection on server fast? I.E., export ORACLE_SID=db;sqlplus user/pw