Oracle 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 Root Blockers test reports the number of root blockers in a database. The detailed diagnosis of this test, provides the details of each of these root blockers. The count and details of sessions blocked by these root blockers are also reported. The maximum time for which the sessions were blocked, and the details of the processes that were blocking them are also revealed.

Figure 1 : The traffic jam analogy representing blocking

Note:

This test will not report metrics for an Oracle 12c CDB server.

Target of the test : An Oracle server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every SID monitored.

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The host for which the test is to be configured
  3. Port - The port on which the server is listening
  4. User – In order to monitor an Oracle database server, a special database user account has to be created in every Oracle database instance that requires monitoring. A Click here hyperlink is available in the test configuration page, using which a new oracle database user can be created. Alternatively, you can manually create the special database user. When doing so, ensure that this user is vested with the select_catalog_role and create session privileges.

    The sample script we recommend for user creation (in Oracle database server versions before 12c) for eG monitoring is:

    create user oraeg identified by oraeg

    create role oratest;

    grant create session to oratest;

    grant select_catalog_role to oratest;

    grant oratest to oraeg;

    The sample script we recommend for user creation (in Oracle database server 12c) for eG monitoring is:

    alter session set container=<Oracle_service_name>;

    create user <user_name>identified by <user_password> container=current default tablespace <name_of_default_tablespace> temporary tablespace <name_of_temporary_tablespace>;

    Grant create session to <user_name>;                                

    Grant select_catalog_role to <user_name>;

    The name of this user has to be specified here.

  5. Password – Password of the specified database user

    This login information is required to query Oracle’s internal dynamic views, so as to fetch the current status / health of the various database components.

  6. Confirm password – Confirm the password by retyping it here.
  7. blocked session count – Specify the minimum number of sessions a process should block for this test to count that process as a root blocker. For instance, if you specify 10 here, it indicates that the Root blockers measure of this test will include only those processes that are blocking 10 or more sessions.
  8. max blocking time secs – If a process is blocked for or beyond the duration (in seconds) specified here, then this test will count that process as a process that has been blocked for a long time. eG Enterprise will then compare the blocked time of all such processes and determine the maximum blocking time. This maximum time value will be reported as the value of the Maximum block time measure. Also, the detailed diagnosis of the Blocked sessions measure will list only those processes that were blocked for a period beyond the MAX BLOCKING TIME SECS configured. For example, if you specify 120 seconds here, then the detailed diagnosis of the Max blocking time measure will display the details of all processes that were blocked for 2 minutes and above.
  9. ISPASSIVE – If the value chosen is yes, then the Oracle server under consideration is a passive server in an Oracle cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
  10. DETAILED DIAGNOSIS – To make diagnosis more efficient and accurate, the eG Enterprise suite embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

    The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

    • The eG manager license should allow the detailed diagnosis capability
    • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Root blockers:

Indicates the number of root blockers.

Number

This measure counts those processes that are blocking the number of processes configured against the BLOCKED SESSIONS parameter, as root blockers.

A non-zero value for this measure is a cause for concern, as it indicates one/more root blockers.

The detailed diagnosis for this test will list the root blockers and indicate

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 processes that are blocked.

Number

A high value of this measure indicates that a large number of processes are not allowed access to resources. Check the detailed diagnosis of this measure to know which processes were blocked for a duration greater than or equal to the MAX BLOCKING TIME configuration, and which processes were blocking them.