Fixing slow connection problems in Oracle Oracle Tips by Burleson Consulting

 

Question: We have two database instances on the same server. One was left at 9.2.0.7 and one was upgraded to 10.2.0.3. Connecting externally (sqlplus '/as sysdba') to the 9.2.0.7 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 (9.2.0.7 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 9.2.0.7 database instances. Any theories as to why "_db_block_cache_protect = "true" is problematic on the 10.2.0.3 database and not the 9.2.0.7?

 

Answer: 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 be caused by a variety of issues, and I recommend isolating the connectivity error by trying all possible ways to connect to your instance: 

sqlplus fred/flintstone@the10gdb

sqlplus fred/flintstone@the10gdb

Then see if there are differences over a db link:

sqlplus fred/flintstone@the10gdb
select * from cust@the9idb

sqlplus fred/flintstone@the19idb
select * from cust@the10gdb

As to the use of the undocumented parameter _db_block_cache_protect, you need to contact Oracle Technical Support (MOSC), as Oracle does not support the use of undocumented parameters without their consent.

 

The _db_block_cache_protect parameter is designed to protect database blocks, and should be set to true only when debugging a database RAM memory corruption problem.

 

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 corruption bugs.

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 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.