Oracle RAC Root Blockers Test
One common problem encountered with databases is blocking. Suppose that process A is modifying data that process B wants to use. Process B will be blocked until process A has completed what it is doing. This is only one type of blocking situation; others exist and are common. What matters to a database administrator is identifying when blocking is a problem and how to deal with it effectively. When blocking is bad enough, users will notice slowdowns and complain about it. With a large number of users, it is common for tens or hundreds of processes to be blocked when slowdowns are noticed. Killing these processes may or may not solve the problem because 10 processes may be blocked by process B, while process B itself is blocked by process A. Issuing 10 kill statements for the processes blocked by B probably will not help, as new processes will simply become blocked by B. Killing process B may or may not help, because then the next process that was blocked by B, which is given execution time, may get blocked by process A and become the process that is blocking the other 9 remaining processes. When you have lots of blocking that is not resolving in a reasonable amount of time you need to identify the root blocker, or the process at the top of the tree of blocked processes. Imagine again that you have 10 processes blocked by process B, and process B is blocked by process A. If A is not blocked by anything, but is itself responsible for lots of blocking (B and the 10 processes waiting on B), then A would be the root blocker. (Think of it as a traffic jam. Figure 1 will help) Killing A (via kill) is likely to unblock B, and once B completes, the 10 processes waiting on B are also likely to complete successfully.
The Oracle RAC Root Blockers test reports the number of root blocker processes in the clustered database. The detailed diagnosis of this test, provides the details of each of these blocker processes, thereby enabling you to identify the root blocker.
Figure 1 : The traffic jam analogy representing blocking
Target of the test : Oracle Server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for the Oracle cluster monitored.
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Root blockers: |
Indicates the number of root blocker processes. |
Number |
If this value increases suddenly, this is a cause for concern. Likewise, if a process has been blocking other processes for a long time, it is a reason for further investigation. The detailed diagnosis for this test, if enabled, will indicate which process is blocking which other processes. Killing a blocker process that has been running for a long while may get the database running well again. Also, by carefully observing the details of the blocker processes, you can quickly identify the root blocker, and investigate the reason why it is blocking other processes. |
Max blocking time |
Indicates the maximum time for which a process blocked one/more processes. |
Seconds |
eG Enterprise isolates processes that have been blocking other processes for a duration greater than the configured MAX BLOCKING TIME. The blocking time of these processes is then compared and the maximum blocking time is identified and reported as the value of this measure. If this time is abnormally high, it indicates that a process been blocking resource access to other process(es) for a very long time. Prolonged blocking can significantly degrade database performance. Under such circumstances therefore, you can use the detailed diagnosis of the Blocked sessions measure to know which process was blocked for the maximum time and by which process. |
Blocked sessions |
Indicates the number of sessions that are blocked currently. |
Number |
Ideally, the value of this measure should be low. A consistent increase in this value is a cause for concern. |