Very long SQL connection opening time
oracle
Solution
I also experienced very slow connection establishments with SQL Developer 4.1.5.21 on Mac OS against Oracle 12.1.0.2.0.
To analyze this I installed Oracle Instant Client to check connectivity directly with `sqlplus`. When I tried to connect with `sqlplus` it gave me the following error:
$ sqlplus <USER>/<PASSWORD>@<HOST>:<PORT>/<SERVICE>
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 16 10:50:44 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
ERROR:
ORA-21561: OID generation failed
I then found blog entry Oracle ORA-21561 : OID generation failed and added the hostname (as returned by the `hostname` command) to the `127.0.0.1`-line in `/etc/hosts`:
127.0.0.1 localhost MacBook-Pro.local
This fixed the ORA-21561 error in `sqlplus` as well as the slow connection establishment in SQL Developer.
Problem
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.