The _diag_daemon parameter and Oracle freezing

Question:    My database hangs every so often, everything slows down, and then it re-started.  What are some of the common causes of Oracle freezing?

Answer:  Oracle can lock-up for many reasons, usually locking or an administrative activity:

• Server issues - Server RAM swapping (page-in), disk enqueues, and network latency can cause Oracle hanging issues.
   

• Locks and latches - These can make individual tasks. DDL, DML and SQL statement "freeze".
   

• AMM resize operations - For some databases, AMM resizing can cause performance issues.
   

• Bitmap Freelist management - Automatic segment space management has some overhead.
   

• Redo log switches - Redo log space requests and offloading the log buffer can cause intermittent slowdowns.
   

• Process failures - Oracle Oracle DIAG background process can cause freezing while it writes dump information.

Regarding process failures, Tony Jambu has noted in the IOUG SELECT Journal (volume 15, #2) that process failures can cause Oracle to halt, while dumps are bring collected.  Tony notes the _diag_daemon undocumented parm (only to be used at the direction of Oracle technical support).  Tony notes that:

• Processes failure can hang an instance for minutes

• Process failures and more common on instances with a large SGA

• It's prominent on large SGA RAC databases where each node must take a dump

• Process failures appear in the alert log with messages containing "Trace dumping is performing"

• The undocumented _diag_daemon parameter can turn-off the dump collection processing, freeing-up the hanging of the instance

Here is a script to test for your current value of _diag_daemon:

COLUMN parameter           FORMAT a37
COLUMN description         FORMAT a30 WORD_WRAPPED
COLUMN "Session Value"     FORMAT a10
COLUMN "Instance Value"    FORMAT a10

SELECT 
     a.ksppinm  "Parameter", 
     a.ksppdesc "Description",
     b.ksppstvl "Session Value",
     c.ksppstvl "Instance Value"
FROM
     x$ksppi a,
     x$ksppcv b,
     x$ksppsv c
WHERE
     a.indx = b.indx
     AND a.indx = c.indx
     AND a.ksppinm LIKE '/_diag%' escape '/'
/