Fixing slow connection problems in Oracle
Oracle Database Tips by Donald Burleson
We have two database instances on the same server. One was left
at 22.214.171.124 and one was upgraded to 10.2.0.3. Connecting
externally (sqlplus '/as sysdba') to the 126.96.36.199 database is
lightning fast. Connecting externally to the 10.2.0.3 database
is very slow, comparatively speaking. This is on an IBM AIX-5L
(64-bit) machine. We are using "tnsnames".
The slow sqlplus connection to our recently upgraded database
(188.8.131.52 to 10.2.0.3) has been solved by making one change to
init.ora. Commenting out the server parameter "_db_block_cache_protect
= true" fixed the problem. This parameter does not cause any
slow connect problems with our 184.108.40.206 database instances. Any
theories as to why "_db_block_cache_protect = "true" is
problematic on the 10.2.0.3 database and not the 220.127.116.11?
Please note that connecting via sqlplus " / as sysdba" - is not
connecting externally. If you are doing sqlplus "/ as
sysdba" then you are not using your tnsnames.ora file.
Slow connectivity can also be related to a memory issue, and bouncing the instance will often fix slow connection issues. Also, check the PGA for size issues.
If you are on 11,2 or earlier, see bug number 7715339, which causes slow logins. For a permanent fix, (depending on your release or Oracle),apply patch 7715339 or disable event -->event="28401 trace name context forever, level 1"
Listener issues and slow connections
The listener can sometimes cause slow connections, and you
should try connecting diectly, bypassing the listener.
Bouncing the listener (lsnrctl stop, lsnrctl start) can
sometimes fix a slow connection issue. Also see this MOSC
note titled "Listener startup / connections hang in 11g" [MOSC
External factors and slow connectivity
Also note that a slow initial connction may be due to factors outside of Oracle, things like firewalls or entries in the /etc/resolve.conf file. Also, check these files for DNS related connection latency:
eth0 interface (static):
IP : 192.168.1.101
SM : 255.255.255.0
192.168.1.101 myhost.acme.com myhost (replace myhost with your actual server hostname)
Troubleshooting slow connection problems
connectivity can be caused by a variety of issues, and I
recommend isolating the connectivity error by trying all
possible ways to connect to your instance and then see if there are differences over a db link:
Please read troubleshooting_sql*net_connectivity_errors.
To troubleshoot & diagnose Oracle connectivity problems, I like to start at the simplest, outermost level, and follow this checklist:
Check with "ping" using the IP address ("ping 18.104.22.168")
Check with "ping" using DNS name (e.g. "tnsping uranus")
Try telnet to the IP on port 1521 (telnet 22.214.171.124 1521)
Check with "tnsping" using TNS service name
Invoke SQL*Plus from the OS command line "sqlplus fred@flintstone". If this fails, check to ensure that your listener has the flintstone service defined.
Sign-on to SQL*Plus and connect with the TNS name ("connect fred/flintstome@service_name")
Within SQL*Plus, try a select from table@remote_db_link
This test should isolate the area where the connectivity is slow.
Slow connectivity from _db_block_cache_protect
As to the use of the undocumented
you need to contact Oracle Technical Support (MOSC), as
Oracle does not support the use of undocumented parameters without
parameter is designed to protect database blocks, and should be
set to true only when debugging a database RAM memory corruption
Steve Adams notes what the _db_block_cache_protect parameter does:
It (_db_block_cache_protect) uses the
mprotect() system call to disable access to the block buffers. Every
legitimate access must then enable access for the buffer it needs and
disable it again afterwards.
This protects you against bugs or hackers
corrupting the cache, but it is not really necessary because the log_buffer
has similar protection by default.
It also adds about a 30% overhead to
system performance. It should only be used by Oracle when diagnosing memory
Of course, this parameter does nothing on platforms that do not support
memory protection, or that support process-based memory protection but have
a thread-based Oracle implementation (NT).
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.