Extending Automatic Performance Diagnostics

This chapter describes custom extension of Oracle AI Database automatic features for performance diagnosing and tuning. This chapter contains the following sections:

Overview of Extensible Automatic Database Diagnostic Monitor (ADDM)

Automatic Database Diagnostic Monitor (ADDM) diagnoses database performance issues by analyzing AWR data and reporting findings, recommendations, actions, and rationales. You can extend (ADDM) with custom performance checks that diagnose performance conditions specific to your applications or operating environment. Extensible ADDM lets you to add custom performance checks to ADDM by adding custom diagnostic rules to the ADDM rule hierarchy. For example, you can create a rule that evaluates database time associated with a specific wait event, wait class, SQL workload, or configuration change. ADDM starts with a broad performance area and then drills down into more specific causes. For example: Application waits > Table lock waits> Row lock contention. A custom rule extends this hierarchy by adding another diagnostic step in this hierarchy. A custom diagnostic rule is a PL/SQL callback that evaluates a performance condition and returns an ADDM rule result. When ADDM analyzes an AWR snapshot range, it calls the callback and includes the returned result in the ADDM report.

The extensible ADDM rule has the following parts:

Benefits of Extensible ADDM

Extensible ADDM helps you apply ADDM’s analysis framework to performance conditions that are specific to your environment.

Usage Notes

Designing a Custom ADDM Rule

Before implementing a custom ADDM rule, define what the custom rule will measure, when it should report a problem, and what ADDM should display.

Item to define What to define Example
Rule subject The performance area that the rule investigates Application wait class
Rule position The parent rule, if the rule should be evaluated as part of a drill-down Child of Application Waits
Performance data The Database statistics gathered from AWR used for custom performance check evaluation. For more information about database statistics, see Gathering Database Statistics Using the Automatic Workload Repository The wait class, wait event, or time-model statistic to measure. Application wait time
Impact definition How ADDM calculates the condition’s database-time cost A wait class, a group of wait events, or parse time minus hard-parse time
Finding A finding is the reportable result. A finding usually consists of: a short finding name, finding message, the measured impact, and recommendation.The rule code needs to construct a finding JSON object (JSON_OBJECT_T) to return it as the result of evaluation. Finding: Application Wait Class, Impact: 2.18 active sessions, 18.48% of total activity, Wait class “Application” was consuming significant database time.
Finding name A short label for the report and its summary "Application" Wait Class
Finding message A detailed explanation when the condition is significant Wait class "Application" was consuming significant database time.
No-problem message What to display when the condition was checked but is insignificant Wait class "Application" was not consuming significant database time.
Missing-data behavior What to report when the required AWR data is unavailable Return a no-data result
Not-applicable behavior What to do when the rule does not apply to the current system Return an ignore result
Significance condition When the impact is large enough to report IS_SIGNIFICANT(impact) returns true
Object The database object to which the finding applies, such as a segment or tablespace.  
Recommendations One or more possible ways to address the problem. This is optional. Each recommendation describes one possible solution. It includes an estimated benefit, one or more actions that the DBA can take, and optional rationales that provide supporting evidence or implementation information. Different recommendations can offer alternative solutions; the DBA does not need to apply every recommendation for the same finding. A finding without recommendations is a symptom; a finding with recommendations is a problem finding. For more information about recommendation, see ADDM Recommendations No

In summary, a custom rule author must define what to measure, how to measure its database-time impact, when the impact is significant, and what ADDM should say for each possible result.

Note: Use dbms_addm_util functions and procedures to define performance data, findings, impact, recommendations the following components of a custom rule. For more information about dbms_addm_util, see (ARPLS topic name once created)

Getting Started with Extensible ADDM

Use the following workflow to create, test, and publish a rule.

1. Create a user with appropriate privileges

Connect as a DBA or SYSDBA to the target container. Create a user for the extension and grant the privileges required to create the callback package and register the rule. The following example creates a common user in the CDB root. Use a local-user name when creating an extension in a PDB.

CREATE USER c##addm_example IDENTIFIED BY <password>;

GRANT CONNECT, CREATE PROCEDURE, ADVISOR TO c##addm_example;

ADVISOR must be granted directly to this user. You might need to grant additional privileges for the logic in the callback. For example, grant access to the relevant AWR views if the rule queries them directly.

2. Create the PL/SQL package

Connect as the rule owner and create a PL/SQL package that declares the callback. The callback must be public, have no arguments, and return a CLOB.

CREATE OR REPLACE PACKAGE my_addm_extension AUTHID CURRENT_USER
IS
  FUNCTION application_wait_rule RETURN CLOB;
