Oracle Hang Manager

Noun terminology
1.Cross Boundary Hang

Cross border hang. In 12.1.0.1, Hang Manager can detect hang between database and ASM.
2.Deadlock or Closed Chain

Deadlock or close the chain. The only way to break the deadlock chain is to have some of these sessions complete their work or be terminated.
3.Hang or Open Chain

Hang or open chain. From Hang Manager's point of view, hang is that some sessions cannot be processed further, and this is not a deadlock or loop chain. Hang or open chain has a fundamentally blocked person that blocks all other sessions in the chain, and also contains a final wait that is blocked by other sessions.
4.Immediate Waiter

In open chain, the session is blocked by the root session that generated the hang.
5.Quality of Service (QoS) Management

QoS (Service Quality Management) is an automated, policy-based service that monitors the load requests of the entire system in a database. Manage the resources needed for the application, adjust the system configuration, and ensure the performance of the application.
6.self-resolved Hang

Self-handling hang. Hang or deadlock detected by Hang manager that no longer exists.

Hang Manager

When diagnosing a database problem, you often encounter problems with the database/process hang. For hang's problem, generally speaking, the following two kinds of reasons are common:
1. Deadlock (cycle). For this hang, the problem will persist unless the loop is broken.
2. A plug-in (blocker) process has blocked other processes after holding certain resources, and other processes have been unable to obtain resources.
The blocker can be divided into the direct blockage process (immediate blocker) and the root blockage process (root blocker). Root blocker is normally in two states.
(1) The root blockage process is idle, and in this case, terminating the process can solve the problem.
(2) The root blocking process is waiting for some database-independent resources (for example, waiting for I/O), and in this case, terminating the process may solve the problem. However, from the database point of view, this is beyond the scope of the database.

Hang manager was introduced from 10.2.0.1. The main purpose is to detect and handle hang problems. As the version increases, the functionality is constantly being perfected and enhanced. However, actually starting from 11.2.0.2, Hang manager actually starts to solve the hang problem by terminating the underlying session or process that generated the hang.

The Hang manager only takes effect in the RAC database.

By default, Hang Manager does not terminate an instance or remove an instance from a clustered environment, nor does it automatically resolve its detected hang issues. The ASM hang problem cannot be solved at this time.

Starting with 12.1.0.1, if QoS in the RAC cluster is active, Hang Manager uses the additional information provided by QoS to determine whether a hang problem should be ignored or resolved. If QoS favors hang manager to resolve hang issues, hang manager uses less time than usual to detect and handle hang issues, rather than delaying processing.

Before 12.1.0.1, hang will only be detected within the database or ASM. Starting with 12.1.0.1, Hang manager detects the hang generated between the database and ASM.

When Hang manager resolves the hang problem, a ora-32701 event is given in the alert log:

Ora-32701:possible hangs up to hang id=24 detectedincident details in:/ee/oracle/oracle_base/diag/rdbms/orcl/orcl1/ incident/incdir_1944098/orcl1_dia0_34930694_i1944098.trcdia0 terminating blocker (ospid:28311778 sid:3398 ser#: 1) of  Hang with ID = $     Requested by master DIA0 process on instance 2-     Resolution reason:automatic Hang Resolution was performed to free a    critical database process.     By terminating session sid:3398 with serial # 1 (ospid:28311778)

Basic steps for Hang Manager
1. Allocate a portion of the memory space for storing hanganalyze dump information. This part of the memory space exists on each node's DB instance

2. Collect Hanganalyze dump information regularly (local and global)

The HM attribute is specific to the RAC database, and the level of Hanganalyze includes both local and global. In addition, the background process responsible for collecting the dump information is DIA0 (this process was introduced from 11g). Local level Hanganalyze dump is collected every 3 seconds by default, collects global levels every 10 seconds Hanganalyze dump

3. Analyze the dump information collected and confirm the existence of hang in the system

Each instance will have its own DIA0 process, responsible for completing the local hang analysis. However, for a RAC database, many hang cases will contain more than one instance of the process. Therefore, a DIA0 process on an instance is required as master to analyze the information collected by multiple instances. For the 11g version, the DIA0 process of the instance with the smallest node number becomes the master process of HM. Of course, after the instance level has been reconfigured, the primary (master) DIA0 process is re-elected in the existing instance

4. Use the results of the analysis to solve the hang problem.

Hang Manager Information View:

Sql> select * from V$hang_info; Sql> select * from V$hang_session_info;   Sql> select * from Gv$hang_statistics; inst_id statistic# NAME VALUE---------------------------------------------          ------------------------------1 0 Number of deadlocks detected and ignored 0 1                                  1 Number of hangs detected 0 1 2 number of local hangs 0 1 3 Number of global hangs 0 1 4 n           Umber of transient hangs 0 1 5 hangs ignored due to high CPU on root ' node 0 1 6 hangs ignored due to high IO on root ' node 0 1 7 hangs Ignor           Ed due to application contention 0 1 8 hangs ignored due to long running operations 0 1 9 HanGS monitored due to archiving issues 0 1 hangs ignored due to archiving issues  0 1 hangs ignored, blocked by remote database 0 1 hangs ignored         Due to SQL parsing 0 1 hangs ignored due to dumping system State 0  1 hangs ignored, instance termination required 0 1 hangs ignored, only one         Active Instance 0 1 of explicitly resolved hangs 0 1 Self-resolved hangs 0 1 total self-resolved hang time in  Seconds 0 1 Minimum self-resolved hang time in seconds 0 1 20                            Maximum self-resolved hang time in seconds 0 1 number of HSC matched hangs 0 1        Hangs resolved due to instance termination 0 2 0 Number of deadlocks detected and  Ignored 0 2 1 Number of hangs detected 0 2 2                                 Number of local hangs 0 2 3 number of global hangs 0 2 4 Number of transient hangs 0 2 5 hangs IGN            ORed due to high CPU on Root's node 0 2 6 hangs ignored due to high IO on root ' s node 0 2 7 hangs ignored due to application contention 0 2 8 hangs ignored due t         o long running Operations 0 2 9 hangs monitored due to archiving issues 0 2 hangs ignored due to archiving issues 0 2 One hangs ignored, blocked by REM            OTE Database  0 2 hangs ignored due to SQL parsing 0 2 hangs ignored due t         O Dumping System State 0 2 hangs ignored, instance termination required 0  2 hangs ignored, only one active instance 0 2 number of explicitly resolved         Hangs 0 2 number of self-resolved hangs 0 2 Total self-resolved hang time in seconds 0 2 minimum self-resolved-time in Seco NDS 0 2 Maximum self-resolved hang time in seconds 0 2 Numbe             R of HSC matched hangs 0 2 hangs resolved due to instance termination 046 rows selected.  Sql>

Oracle Hang Manager