DBA Tips for Oracle Freezing!!

Author: Carl Mill

It is very frustrating phase for every database administrator when they find database hanging every time, slow down and then restarting from time to time. A number of issues are responsible for Oracle freezing. It usually involves locking or an administrative activity. Today we will discuss some of these issues so that you can be able to resolve them and increase the performance of your database.

Network Latency:

A number of server side issues are responsible for freezing of Oracle databases. Network latency is also a server issue that can cause Oracle hanging issues.

RAM Swapping Operations :

Oracle database becomes RAM-Bound when it experiences RAM swapping operations.  You can correlate the scan-rate with page-in to determine real server RAM swapping conditions.

Locks and latches :

Locks and latches (library cache lock) are concurrent management overhead that can make the individual tasks of DDL, DML and SQL statements to freeze.

Disk Enqueue:

Disk Enqueue is a condition where read/write requests are waiting for access to the disk. This can also hang your database.

AMM Resize Operations:

The resizing operation for Oracle automatic memory management can cause several performance issues regarding your database system. AMM is not appropriate for shops that have too-little RAM. This is because AMM will attempt to keep up with processing demands by continuously shuffling memory between pools.

Automatic Segment Space Management:

Automatic segment space management is a nice feature that provides simpler and more efficient way of managing space within a segment but it has some overhead associated with it. 

Sizing Redo Log Files:

The size of the redo log files can influence performance as their size largely affects the behavior of the database writer and archiver processes. In general larger redo log files provide better performance whereas undersized log files reduce performance and increase the checkpoint activity.

Redo Log Space Requests:

The redo log space requests indicate that the active log file is full and Oracle is waiting for disk space to be allocated for the redo log entries. Redo log space requests can cause irregular slowdowns in your database operations.

Offloading the Log Buffer:

The offloading of log buffer can also slow down your database irregularly.

Process Failures:

The process failures can cause Oracle freezing while dumps are being collected. Process failures are most common in instances with a large SGA and on large SGA RAC databases where each node must take a dump. You may also encounter process failures in the alert log with messages such as ‘Trace dumping is performing'.

The _diag_daemon is an internal Oracle parameter that can turn-off the dump collection processing thus freeing-up the hanging of the instance. Remember it is an undocumented parameter and should not be used unless you are instructed by Oracle Support. Read Again!!

 More Database Articles
   Database Security: Step by step guideline
   Secrets of Oracle Session History Metrics!!
   The Secrets of Oracle Scalar subqueries!!
   Performance Boost by improving Optimizer Statistics!
   The Power of 11g Snapshot Standby Database for Oracle 10g!!