END my_addm_extension;
/

3. Implement the diagnostic logic in the PL/SQL package created in step 2

In the callback, use DBMS_ADDM_UTIL to get the data for the current ADDM analysis, determine the database-time impact, and return an ADDM result. This example checks the Application wait class.

CREATE OR REPLACE PACKAGE BODY my_addm_extension
IS
  FUNCTION application_wait_rule RETURN CLOB
  IS
    impact_def JSON_OBJECT_T;
    impact     NUMBER;
  BEGIN
    impact_def := DBMS_ADDM_UTIL.IMPACT_DEF_WAIT_CLASS('Application');
    impact := DBMS_ADDM_UTIL.EVALUATE_IMPACT(impact_def);

    IF DBMS_ADDM_UTIL.IS_SIGNIFICANT(impact) THEN
      RETURN DBMS_ADDM_UTIL.HAS_SYMPTOM(
        impact_definition => impact_def,
        impact            => impact,
        name_msg          => DBMS_ADDM_UTIL.MSG(5001),
        fdg_msg           => DBMS_ADDM_UTIL.MSG(39));
    END IF;

    RETURN DBMS_ADDM_UTIL.NO_PROBLEM(DBMS_ADDM_UTIL.MSG(33), impact);
  END application_wait_rule;
END my_addm_extension;
/

4. Register the rule

Register the callback with DBMS_ADDM.REGISTER_RULE. The following example registers the rule only for tasks owned by the user who registers it.

BEGIN
  DBMS_ADDM.REGISTER_RULE(
    rule_name     => 'Application Wait Extension',
    package_name  => 'MY_ADDM_EXTENSION',
    eval_function => 'APPLICATION_WAIT_RULE',
    description   => 'Evaluates database time spent in Application waits.',
    publisher     => 'Example ADDM Extension',
    enabled       => 'USER');
END;
/

Optionally, use the parents argument to make the rule a child of an existing ADDM rule. Use the applicable argument to limit execution to environments such as CDB root, PDB, Exadata, or a specified database version. You can register a rule at the root of the ADDM rule hierarchy, or make it a child of an existing rule. A child rule is evaluated when its parent identifies a significant condition, enabling the report to drill down from a general problem to a more specific cause.

5. Enable extensions and test the rule

Set the EXTENSIONS_ENABLED parameter for an ADDM task before running it:

EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(
  'my_addm_task', 'EXTENSIONS_ENABLED', 'YES');

Run ADDM for a suitable AWR snapshot range and review the report. A finding from an extension identifies the registered publisher. No-problem and no-data messages do not display a publisher.

To enable extensions by default for subsequently created ADDM tasks, use:

EXEC DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
  'ADDM', 'EXTENSIONS_ENABLED', 'YES');

6. Publish the rule

After testing, a DBA or SYSDBA can enable the rule for all users in the current container:

BEGIN
  DBMS_ADDM.UPDATE_RULE(
    rule_name => 'Application Wait Extension',
    enabled   => 'ALL');
END;
/

Using PL/SQL Packages with Extensible ADDM

The following packages have distinct roles in the extensible ADDM workflow.

Package Use in extensible ADDM
DBMS_ADDM_UTIL Implement rule logic. Use it to obtain the ADDM analysis context and statistics, define and evaluate impact, apply ADDM’s significance test, and construct findings, recommendations, actions, and rationales.
DBMS_ADDM Register, update, enable, and organize extension rules. Use REGISTER_RULE to create the registration and UPDATE_RULE to change rule properties or make a tested rule available to all users.
DBMS_ADVISOR Enable extensions for an ADDM task or set the default for new ADDM tasks. Use SET_TASK_PARAMETER or SET_DEFAULT_TASK_PARAMETER with EXTENSIONS_ENABLED set to YES.

Viewing and Updating Registered Rules

Use DBA_ADDM_RULE_INFO to view all extension rules in the current container. Use USER_ADDM_RULE_INFO to view rules that you own and rules that are publicly available to you. DBA_ADDM_RULE_STATISTICS and USER_ADDM_RULE_STATISTICS list the system statistics and initialization parameters requested by registered rules.

SELECT rule_name, owner_name, package_name, eval_function,
       publisher, enabled, description
FROM   dba_addm_rule_info
ORDER BY rule_name;

Use DBMS_ADDM.UPDATE_RULE to change a registered rule. A rule name cannot be changed. To clear a registered sysstats, parameters, or applicable value, pass an empty string for that argument.

See Also