DBMS_ADDM_UTIL

The DBMS_ADDM_UTIL package provides utilities for implementing Automatic Database Diagnostic Monitor (ADDM) rules.

The DBMS_ADDM_UTIL package provides utilities for implementing Automatic Database Diagnostic Monitor (ADDM) rules. The package supplies builders for impact definitions, messages, findings, recommendations, actions, and rationales, together with APIs for reading the ADDM analysis context and the statistics used by rule callbacks.

The package is intended for rule evaluation. A rule callback can use the package to calculate impact, inspect the analysis period, construct an ADDM finding, and return the JSON result expected by the ADDM rule engine.

Security Model

The package is declared with AUTHID CURRENT_USER. It runs with the privileges of the invoking user. The package is granted to PUBLIC and has a public synonym.

APIs that expose protected ADDM data invoke DBMS_ADDM_UTIL.CHECK_EXT_PRIV. The check requires the ADVISOR privilege and SELECT_CATALOG_ROLE; it raises an error when either requirement is not met. Calling this procedure explicitly does not initialize the analysis context or grant privileges.

Usage Notes

The engine initializes the utility package for rule evaluation and clears it after analysis. Most constructors, messages, statistics, and template helpers check this state. Use these APIs from a registered rule callback while ADDM is running; there is no public initialization API in this package.

An extension callback returns a CLOB containing a serialized result from NO_DATA, NO_PROBLEM, HAS_FINDING, HAS_FINDINGS, HAS_SYMPTOM, or EVALUATE_RULE, or SQL NULL for IGNORE. Construct the component objects with JSON_OBJECT_T first. GET_SQL_TEXT also returns a CLOB, but its content is SQL text.

About This Reference

This reference describes the public APIs for ADDM rule evaluation, including signatures, parameter modes, defaults, JSON structures, missing-data results, and usage notes.

JSON blocks illustrate object structure, including nested objects and array elements; their sample strings, numeric values, and flags are not defaults or measurements from a running database. The accompanying tables describe types, units, and optional members. Some nested message examples use the supported text-only form {"msg_text": "..."} for readability; MSG, TEXT_MSG, and EVALUATE_MESSAGE document the complete message-group form. SQL NULL, JSON null, an absent member, an empty array, and an object with init: false are distinct outcomes.

A parameter marked “Required” has no default in the declaration and must be supplied. Whether explicit NULL is useful or permitted is described separately. OUT parameters are produced by the API, and IN OUT NOCOPY parameters can be modified in place.

Package Types and Constants

Describes the public types and constants defined by the DBMS_ADDM_UTIL package.

Public types

NUMBER_ARRAY

NUMBER_ARRAY is a VARRAY(1024) of NUMBER. It is used by the INST_IDS member of ANALYSIS_PERIOD.

ANALYSIS_PERIOD

ANALYSIS_PERIOD describes the requested ADDM analysis period.

Member Type Description
DBID NUMBER AWR identifier used to identify the snapshots
SOURCE_DBID NUMBER Source database identifier
BEGIN_SNAP NUMBER Starting snapshot identifier
END_SNAP NUMBER Ending snapshot identifier
INST_ID NUMBER Instance identifier; 0 when more than one instance is analyzed
INST_IDS NUMBER_ARRAY Instances included in the analysis
BEGIN_TIME_UTC DATE Start of the analysis period in UTC
END_TIME_UTC DATE End of the analysis period in UTC
TIME_PERIOD_SECS NUMBER Length of the analysis period in seconds
DB_TIME NUMBER Database time for the analysis period

SYSTEM_METADATA

SYSTEM_METADATA describes the database and deployment context.

Member Type Description
IS_CDB_ROOT BOOLEAN Whether the analysis is for the CDB root
IS_PDB BOOLEAN Whether the analysis is for a PDB
IS_NON_CDB BOOLEAN Whether the database is a non-CDB
INST_COUNT NUMBER Number of instances
IS_AUTO BOOLEAN Whether the database is autonomous
AUTO_TYPE VARCHAR2(10) Autonomous database type.
IS_PRIMARY BOOLEAN Whether the database is primary.
IS_READ_ONLY BOOLEAN Whether the database is read-only.
IS_PHYSICAL_STANDBY BOOLEAN Whether the database is a physical standby
IS_TRUE_CACHE BOOLEAN Whether the database is True Cache
DB_VERSION VARCHAR2(17) Database version
IS_EXADATA BOOLEAN Whether the system is Exadata
IS_ALL_INSTANCES BOOLEAN Whether all instances are analyzed
IS_IMPORTED BOOLEAN Whether the analysis data was imported

ADDM_JSON_TB

ADDM_JSON_TB is an associative array of JSON_OBJECT_T indexed by VARCHAR2(128).

Public constants

The package defines constants for applicability criteria, wait classes, PDB object types, recommendation types, and action commands.

Criteria in the same applicability dimension are combined with OR. Criteria from different dimensions are combined with AND. Version criteria use Version from <version>, Version to <version>, or a range containing both bounds.

Summary of DBMS_ADDM_UTIL Subprograms

Lists the DBMS_ADDM_UTIL subprograms by functional group.

Subprograms

Group Subprograms
Impact definitions IMPACT_DEF_WAIT_EVENT, IMPACT_DEF_WAIT_EVENTS, IMPACT_DEF_WAIT_CLASS, IMPACT_DEF_TIME_MODEL, IMPACT_DEF_TIME_MODELS
Messages MSG, TEXT_MSG, ADD_MSG, ADD_TEXT_MSG, CONCAT_MESSAGES
Rule results IGNORE, NO_DATA, NO_PROBLEM, HAS_FINDING, HAS_FINDINGS, HAS_SYMPTOM
Objects SQL_OBJECT, DATA_OBJECT, BLOCK_OBJECT, FILE_OBJECT, TABLESPACE_OBJECT, LATCH_OBJECT, ENQ_OBJECT
Findings and recommendations NEW_FINDING, ADD_IMPACT, ADD_INFO, SET_OBJECT, ADD_REC, ADD_PDB_MESSAGE, NEW_REC, ADD_ACTION, ADD_ACT_TO_FDG, ADD_RTN, ADD_RTN_TO_FDG
Analysis context GET_ANALYSIS_PERIOD, GET_SYS_METADATA, TASK_PARAMETER_NUMBER, TASK_PARAMETER, IS_APPLICABLE
Statistics STAT_DIFF, ASH_STATS, TIME_STAT, TIME_STAT_VALUE, WAIT_EVENT, WAIT_EVENTS, WAIT_EVENT_FG, WAIT_EVENTS_FG, WAIT_CLASS, WAIT_CLASS_FG, TOP_ENQUEUE_STATS, SYS_STAT, SYS_STAT_VALUE, TOP_LIBRARYCACHE_STATS, TOP_ROWCACHE_STATS, OS_STAT, OS_STAT_VALUE, ORA_PARAMETER, IS_PARAMETER_MODIFIED, ORA_PARAM_END_VALUE, CPU_STATS, GET_EFFEC_NUM_CORES, IOSTAT_DETAIL, IO_STATS, IO_CAPACITY_STATS, RAC_STATS, RAC_MISC_STAT_VALUE, MEMORY_ADVICE, GET_TOP_SQL, GET_SQL_TEXT, PDB_NAME, SERVICE_NAME, DATAFILE_NAME, DB_OBJ_STATS, EXADATA_CELL_IO_STATS, EXADATA_MODEL_IO_STATS, EXADATA_SYSTEM_IO_STATS, EXADATA_GLOBAL_SYS_IO_STATS, EXADATA_CELL_CFG_STATS
Rule helpers and templates PERCENTAGE_OF_DB_TIME, IS_SIGNIFICANT, EVALUATE_IMPACT, TOP_FROM_ASH, ADD_INSTANCE_BREAKDOWN, ADD_TOP_SQL_RTNS, ADD_TOP_OBJ_RTNS, EVALUATE_ARGUMENT, EVALUATE_APPLICABLE_SPEC, EVALUATE_MESSAGE, EVALUATE_TOP_FROM_ASH, EVALUATE_OBJECT, EVALUATE_RATIONALE, EVALUATE_ACTION, EVALUATE_REC, EVALUATE_FINDING, EVALUATE_RULE, CHECK_EXT_PRIV

Impact Definition Functions

An impact definition describes which measured activity belongs to a rule.

An impact definition describes which measured activity belongs to a rule. Constructors record that definition; EVALUATE_IMPACT calculates its cost. A wait definition can combine events and a class, but overlapping entries are summed independently, so avoid specifying an event together with its containing class unless that is intended. A time-model definition supports additive and subtractive counters.

IMPACT_DEF_WAIT_EVENT

Use the DBMS_ADDM_UTIL.IMPACT_DEF_WAIT_EVENT PL/SQL function to describe foreground time attributable to one named wait event.

Purpose

Use this function to construct an impact definition for a rule that measures the foreground time attributable to one named wait event. Pass the result to EVALUATE_IMPACT to measure the problem, and attach the same definition to a finding so ADDM can attribute its impact to instances or PDBs. The constructor records the definition; it does not collect a wait statistic or evaluate significance.

Syntax

DBMS_ADDM_UTIL.IMPACT_DEF_WAIT_EVENT (
    name IN VARCHAR2,
    base_latency IN NUMBER DEFAULT 0)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
name VARCHAR2 Exact wait-event name Yes Identifies the wait event whose foreground time is included in the impact definition. Mode: IN. For example, enq: TX - row lock contention.
base_latency NUMBER Numeric value or NULL No Normal latency per wait, in microseconds, to subtract when impact is evaluated. Mode: IN. Defaults to 0; omitted or explicit NULL is stored as 0.

Returned JSON Structure

The return is a JSON_OBJECT_T.

{
  "wait_events": [
    "enq: TX - row lock contention"
  ],
  "base_latency": 0
}
JSON Member JSON Type Meaning and Availability
wait_events Array of string One-element array containing name.
base_latency Number Baseline microseconds per wait. Omitted or NULL input is normalized to 0.

IMPACT_DEF_WAIT_EVENTS Function

Describes a problem whose impact spans several wait events, such as the local and global-cache forms of buffer-busy waits.

Purpose

Describes a problem whose impact spans several wait events, such as the local and global-cache forms of buffer-busy waits. EVALUATE_IMPACT sums the selected foreground wait times and subtracts the per-wait baseline. Supply NAMES as JSON array text, not a comma-separated list or a JSON_ARRAY_T instance.

Syntax

DBMS_ADDM_UTIL.IMPACT_DEF_WAIT_EVENTS (
    names IN VARCHAR2,
    base_latency IN NUMBER DEFAULT 0)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
NAMES VARCHAR2 Not specified Yes JSON array text of wait-event names, for example'["buffer busy waits","gc buffer busy acquire"]'. Mode: IN.
BASE_LATENCY NUMBER Not specified No Normal latency per wait, in microseconds, to subtract when impact is evaluated. Omitted or explicit NULL is stored as 0. Mode: IN. Default: 0.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "wait_events": [
    "buffer busy waits",
    "gc buffer busy acquire"
  ],
  "base_latency": 0
}
JSON member JSON type Meaning and availability
wait_events array of string Wait-event names parsed from NAMES.
base_latency number Baseline microseconds per wait, default 0.

IMPACT_DEF_WAIT_CLASS Function

Describes all foreground waits in a wait class, providing a broad rule such as Application or Concurrency waits.

Purpose

Describes all foreground waits in a wait class, providing a broad rule such as Application or Concurrency waits. Use the result to evaluate aggregate impact or as the parent definition for more specific rules. This constructor emits only wait_class; it does not add a baseline.

Syntax

DBMS_ADDM_UTIL.IMPACT_DEF_WAIT_CLASS (
    name IN VARCHAR2)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
NAME VARCHAR2 Not specified Yes Wait-class name, for example Application or a WAIT_CLASS_* constant. Mode:IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "wait_class": "Application"
}
JSON member JSON type Meaning and availability
wait_class string Requested wait class.

IMPACT_DEF_TIME_MODEL Function

Describes the elapsed time recorded by one time-model statistic, such as hard parse elapsed time.

Purpose

Describes the elapsed time recorded by one time-model statistic, such as hard parse elapsed time. The resulting definition can be evaluated into microseconds of impact and reused for ASH attribution. It records the statistic name in a one-element array.

Syntax

DBMS_ADDM_UTIL.IMPACT_DEF_TIME_MODEL (
    name IN VARCHAR2)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
NAME VARCHAR2 Not specified Yes Exact time-model statistic name, for example hard parse elapsed time. Mode:IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "time_model": [
    "hard parse elapsed time"
  ]
}
JSON member JSON type Meaning and availability
time_model array of string One-element array containing NAME.

IMPACT_DEF_TIME_MODELS Function

Describes impact as the sum of selected time-model statistics minus a second, optional set.

Purpose

Describes impact as the sum of selected time-model statistics minus a second, optional set. For example, soft-parse impact is parse time elapsed minus hard parse elapsed time. Both arguments contain JSON array text; sub_time_model is omitted when SUB_NAMES is NULL.

Syntax

DBMS_ADDM_UTIL.IMPACT_DEF_TIME_MODELS (
    names IN VARCHAR2,
    sub_names IN VARCHAR2 DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
NAMES VARCHAR2 Not specified Yes JSON array text of time-model names whose deltas are added. Mode:IN.
SUB_NAMES VARCHAR2 Not specified No Optional JSON array text naming time-model statistics to subtract. NULL omitssub_time_model. Mode: IN. Default: NULL.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "time_model": [
    "parse time elapsed"
  ],
  "sub_time_model": [
    "hard parse elapsed time"
  ]
}
JSON member JSON type Meaning and availability
time_model array of string Additive statistic names.
sub_time_model array of string Optional subtractive statistic names; absent ifSUB_NAMES is NULL.

Message Procedures and Functions

Messages carry both structured components and rendered text.

Messages carry both structured components and rendered text. SMG messages support translation through their identifiers; extension text messages retain their supplied language. Use argument placeholders for values whose redaction level must be preserved. Levels are: 0 (none), 1 (user data), 2 (system data), and 3 (both).

MSG Function

Creates a translatable message group containing one message from the SMG message facility. Use it for finding names, explanatory text, actions, and rationales when an existing message identifier is available. Arguments remain separate from the message identifier so the report infrastructure can format, translate, and redact the message.

Syntax

DBMS_ADDM_UTIL.MSG (
    msg_id IN NUMBER,
    p1 IN VARCHAR2 DEFAULT NULL,
    p2 IN VARCHAR2 DEFAULT NULL,
    p3 IN VARCHAR2 DEFAULT NULL,
    p4 IN VARCHAR2 DEFAULT NULL,
    p5 IN VARCHAR2 DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameter Mode Type Default Description
MSG_ID IN NUMBER Required Numeric SMG message identifier, for example 5200; the JSON member uses a formatted string such as SMG-05200.
P1 IN VARCHAR2 NULL Substitution argument 1, represented as text. NULL means no value is supplied for this optional argument.
P2 IN VARCHAR2 NULL Substitution argument 2, represented as text. NULL means no value is supplied for this optional argument.
P3 IN VARCHAR2 NULL Substitution argument 3, represented as text. NULL means no value is supplied for this optional argument.
P4 IN VARCHAR2 NULL Substitution argument 4, represented as text. NULL means no value is supplied for this optional argument.
P5 IN VARCHAR2 NULL Substitution argument 5, represented as text. NULL means no value is supplied for this optional argument.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "msg_group": [
    {
      "msg_id": "SMG-05200",
      "p1": "INSERT",
      "p2": "a2k1zqcbp5nxf"
    }
  ],
  "msg_text": "<rendered SMG message with arguments>"
}
JSON member JSON type Meaning and availability
msg_group array Ordered single-message definitions.
msg_group[].msg_id string Formatted SMG identifier, such as SMG-05200.
msg_group[].p1 string Optional substitution argument 1; omitted when not supplied.
msg_group[].p2 string Optional substitution argument 2; omitted when not supplied.
msg_group[].p3 string Optional substitution argument 3; omitted when not supplied.
msg_group[].p4 string Optional substitution argument 4; omitted when not supplied.
msg_group[].p5 string Optional substitution argument 5; omitted when not supplied.
msg_text string Rendered text of the complete group in the constructing session language.

The group contains one SMG component at construction. Components can contain p1 through p5; unused arguments are omitted.

ADD_MSG Procedure

Appends an SMG message and its arguments to an existing message group.

Purpose

Appends an SMG message and its arguments to an existing message group. Use it to build a composite explanation while retaining each message’s translation metadata. MSG_GROUP is updated in place and follows the same message-group structure as MSG.

Syntax

DBMS_ADDM_UTIL.ADD_MSG (
    msg_group IN OUT NOCOPY JSON_OBJECT_T,
    msg_id IN NUMBER,
    p1 IN VARCHAR2 DEFAULT NULL,
    p2 IN VARCHAR2 DEFAULT NULL,
    p3 IN VARCHAR2 DEFAULT NULL,
    p4 IN VARCHAR2 DEFAULT NULL,
    p5 IN VARCHAR2 DEFAULT NULL);

Parameters

Parameters Type Value Range Required Description Notes
MSG_GROUP JSON_OBJECT_T Not specified Yes Existing message group to update in place. It can contain SMG messages, text messages, or both. Mode: IN OUT NOCOPY.
MSG_ID NUMBER Not specified Yes Numeric SMG message identifier, for example 5200; the JSON member uses a formatted string such as SMG-05200. Mode: IN.
P1 VARCHAR2 Not specified No Substitution argument 1, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
P2 VARCHAR2 Not specified No Substitution argument 2, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
P3 VARCHAR2 Not specified No Substitution argument 3, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
P4 VARCHAR2 Not specified No Substitution argument 4, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
P5 VARCHAR2 Not specified No Substitution argument 5, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.

ADD_TEXT_MSG Procedure

Appends a text-template message to an existing group, retaining the argument and redaction metadata for the new message.

Purpose

Appends a text-template message to an existing group, retaining the argument and redaction metadata for the new message. This is useful when an extension needs to add contextual detail to an existing finding or rationale. MSG_GROUP is updated in place.

Syntax

DBMS_ADDM_UTIL.ADD_TEXT_MSG (
    msg_group IN OUT NOCOPY JSON_OBJECT_T,
    msg_text IN VARCHAR2,
    p1 IN VARCHAR2 DEFAULT NULL,
    r1 IN VARCHAR2 DEFAULT NULL,
    p2 IN VARCHAR2 DEFAULT NULL,
    r2 IN VARCHAR2 DEFAULT NULL,
    p3 IN VARCHAR2 DEFAULT NULL,
    r3 IN VARCHAR2 DEFAULT NULL,
    p4 IN VARCHAR2 DEFAULT NULL,
    r4 IN VARCHAR2 DEFAULT NULL,
    p5 IN VARCHAR2 DEFAULT NULL,
    r5 IN VARCHAR2 DEFAULT NULL);

Parameters

Parameters Type Value Range Required Description Notes
MSG_GROUP JSON_OBJECT_T Not specified Yes Existing message group to update in place. It can contain SMG messages, text messages, or both. Mode: IN OUT NOCOPY.
MSG_TEXT VARCHAR2 Not specified Yes Text template. Use %1 through%5 for substitution arguments; repeated occurrences of a placeholder use the same argument. Mode: IN.
P1 VARCHAR2 Not specified No Substitution argument 1, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
R1 VARCHAR2 Not specified No Redaction level for P1: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL.
P2 VARCHAR2 Not specified No Substitution argument 2, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
R2 VARCHAR2 Not specified No Redaction level for P2: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL.
P3 VARCHAR2 Not specified No Substitution argument 3, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
R3 VARCHAR2 Not specified No Redaction level for P3: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL.
P4 VARCHAR2 Not specified No Substitution argument 4, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
R4 VARCHAR2 Not specified No Redaction level for P4: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL.
P5 VARCHAR2 Not specified No Substitution argument 5, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL.
R5 VARCHAR2 Not specified No Redaction level for P5: 0 orNULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL.

CONCAT_MESSAGES Procedure

Appends the messages from ADDITIONAL_MSG to MSG_GROUP in their existing order.

Purpose

Appends the messages from ADDITIONAL_MSG to MSG_GROUP in their existing order. Use it to combine independently constructed explanations into one composite message; rendering joins message components with newline separators. A NULL additional message leaves the target unchanged.

Syntax

DBMS_ADDM_UTIL.CONCAT_MESSAGES (
    msg_group IN OUT NOCOPY JSON_OBJECT_T,
    additional_msg IN JSON_OBJECT_T);

Parameters

Parameters Type Value Range Required Description Notes
MSG_GROUP JSON_OBJECT_T Not specified Yes Existing message group to update in place. It can contain SMG messages, text messages, or both. Mode: IN OUT NOCOPY.
ADDITIONAL_MSG JSON_OBJECT_T Not specified Yes Message group whose components are appended to MSG_GROUP. NULL leaves the target unchanged. Mode: IN.

MSG Function

Creates a message group from a text template when an extension does not have an SMG message identifier.

Purpose

Creates a message group from a text template when an extension does not have an SMG message identifier. Insert %1 through %5 in the template and pass substitution values separately, pairing sensitive values with their redaction levels. The group can be used wherever an ADDM message object is required.

Syntax

DBMS_ADDM_UTIL.TEXT_MSG (
    msg_text IN VARCHAR2,
    p1 IN VARCHAR2 DEFAULT NULL,
    r1 IN VARCHAR2 DEFAULT NULL,
    p2 IN VARCHAR2 DEFAULT NULL,
    r2 IN VARCHAR2 DEFAULT NULL,
    p3 IN VARCHAR2 DEFAULT NULL,
    r3 IN VARCHAR2 DEFAULT NULL,
    p4 IN VARCHAR2 DEFAULT NULL,
    r4 IN VARCHAR2 DEFAULT NULL,
    p5 IN VARCHAR2 DEFAULT NULL,
    r5 IN VARCHAR2 DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
MSG_TEXT VARCHAR2 Not specified Yes Text template. Use %1 through %5 for substitution arguments; repeated occurrences of a placeholder use the same argument. Mode: IN.
P1 VARCHAR2 Not specified No Substitution argument 1, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL
R1 VARCHAR2 Not specified No Redaction level for P1: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL
P2 VARCHAR2 Not specified No Substitution argument 2, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL
R2 VARCHAR2 Not specified No Redaction level for P2: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL
P3 VARCHAR2 Not specified No Substitution argument 3, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL
R3 VARCHAR2 Not specified No Redaction level for P3: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL
P4 VARCHAR2 Not specified No Substitution argument 4, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL
R4 VARCHAR2 Not specified No Redaction level for P4: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared asVARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL
P5 VARCHAR2 Not specified No Substitution argument 5, represented as text. NULL means no value is supplied for this optional argument. Mode: IN. Default: NULL
R5 VARCHAR2 Not specified No Redaction level for P5: 0 or NULL = not sensitive, 1 = user data, 2 = system data, 3 = both. Declared as VARCHAR2; pass the corresponding numeric text. Mode: IN. Default: NULL

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "msg_group": [
    {
      "msg_text": "Object %1 belongs to %2.",
      "p1": "ORDERS",
      "r1": 1,
      "p2": "APP",
      "r2": 1
    }
  ],
  "msg_text": "Object ORDERS belongs to APP."
}
JSON member JSON type Meaning and availability
msg_group array Ordered text-message definitions
msg_group[].msg_text string Text template containing %1 through %5 placeholders
msg_group[].p1 string Optional substitution argument 1
msg_group[].r1 number Optional redaction level for p1; 0 or NULL means none, 1 (user), 2 (system), 3 (both)
msg_group[].p2 string Optional substitution argument 2
msg_group[].r2 number Optional redaction level for p2; 0 NULL means none, 1 (user), 2 (system), 3 (both)
msg_group[].p3 string Optional substitution argument 3
msg_group[].r3 number Optional redaction level for p3; 0 or NULL means none, 1 (user), 2 (system), 3 (both)
msg_group[].p4 string Optional substitution argument 4.
msg_group[].r4 number Optional redaction level for p4; 0 or NULL means none, 1 (user), 2 (system), 3 (both).
msg_group[].p5 string Optional substitution argument 5
msg_group[].r5 number Optional redaction level for p5; 0 or NULL meansnone, 1 (user), 2 (system), 3 (both)
msg_text string Rendered text of the complete message group

Unused argument/redaction members are optional. Text-message components use the nested msg_text member.

Rule Evaluation Result Functions

A rule callback reports ignore, no data, no problem, or findings.

A rule callback reports ignore, no data, no problem, or findings. These APIs return serialized JSON in a CLOB, except that ignore is SQL NULL. Node cost describes the rule’s total database time; a finding’s impact and a recommendation’s benefit have different scopes and should not be added without considering overlap.

HAS_FINDING Function

Wraps one already constructed finding in the JSON result expected from a rule callback and serializes it to a CLOB.

Purpose

Wraps one already constructed finding in the JSON result expected from a rule callback and serializes it to a CLOB. If NODE_COST is omitted or NULL, the function uses the finding’s impact. Use HAS_FINDINGS when a rule produces several findings whose impacts may overlap.

Syntax

DBMS_ADDM_UTIL.HAS_FINDING (
    finding IN JSON_OBJECT_T,
    node_cost IN NUMBER DEFAULT NULL)
RETURN CLOB;

Parameters

Parameters Type Value Range Required Description Notes
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. Use NEW_FINDING to construct it before calling mutation helpers. Mode: IN.
NODE_COST NUMBER Not specified No Rule cost in microseconds. Omitted or explicit NULL defaults to FINDING.impact. Mode: IN. Default: NULL.

Returned JSON structure

The return is a CLOB containing the following JSON structure.

{
  "findings": [
    {
      "name": {
        "msg_text": "Application waits"
      },
      "message": {
        "msg_text": "Application waits consumed significant database time."
      },
      "impact_definition": {
        "wait_class": "Application"
      },
      "impact": 1000000,
      "type": "SYMPTOM"
    }
  ],
  "node_cost": 1000000
}
JSON member JSON type Meaning and availability
findings array Exactly one supplied finding, using the complete NEW_FINDING structure.
node_cost number Explicit node cost or the finding impact.

The finding can contain every optional member documented under NEW_FINDING. This function delegates to HAS_FINDINGS, including its recommendation-type processing.

HAS_FINDINGS Function

Serializes a collection of findings together with the cost of the rule as a whole.

Purpose

Serializes a collection of findings together with the cost of the rule as a whole. Supply the node cost explicitly: adding the impacts of overlapping findings can count the same database time more than once. The function also processes recommendation-type summary metadata on each finding.

Syntax

DBMS_ADDM_UTIL.HAS_FINDINGS (
    findings IN JSON_ARRAY_T,
    node_cost IN NUMBER)
RETURN CLOB;

Parameters

Parameters Type Value Range Required Description Notes
FINDINGS JSON_ARRAY_T Not specified Yes Array of finding objects. Each finding contains its own impact and may contain recommendations. Mode: IN.
NODE_COST NUMBER Not specified Yes Total database time associated with the rule, in microseconds. It is separate from the sum of finding impacts, which may overlap. Mode: IN.

Returned JSON structure

The return is a CLOB containing the following JSON structure.

{
  "findings": [
    {
      "name": {
        "msg_text": "Application waits"
      },
      "message": {
        "msg_text": "Application waits consumed significant database time."
      },
      "impact_definition": {
        "wait_class": "Application"
      },
      "impact": 1000000,
      "type": "SYMPTOM"
    }
  ],
  "node_cost": 1000000
}
JSON member JSON type Meaning and availability
findings array Supplied findings, each using the NEW_FINDING structure.
node_cost number Explicit rule cost in microseconds.

Each finding can contain the optional NEW_FINDING members. When a finding has recommendations but no rec_types, HAS_FINDINGS adds rec_types: []. This field does not provide a populated recommendation summary.

HAS_SYMPTOM Function

Constructs and serializes a single symptom finding without recommendations.

Purpose

Constructs and serializes a single symptom finding without recommendations. Use it when a rule can identify a significant area of database time but cannot propose a concrete corrective action. The supplied impact is used for both the finding and node cost; this shortcut stores that value directly.

Syntax

DBMS_ADDM_UTIL.HAS_SYMPTOM (
    impact_definition IN JSON_OBJECT_T,
    impact IN NUMBER,
    name_msg IN JSON_OBJECT_T,
    fdg_msg IN JSON_OBJECT_T)
RETURN CLOB;

Parameters

Parameters Type Value Range Required Description Notes
IMPACT_DEFINITION JSON_OBJECT_T Not specified Yes Impact-definition object stored with the finding, used for attribution and instance/PDB breakdown. It may be an approximation when impact is calculated by custom logic. Mode: IN.
IMPACT NUMBER Not specified Yes Measured database time attributable to the diagnosed issue, in microseconds. Mode: IN.
NAME_MSG JSON_OBJECT_T Not specified Yes Short finding-name message group containing a single message without substitution arguments. Mode: IN.
FDG_MSG JSON_OBJECT_T Not specified Yes Detailed finding message group created byMSG, TEXT_MSG, orEVALUATE_MESSAGE. Mode: IN.

Returned JSON structure

The return is a CLOB containing the following JSON structure.

{
  "findings": [
    {
      "name": {
        "msg_text": "Application waits"
      },
      "type": "SYMPTOM",
      "message": {
        "msg_text": "Application waits consumed significant database time."
      },
      "impact_definition": {
        "wait_class": "Application"
      },
      "impact": 1000000
    }
  ],
  "node_cost": 1000000
}
JSON member JSON type Meaning and availability
findings array One symptom finding with name, type, message,impact_definition, and impact.
node_cost number Equal to the supplied IMPACT.

No recommendations member is added. Unlike NEW_FINDING, this function writes IMPACT directly without capping it. Supply an appropriately bounded value.

IGNORE Function

Returns SQL NULL as a CLOB to tell the rule engine to omit this rule’s result.

Purpose

Returns SQL NULL as a CLOB to tell the rule engine to omit this rule’s result. Use this when the rule is inapplicable or there is no useful observation to report. The result is neither an empty JSON object nor the JSON text null.

Syntax

DBMS_ADDM_UTIL.IGNORE
RETURN CLOB;

Parameters

None.

NO_DATA Function

Serializes a warning that the rule could not assess its subject because required data was unavailable.

Purpose

Serializes a warning that the rule could not assess its subject because required data was unavailable. The warning explains the missing information and can appear in the ADDM report’s warnings. This result has no impact or node cost because the missing data prevented that assessment.

Syntax

DBMS_ADDM_UTIL.NO_DATA (
    msg IN JSON_OBJECT_T)
RETURN CLOB;

Parameters

Parameters Type Value Range Required Description Notes
MSG JSON_OBJECT_T Not specified Yes Message group explaining why no data or no significant problem was reported. Mode: IN.

Returned JSON structure

The return is a CLOB containing the following JSON structure.

{
  "no_data": {
    "msg_text": "Required statistics are unavailable."
  }
}
JSON member JSON type Meaning and availability
no_data object Explanation message group; see MSG, TEXT_MSG.

NO_PROBLEM Function

Serializes the conclusion that the rule assessed its subject and did not find a significant problem.

Purpose

Serializes the conclusion that the rule assessed its subject and did not find a significant problem. Include the measured impact when available so the engine retains the rule’s cost even though there is no finding. The function does not perform a significance check; call IS_SIGNIFICANT when making that decision.

Syntax

DBMS_ADDM_UTIL.NO_PROBLEM (
    msg IN JSON_OBJECT_T,
    impact IN NUMBER DEFAULT 0)
RETURN CLOB;

Parameters

Parameters Type Value Range Required Description Notes
MSG JSON_OBJECT_T Not specified Yes Message group explaining why no data or no significant problem was reported. Mode: IN.
IMPACT NUMBER Not specified No Measured database time attributable to the diagnosed issue, in microseconds. Mode: IN. Default: 0.

Returned JSON structure

The return is a CLOB containing the following JSON structure.

{
  "no_problem": {
    "msg_text": "The measured activity was not significant."
  },
  "impact": 0
}
JSON member JSON type Meaning and availability
no_problem object Explanation message group.
impact number Supplied impact in microseconds, default 0.

Object Construction Functions

Object descriptors identify related database entities for report consumers.

Object descriptors identify related database entities for report consumers. They are not database lookups. Numeric attributes are JSON numbers. Container identifiers are CON_DBID values, not recyclable CON_ID values.

BLOCK_OBJECT Function

Identifies a specific database block using its object, file, and block numbers.

Purpose

Identifies a specific database block using its object, file, and block numbers. Use it when a finding or recommendation concerns a hot block rather than an entire segment. Include the container DBID when file and object identifiers need to be distinguished across PDBs.

Syntax

DBMS_ADDM_UTIL.BLOCK_OBJECT (
    obj_id IN NUMBER,
    file_number IN NUMBER,
    block_number IN NUMBER,
    con_dbid IN NUMBER DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
OBJ_ID NUMBER Not specified Yes Dictionary object number identifying the database segment. Mode: IN.
FILE_NUMBER NUMBER Not specified Yes File number containing the affected block. Mode: IN.
BLOCK_NUMBER NUMBER Not specified Yes Block number within FILE_NUMBER, identifying the affected database block. Mode: IN.
CON_DBID NUMBER Not specified No Container database identifier (not CON_ID). In object constructors, NULL omits the con_dbid member without resolving a default. Mode: IN. Default: NULL.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "type": "DATABASE BLOCK",
  "object_id": 12345,
  "file_number": 7,
  "block_number": 100,
  "con_dbid": 54321
}
JSON member JSON type Meaning and availability
type string DATABASE BLOCK.
object_id number Dictionary object number.
file_number number File containing the block.
block_number number Block number within the file.
con_dbid number Optional; omitted when CON_DBID is NULL.

DATA_OBJECT Function

Identifies a database segment by dictionary object number and, optionally, container DBID.

Purpose

Identifies a database segment by dictionary object number and, optionally, container DBID. Attach this object to a finding or pass it to DB_OBJ_STATS to resolve the object’s owner, name, type, and tablespace. The constructor does not itself look up that metadata.

Syntax

DBMS_ADDM_UTIL.DATA_OBJECT (
    obj_id IN NUMBER,
    con_dbid IN NUMBER DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
OBJ_ID NUMBER Not specified Yes Dictionary object number identifying the database segment. Mode: IN.
CON_DBID NUMBER Not specified No Container database identifier (notCON_ID). In object constructors, NULL omits the con_dbid member without resolving a default. Mode: IN. Default: NULL.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "type": "DATABASE OBJECT",
  "object_id": 12345,
  "con_dbid": 54321
}
JSON member JSON type Meaning and availability
type string DATABASE OBJECT.
object_id number Dictionary object number.
con_dbid number Optional; omitted when CON_DBID is NULL.

ENQ_OBJECT Function

Identifies an enqueue using its two-letter type code and resource identifiers.

Purpose

Identifies an enqueue using its two-letter type code and resource identifiers. For enqueue wait events, the type can be derived from the event name and the two identifiers commonly come from ASH P2 and P3. Explicitly passing NULL for an identifier omits that member from the JSON object.

Syntax

DBMS_ADDM_UTIL.ENQ_OBJECT (
    code IN VARCHAR2,
    id1 IN NUMBER,
    id2 IN NUMBER)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
CODE VARCHAR2 Not specified Yes Two-letter enqueue type, for example TX or TM. Mode: IN.
ID1 NUMBER Not specified Yes First enqueue resource identifier, commonly ASH P2. Supply explicitly; NULL omitsid1. Mode: IN.
ID2 NUMBER Not specified Yes Second enqueue resource identifier, commonly ASH P3. Supply explicitly; NULL omits id2. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "type": "DATABASE ENQ",
  "code": "TX",
  "id1": 123,
  "id2": 456
}
JSON member JSON type Meaning and availability
type string DATABASE ENQ.
code string Two-letter enqueue code.
id1 number Optional first resource ID, omitted when NULL.
id2 number Optional second resource ID, omitted when NULL.

FILE_OBJECT Function

Identifies a database datafile or tempfile by name for use in an ADDM report construct.

Purpose

Identifies a database datafile or tempfile by name for use in an ADDM report construct. Resolve a numeric file identifier with DATAFILE_NAME before constructing this object when the name is not already known. A container DBID can disambiguate files in a root-level analysis.

Syntax

DBMS_ADDM_UTIL.FILE_OBJECT (
    file_name IN VARCHAR2,
    con_dbid IN NUMBER DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
FILE_NAME VARCHAR2 Not specified Yes Datafile or tempfile name to store in the object. Mode: IN.
CON_DBID NUMBER Not specified No Container database identifier (not CON_ID). In object constructors, NULL omits the con_dbid member without resolving a default. Mode: IN. Default: NULL.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "type": "DATABASE FILE",
  "file_name": "<datafile name>",
  "con_dbid": 54321
}
JSON member JSON type Meaning and availability
type string DATABASE FILE.
file_name string Datafile or tempfile name.
con_dbid number Optional; omitted when CON_DBID is NULL.

LATCH_OBJECT Function

Constructs the ADDM representation of a database latch.

Purpose

Constructs the ADDM representation of a database latch. The NAME parameter accepts a NUMBER, which is stored as a JSON number under name.

Syntax

DBMS_ADDM_UTIL.LATCH_OBJECT (
    name IN NUMBER)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
NAME NUMBER Not specified Yes Numeric latch value. Stored as a JSON number under name. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "type": "DATABASE LATCH",
  "name": 123
}
JSON member JSON type Meaning and availability
type string DATABASE LATCH
name number Numeric value supplied to NAME

SQL_OBJECT Function

Identifies a SQL statement for attachment to a finding, action, or rationale.

Purpose

Identifies a SQL statement for attachment to a finding, action, or rationale. Supply the plan and SQL text when available to give report consumers enough context to inspect or tune the statement. This constructor records the supplied attributes; it does not fetch SQL text or validate that the statement is in the top-SQL cache.

Syntax

DBMS_ADDM_UTIL.SQL_OBJECT (
    sql_id IN VARCHAR2,
    plan_hash IN NUMBER,
    sql_text IN CLOB,
    con_dbid IN NUMBER DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
SQL_ID VARCHAR2 Not specified Yes SQL statement identifier. GET_TOP_SQL and GET_SQL_TEXT require exactly 13 characters when non-NULL. Mode: IN.
PLAN_HASH NUMBER Not specified Yes Plan hash value. This argument has no default, but explicit NULL is permitted and omits plan_hash. Mode: IN.
SQL_TEXT CLOB Not specified Yes SQL statement text as a CLOB. This argument has no default; explicit NULL omitssql_text. Mode: IN.
CON_DBID NUMBER Not specified No Container database identifier (notCON_ID). In object constructors, NULL omits the con_dbid member without resolving a default. Mode: IN. Default: NULL.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "type": "SQL",
  "sql_id": "a2k1zqcbp5nxf",
  "plan_hash": 123456789,
  "sql_text": "select * from orders",
  "con_dbid": 12345
}
JSON member JSON type Meaning and availability
type string SQL
sql_id string SQL statement ID
plan_hash number Optional; omitted if PLAN_HASH is NULL
sql_text string Optional SQL text, omitted when NULL. The CLOB becomes a JSON string
con_dbid number Optional container DBID, omitted when NULL

TABLESPACE_OBJECT Function

Identifies a tablespace by its identifier and name.

Purpose

Identifies a tablespace by its identifier and name. Attach it to findings or recommendations about tablespace configuration or activity. The constructor stores the supplied name without querying the dictionary.

Syntax

DBMS_ADDM_UTIL.TABLESPACE_OBJECT (
    tablespace_id IN NUMBER,
    name IN VARCHAR2,
    con_dbid IN NUMBER DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
TABLESPACE_ID NUMBER Not specified Yes Numeric tablespace identifier Mode: IN.
NAME VARCHAR2 Not specified Yes Tablespace name to store with TABLESPACE_ID Mode: IN.
CON_DBID NUMBER Not specified No Container database identifier (not CON_ID). In object constructors, NULL omits the con_dbid member without resolving a default. Mode: IN. Default: NULL

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "type": "TABLESPACE",
  "tablespace_id": 5,
  "name": "USERS",
  "con_dbid": 54321
}
JSON member JSON type Meaning and availability
type string TABLESPACE
tablespace_id number Tablespace identifier
name string Tablespace name
con_dbid number Optional; omitted when CON_DBID is NULL

Finding and Recommendations Procedures and Functions

A finding explains the diagnosed issue; recommendations describe possible remedies.

A finding explains the diagnosed issue; recommendations describe possible remedies. Each recommendation contains actions to implement and optional rationales explaining the evidence. Actions and rationales retain insertion order. The engine later adds report IDs, calculated percentages, hierarchy, and other report metadata.

ADD_IMPACT Procedure

Adds database time to an existing finding’s impact and caps the new total at the analysis period’s database time.

Purpose

Adds database time to an existing finding’s impact and caps the new total at the analysis period’s database time. Use it when building a finding incrementally from several diagnosed components. The function does not change the finding’s impact definition or evaluate whether the components overlap.

Syntax

DBMS_ADDM_UTIL.ADD_IMPACT (
    finding IN OUT NOCOPY JSON_OBJECT_T,
    impact IN NUMBER);

Parameters

Parameters Type Value Range Required Description Notes
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. Use NEW_FINDING to construct it before calling mutation helpers. Mode: IN OUT NOCOPY.
IMPACT NUMBER Not specified Yes Additional database time in microseconds to add to the existing impact. The result is capped at total analysis DB time. Mode: IN.

ADD_INFO Procedure

Adds a supporting explanation to a finding’s more_info message group.

Purpose

Adds a supporting explanation to a finding’s more_info message group. If the member already exists, the procedure appends the new message to that group, preserving the existing explanation. Passing NULL leaves the finding unchanged.

Syntax

DBMS_ADDM_UTIL.ADD_INFO (
    finding IN OUT NOCOPY JSON_OBJECT_T,
    more_info IN JSON_OBJECT_T);

Parameters

Parameters Type Value Range Required Description Notes
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. Use NEW_FINDING to construct it before calling mutation helpers. Mode: IN OUT NOCOPY.
MORE_INFO JSON_OBJECT_T Not specified Yes Additional message group explaining the finding. NULL omits it at construction and is ignored by ADD_INFO. Mode: IN.

ADD_REC Procedure

Appends an applicable recommendation to the finding’s recommendations array and changes a symptom to a problem when the array is first created.

Purpose

Appends an applicable recommendation to the finding’s recommendations array and changes a symptom to a problem when the array is first created. A NULL recommendation benefit or one larger than the finding’s impact is reduced to the finding’s impact. If applicability fails, the finding is left unchanged.

Syntax

DBMS_ADDM_UTIL.ADD_REC (
    finding IN OUT NOCOPY JSON_OBJECT_T,
    rec IN JSON_OBJECT_T,
    rec_applicable IN JSON_ARRAY_T DEFAULT NULL);

Parameters

Parameters Type Value Range Required Description Notes
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. Use NEW_FINDING to construct it before calling mutation helpers. Mode: IN OUT NOCOPY.
REC JSON_OBJECT_T Not specified Yes Recommendation object. At finding construction this is an optional initial recommendation; mutation helpers require an existing recommendation. Mode: IN.
REC_APPLICABLE JSON_ARRAY_T Not specified No Optional applicability criteria for the recommendation being added. NULL means no applicability filter. Mode: IN. Default: NULL.

ADD_PDB_MESSAGE Procedure

Adds a sentence identifying the container to which a SQL statement, object, session, or other entity belongs.

Purpose

Adds a sentence identifying the container to which a SQL statement, object, session, or other entity belongs. The procedure selects a root-container, container-ID, or container-name message according to the available metadata. This gives a DBA container context without requiring each extension to construct the wording.

Syntax

DBMS_ADDM_UTIL.ADD_PDB_MESSAGE (
    msg_group IN OUT NOCOPY JSON_OBJECT_T,
    obj_type IN VARCHAR2,
    con_dbid IN NUMBER);

Parameters

Parameters Type Value Range Required Description Notes
MSG_GROUP JSON_OBJECT_T Not specified Yes Existing message group to update in place. It can contain SMG messages, text messages, or both. Mode: IN OUT NOCOPY.
OBJ_TYPE VARCHAR2 Not specified Yes One of PDB_OBJ_SQL, PDB_OBJ_DB_OBJ, PDB_OBJ_TABLESPACE, PDB_OBJ_BLOCK, PDB_OBJ_SESSION, PDB_OBJ_MODULE, PDB_OBJ_SERVICE, or PDB_OBJ_LOCK. Mode: IN.
CON_DBID NUMBER Not specified Yes DBID of the container to identify. The helper adds no message for non-CDB/PDB analysis; it qualifies container-specific activity during root analysis. Mode: IN.

ADD_ACTION Procedure

Appends an applicable action to a recommendation’s existing actions array.

Purpose

Appends an applicable action to a recommendation’s existing actions array. Actions describe the steps needed to implement that recommendation; they are not alternative recommendations with independently additive benefits. Optional command metadata identifies an advisor or configuration workflow but does not execute it.

Syntax

DBMS_ADDM_UTIL.ADD_ACTION (
    rec IN OUT NOCOPY JSON_OBJECT_T,
    act_message IN JSON_OBJECT_T,
    act_command IN VARCHAR2 DEFAULT NULL,
    act_object IN JSON_OBJECT_T DEFAULT NULL,
    param_name IN VARCHAR2 DEFAULT NULL,
    from_param_value IN VARCHAR2 DEFAULT NULL,
    to_param_value IN VARCHAR2 DEFAULT NULL,
    act_applicable IN JSON_ARRAY_T DEFAULT NULL);

Parameters

Parameters Type Value Range Required Description Notes
REC JSON_OBJECT_T Not specified Yes Recommendation object. At finding construction this is an optional initial recommendation; mutation helpers require an existing recommendation. Mode: IN OUT NOCOPY.
ACT_MESSAGE JSON_OBJECT_T Not specified Yes Message group containing instructions for the DBA to implement this action. Mode: IN.
ACT_COMMAND VARCHAR2 Not specified No Optional COMMAND_SQL, COMMAND_SEGMENT, COMMAND_PARAMETER, orCOMMAND_INSTANCE navigation hint. SQL/segment commands require usable object metadata; no operation is executed. Mode: IN. Default: NULL.
ACT_OBJECT JSON_OBJECT_T Not specified No Optional related-object descriptor. SQL tuning uses SQL_OBJECT. Segment tuning uses DATA_OBJECT. Mode: IN. Default: NULL.
PARAM_NAME VARCHAR2 Not specified No Initialization-parameter name required to attach an ALTER PARAMETER command. If NULL, parameter command metadata is not generated. Mode: IN. Default: NULL.
FROM_PARAM_VALUE VARCHAR2 Not specified No Existing parameter value as text for anALTER PARAMETERaction. Optionally; a numeric navigation attribute is also added if conversion succeeds. Mode: IN. Default: NULL.
TO_PARAM_VALUE VARCHAR2 Not specified No Recommended new parameter value as text for ALTER PARAMETER metadata. A numeric navigation attribute is added if conversion succeeds. Mode: IN. Default: NULL.
ACT_APPLICABLE JSON_ARRAY_T Not specified No Optional applicability criteria for the action. If criteria do not match, nothing is appended. Mode: IN. Default: NULL.

The appended object has the action structure and optional navigation fields described under NEW_REC Function . The procedure appends it to rec.actions.

ADD_ACT_TO_FDG Procedure

Appends an action to the last recommendation in a finding, which is convenient while constructing recommendations in sequence.

Purpose

Appends an action to the last recommendation in a finding, which is convenient while constructing recommendations in sequence. The call does nothing when applicability fails or the finding has no recommendations member. Ensure an existing array contains a recommendation before calling this helper; use ADD_ACTION when parameter-change metadata is needed.

Syntax

DBMS_ADDM_UTIL.ADD_ACT_TO_FDG (
    finding IN OUT NOCOPY JSON_OBJECT_T,
    act_message IN JSON_OBJECT_T,
    act_command IN VARCHAR2 DEFAULT NULL,
    act_object IN JSON_OBJECT_T DEFAULT NULL,
    act_applicable IN JSON_ARRAY_T DEFAULT NULL);

Parameters

Parameters Type Value Range Required Description Notes
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. UseNEW_FINDING to construct it before calling mutation helpers. Mode: IN OUT NOCOPY.
ACT_MESSAGE JSON_OBJECT_T Not specified Yes Message group containing instructions for the DBA to implement this action. Mode: IN.
ACT_COMMAND VARCHAR2 Not specified No Optional COMMAND_SQL, COMMAND_SEGMENT, COMMAND_PARAMETER, or COMMAND_INSTANCE navigation hint. SQL/segment commands require usable object metadata; no operation is executed. Mode: IN. Default: NULL.
ACT_OBJECT JSON_OBJECT_T Not specified No Optional related-object descriptor. SQL tuning uses SQL_OBJECT. Segment tuning uses DATA_OBJECT. Mode: IN. Default: NULL.
ACT_APPLICABLE JSON_ARRAY_T Not specified No Optional applicability criteria for the action. If criteria do not match, nothing is appended. Mode: IN. Default: NULL.

The appended object has the action structure described under NEW_REC Function, and is added to the last recommendation’s actions array.

ADD_RTN Procedure

Appends an applicable rationale explaining why a recommendation is relevant.

Purpose

Appends an applicable rationale explaining why a recommendation is relevant. A rationale can identify supporting SQL or an object as well as carrying a message; it does not add another action or change the benefit. The procedure creates the rationales array on first use.

Syntax

DBMS_ADDM_UTIL.ADD_RTN (
    rec IN OUT NOCOPY JSON_OBJECT_T,
    rtn_message IN JSON_OBJECT_T,
    rtn_object IN JSON_OBJECT_T DEFAULT NULL,
    rtn_applicable IN JSON_ARRAY_T DEFAULT NULL);

Parameters

Parameters Type Value Range Required Description Notes
REC JSON_OBJECT_T Not specified Yes Recommendation object. At finding construction this is an optional initial recommendation; mutation helpers require an existing recommendation. Mode: IN OUT NOCOPY.
RTN_MESSAGE JSON_OBJECT_T Not specified Yes Message group explaining the evidence or reason for the recommendation. Mode: IN.
RTN_OBJECT JSON_OBJECT_T Not specified No Optional related-object descriptor providing structured evidence for the rationale. Mode: IN. Default: NULL.
RTN_APPLICABLE JSON_ARRAY_T Not specified No Optional applicability criteria for the rationale. If criteria do not match, nothing is appended. Mode: IN. Default: NULL.

The appended rationale has the following structure. object is optional, and message uses the MSG/TEXT_MSG structure:

{
  "message": {"msg_text": "<supporting explanation>"},
  "object": {"type": "DATABASE OBJECT", "object_id": 12345}
}

ADD_RTN_TO_FDG Procedure

Appends a rationale to the last recommendation in a finding.

Purpose

Appends a rationale to the last recommendation in a finding. Use it immediately after creating the recommendation to which the evidence belongs. The call does nothing when applicability fails or the finding has no recommendations member; an existing array must contain a recommendation.

Syntax

DBMS_ADDM_UTIL.ADD_RTN_TO_FDG (
    finding IN OUT NOCOPY JSON_OBJECT_T,
    rtn_message IN JSON_OBJECT_T,
    rtn_object IN JSON_OBJECT_T DEFAULT NULL,
    rtn_applicable IN JSON_ARRAY_T DEFAULT NULL);

Parameters

Parameters Type Value Range Required Description Notes
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. Use NEW_FINDING to construct it before calling mutation helpers. Mode: IN OUT NOCOPY.
RTN_MESSAGE JSON_OBJECT_T Not specified Yes Message group explaining the evidence or reason for the recommendation. Mode: IN.
RTN_OBJECT JSON_OBJECT_T Not specified No Optional related-object descriptor providing structured evidence for the rationale. Mode: IN. Default: NULL.
RTN_APPLICABLE JSON_ARRAY_T Not specified No Optional applicability criteria for the rationale. If criteria do not match, nothing is appended. Mode: IN. Default: NULL.

The appended rationale has the same message and optional object structure as ADD_RTN Procedure .

NEW_FINDING Function

Creates a finding with a short subject name, detailed message, impact definition, and measured impact.

Purpose

Creates a finding with a short subject name, detailed message, impact definition, and measured impact. The function caps impact at the analysis period’s total database time. Optional supporting information, a related object, and an initial recommendation can be attached; additional recommendations can be added later with ADD_REC.

Syntax

DBMS_ADDM_UTIL.NEW_FINDING (
    fdg_name IN JSON_OBJECT_T,
    fdg_msg IN JSON_OBJECT_T,
    impact_definition IN JSON_OBJECT_T,
    impact IN NUMBER,
    more_info IN JSON_OBJECT_T DEFAULT NULL,
    obj IN JSON_OBJECT_T DEFAULT NULL,
    rec IN JSON_OBJECT_T DEFAULT NULL,
    rec_applicable IN JSON_ARRAY_T DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
FDG_NAME JSON_OBJECT_T Not specified Yes Short finding-name message group containing one message with no substitution arguments. Mode: IN.
FDG_MSG JSON_OBJECT_T Not specified Yes Detailed finding message group created byMSG, TEXT_MSG, or EVALUATE_MESSAGE. Mode: IN.
IMPACT_DEFINITION JSON_OBJECT_T Not specified Yes Impact-definition object stored with the finding, used for attribution and instance/PDB breakdown. It may be an approximation when impact is calculated by custom logic. Mode: IN.
IMPACT NUMBER Not specified Yes Measured database time attributable to the diagnosed issue, in microseconds. Mode: IN.
MORE_INFO JSON_OBJECT_T Not specified No Additional message group explaining the finding. NULL omits it at construction and is ignored by ADD_INFO. Mode: IN. Default: NULL.
OBJ JSON_OBJECT_T Not specified No Related-object descriptor from an object constructor. Optional during finding construction; SET_OBJECT replaces the current descriptor. Mode: IN. Default: NULL.
REC JSON_OBJECT_T Not specified No Recommendation object. At finding construction this is an optional initial recommendation; mutation helpers require an existing recommendation. Mode: IN. Default: NULL.
REC_APPLICABLE JSON_ARRAY_T Not specified No Optional applicability criteria for the recommendation being added. NULL means no applicability filter. Mode: IN. Default: NULL.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "name": {
    "msg_text": "High parse activity"
  },
  "message": {
    "msg_text": "Parsing consumed significant database time."
  },
  "impact_definition": {
    "time_model": [
      "parse time elapsed"
    ]
  },
  "impact": 1000000,
  "type": "PROBLEM",
  "more_info": {
    "msg_text": "<optional supporting explanation>"
  },
  "object": {
    "type": "SQL",
    "sql_id": "a2k1zqcbp5nxf"
  },
  "recommendations": [
    {
      "benefit": 1000000,
      "type": "Application Analysis",
      "type_message": {
        "msg_text": "Application Analysis"
      },
      "rank": 1,
      "actions": [
        {
          "message": {
            "msg_text": "Review SQL parsing and cursor sharing."
          }
        }
      ]
    }
  ]
}
JSON member JSON type Meaning and availability
name object Short subject message, single component without arguments.
message object Detailed finding message group.
impact_definition object Wait/time-model definition used for attribution.
impact number Finding impact in DB-time microseconds.
type string SYMPTOM for a basic finding; PROBLEM when recommendations are added.
more_info object Optional additional message group.
object object Optional related-object descriptor.
recommendations array Optional recommendation array. Each item has the full NEW_REC structure; ADD_RTN can add rationales.

The example includes optional arguments. Without them, more_info, object, and recommendations are absent and type is SYMPTOM. Recommendations have the structure shown under NEW_REC, with optional rationales added later. The constructor sets type to PROBLEM before filtering a non-null initial REC; if REC_APPLICABLE rejects that recommendation, the type can remain PROBLEM without a recommendations array. Report-only IDs, percentages, filtering flags, and hierarchy metadata are supplied later by the engine.

NEW_REC Function

Creates a recommendation with an estimated benefit, solution category, rank, and an initial action.

Purpose

Creates a recommendation with an estimated benefit, solution category, rank, and an initial action. The action explains what the DBA should do and may carry object and navigation metadata for a follow-on advisor or parameter page. Passing NULL for ACT_MESSAGE creates an empty actions array so actions can be added later; a usable final recommendation should contain an action.

Syntax

DBMS_ADDM_UTIL.NEW_REC (
    benefit IN NUMBER,
    rec_type IN VARCHAR2,
    act_message IN JSON_OBJECT_T,
    act_command IN VARCHAR2 DEFAULT NULL,
    act_object IN JSON_OBJECT_T DEFAULT NULL,
    param_name IN VARCHAR2 DEFAULT NULL,
    from_param_value IN VARCHAR2 DEFAULT NULL,
    to_param_value IN VARCHAR2 DEFAULT NULL,
    rank_value IN NUMBER DEFAULT 1)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
BENEFIT NUMBER Not specified Yes Estimated database time saved by implementing the recommendation, in microseconds. ADD_REC limits it to the finding’s impact. Mode: IN.
REC_TYPE VARCHAR2 Not specified Yes Solution category. Use one of the REC_TYPE_* constants; the constructor stores up to 30 characters and supplies the corresponding type_message. Mode: IN.
ACT_MESSAGE JSON_OBJECT_T Not specified Yes Message group for the initial action. Supply explicitly; NULL creates an empty actions array for later additions. Mode: IN.
ACT_COMMAND VARCHAR2 Not specified No Optional COMMAND_SQL, COMMAND_SEGMENT, COMMAND_PARAMETER, or COMMAND_INSTANCE navigation hint. SQL/segment commands require usable object metadata; no operation is executed. Mode: IN. Default: NULL.
ACT_OBJECT JSON_OBJECT_T Not specified No Optional related-object descriptor. SQL tuning uses SQL_OBJECT; segment tuning uses DATA_OBJECT. Mode: IN. Default: NULL.
PARAM_NAME VARCHAR2 Not specified No Initialization-parameter name required to attach an ALTER PARAMETER command. If NULL, parameter command metadata is not generated. Mode: IN. Default: NULL.
FROM_PARAM_VALUE VARCHAR2 Not specified No Existing parameter value as text for an ALTER PARAMETER action. Optional; a numeric navigation attribute is also added if conversion succeeds. Mode: IN. Default: NULL.
TO_PARAM_VALUE VARCHAR2 Not specified No Recommended new parameter value as text for ALTER PARAMETER metadata. A numeric navigation attribute is added if conversion succeeds. Mode: IN. Default: NULL.
RANK_VALUE NUMBER Not specified No Recommendation rank used to order recommendations with equal benefit. Defaults to 1. Mode: IN. Default: 1.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "benefit": 1000000,
  "type": "Application Analysis",
  "type_message": {
    "msg_text": "Application Analysis"
  },
  "rank": 1,
  "actions": [
    {
      "message": {
        "msg_text": "Review SQL parsing and cursor sharing."
      }
    }
  ]
}
JSON member JSON type Meaning and availability
benefit number Estimated DB time saved, in microseconds.
type string Recommendation category from REC_TYPE_*.
type_message object Translatable message group for the category.
rank number Tie-breaking rank; default 1.
actions array Actions to implement, in insertion order. Can initially be empty.
actions[].message object Action instructions; full message-group structure is described under MSG and TEXT_MSG.
actions[].object object Optional related-object descriptor; see the object constructors.
actions[].command string Optional navigation command. No command is added when prerequisites are unavailable.

actions is always present and is [] when ACT_MESSAGE is NULL. NEW_REC does not itself add rationales; ADD_RTN or EVALUATE_RATIONALE adds that optional array. type_message is a message group. Action object/command members are conditional; the command metadata table below describes all additional attributes.

Action navigation attributes are conditional:

Command Required information Additional JSON members
COMMAND_SQL SQL object containing sql_id <p> <ul id="GUID-3B61E643-A289-4CDC-AFFC-CD277DC84DFF__UL_FMZ_ZK5_NKC" style="list-style-type: disc;"> <li>command; attr1 = SQL ID</li> <li>num_attr3 = container/source DBID</li> <li>optional num_attr1 = response time in microseconds per execution </li> <li>num_attr2 = average active sessions when SQLSTAT is available. </li> </ul> </p>
COMMAND_SEGMENT Database object whose metadata can be resolved <p> <ul id="GUID-3B61E643-A289-4CDC-AFFC-CD277DC84DFF__UL_NPC_5K5_NKC" style="list-style-type: disc;"> <li>command</li> <li>attr1 = owner</li> <li>attr2 = object name</li> <li>optional attr3 = partition name </li> <li>attr4 = PDB name</li> <li>num_attr3 = container/source DBID</li> <li>num_attr1 is null</li> </ul> </p>
COMMAND_PARAMETER Non-NULL PARAM_NAME <p> <ul style="list-style-type: disc;"> <li>command</li> <li>attr1 = parameter name</li> <li>optionalattr2/attr3 = old/new values as strings</li> <li>optional num_attr1/num_attr2 = numeric conversions of those values</li> </ul> </p>
COMMAND_INSTANCE Command constant command = EXAMINE INSTANCE ADDM.
NULL, unknown command, or unmet prerequisites None No command navigation metadata is added.

For example, a parameter action created with the applicable arguments has this structure:

{
  "message": {"msg_text": "Increase the parameter value."},
  "command": "ALTER PARAMETER",
  "attr1": "open_cursors",
  "attr2": "300",
  "attr3": "600",
  "num_attr1": 300,
  "num_attr2": 600
}

The generic attr* fields are JSON strings and num_attr* fields are numbers (or null where noted). They describe a suggested workflow; constructing the action does not execute it.

SET_OBJECT Procedure

Sets the finding’s related object to the supplied object descriptor.

Purpose

Sets the finding’s related object to the supplied object descriptor. An existing descriptor is replaced, allowing a generic finding to be associated with the specific SQL statement, segment, block, or other entity diagnosed by the rule. Use the appropriate object constructor for the descriptor.

Syntax

DBMS_ADDM_UTIL.SET_OBJECT (
    finding IN OUT NOCOPY JSON_OBJECT_T,
    obj IN JSON_OBJECT_T);

Parameters

Parameters Type Value Range Required Description Notes
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. Use NEW_FINDING to construct it before calling mutation helpers. Mode: IN OUT NOCOPY
OBJ JSON_OBJECT_T Not specified Yes Related-object descriptor from an object constructor. Optional during finding construction; SET_OBJECT replaces the current descriptor. Mode: IN.

Analysis Context Functions

The analysis record and system-metadata record identify the historical database context.

The analysis record and system-metadata record identify the historical database context. Advisor task parameters control the analysis; database initialization parameters are obtained separately through ORA_PARAMETER. All of these refer to the engine’s current analysis period.

IS_APPLICABLE Function

Evaluates whether a set of criteria matches the database and analysis scope of the current ADDM run.

Purpose

Evaluates whether a set of criteria matches the database and analysis scope of the current ADDM run. Criteria in one dimension are alternatives (OR), while dimensions are combined with AND; an unspecified dimension does not restrict applicability. Use the public APPLICABLE_* values, including single-instance and multi-instance, rather than obsolete Local/Global examples. The autonomous dimension is ignored for non-CDB and CDB-root analysis. A NULL or empty criteria array returns TRUE; criteria strings are trimmed and compared case-insensitively.

Syntax

DBMS_ADDM_UTIL.IS_APPLICABLE (
    criteria IN JSON_ARRAY_T)
RETURN BOOLEAN;

Parameters

Parameters Type Value Range Required Description Notes
CRITERIA JSON_ARRAY_T Not specified Yes Array of applicability strings. Use APPLICABLE_* values and optional version bounds; OR within a dimension, AND between dimensions. Mode: IN.

GET_ANALYSIS_PERIOD Function

Returns the PL/SQL ANALYSIS_PERIOD record initialized by the ADDM engine.

Purpose

Returns the PL/SQL ANALYSIS_PERIOD record initialized by the ADDM engine. Use its AWR identifier, snapshot bounds, and instance list when correlating utility results with additional AWR queries. DBID identifies the AWR snapshot set and may differ from SOURCE_DBID; this return value is a record, not JSON.

Syntax

DBMS_ADDM_UTIL.GET_ANALYSIS_PERIOD
RETURN ANALYSIS_PERIOD;

Parameters

None.

GET_SYS_METADATA Function

Returns the PL/SQL SYSTEM_METADATA record for the database being analyzed.

Purpose

Returns the PL/SQL SYSTEM_METADATA record for the database being analyzed. Use it to determine container scope, instance count, database role, version, and deployment characteristics before executing configuration-specific logic. The engine must have initialized the analysis context.

Syntax

DBMS_ADDM_UTIL.GET_SYS_METADATA
RETURN SYSTEM_METADATA;

Parameters

None.

TASK_PARAMETER Function

Reads an ADDM advisor-task parameter as text, including a parameter whose stored value is numeric.

Purpose

Reads an ADDM advisor-task parameter as text, including a parameter whose stored value is numeric. Use it to inspect analysis controls rather than database initialization settings. Returns NULL when the parameter is unavailable.

Syntax

DBMS_ADDM_UTIL.TASK_PARAMETER (
    parameter_name IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Parameters Type Value Range Required Description Notes
PARAMETER_NAME VARCHAR2 Not specified Yes Advisor-task parameter name, at most 128 bytes. This is not an initialization-parameter name. Mode: IN.

TASK_PARAMETER_NUMBER Function

Reads a numeric ADDM task parameter, such as a threshold controlling rule evaluation.

Purpose

Reads a numeric ADDM task parameter, such as a threshold controlling rule evaluation. Task parameters belong to the advisor task and are distinct from initialization parameters returned by ORA_PARAMETER. For missing or string-valued parameters, NULL is a possible result; callers should also handle numeric conversion errors.

Syntax

DBMS_ADDM_UTIL.TASK_PARAMETER_NUMBER (
    parameter_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
PARAMETER_NAME VARCHAR2 Not specified Yes Numeric advisor-task parameter name, at most 128 bytes, for example EVT_ELPCT_MIN Mode: IN.

Statistics Procedures and Functions

Statistics belong to the initialized analysis period and can be cached for repeated rule evaluation.

Statistics belong to the initialized analysis period and can be cached for repeated rule evaluation. Inspect availability indicators before interpreting zeros as measurements. Generic endpoint objects support both cumulative counters and gauges; use STAT_DIFF only when subtraction is meaningful. Register required system-statistic and initialization-parameter dependencies with the extension rule so the engine can prepare them.

ASH_STATS Function

Returns counts of foreground and background ASH samples split into CPU and wait states, along with the time represented by one foreground sample.

Purpose

Returns counts of foreground and background ASH samples split into CPU and wait states, along with the time represented by one foreground sample. Use the counts to assess sample coverage and estimate database time from ASH rows. Missing required ASH data can raise an error rather than returning an empty object. The time_per_row value is in microseconds (DB time / foreground sample count).

Syntax

DBMS_ADDM_UTIL.ASH_STATS
RETURN JSON_OBJECT_T;

Parameters

None.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "fg_wait": 0,
  "fg_cpu": 0,
  "bg_wait": 0,
  "bg_cpu": 0,
  "fg_idle": 0,
  "time_per_row": 0
}
JSON member JSON type Meaning and availability
fg_wait number Count of foreground wait rows
fg_cpu number Count of foreground CPU rows
bg_wait number Count of background wait rows
bg_cpu number Count of background CPU rows
fg_idle number Count of foreground wait rows that are idle
time_per_row number Estimated microseconds represented by one foreground ASH sample: ROUND(DB time/ (fg_wait + fg_cpu), 4).

CPU_STATS Function

Returns a combined profile of Oracle CPU consumption, available CPU capacity, host utilization, and CPU run-queue estimates.

Purpose

Returns a combined profile of Oracle CPU consumption, available CPU capacity, host utilization, and CPU run-queue estimates. Interpret optional measurements only when their presence flags are true, particularly for PDB analysis where host data is restricted. CPU and queue times are in microseconds; utilization members distinguish percentages from ratios as described below. CPU acquisition requires a single-instance analysis and can raise ORA-13729 for a multi-instance run.

Syntax

DBMS_ADDM_UTIL.CPU_STATS
RETURN JSON_OBJECT_T;

Parameters

None.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "init": true,
  "fgcpu": 0,
  "bgcpu": 0,
  "prscpu": 0,
  "rmancpu": 0,
  "orclcpu": 0,
  "orcl_cpu_perc": 0,
  "availcpu": 0,
  "totalcpu": 0,
  "orcl_cpu_util": 0,
  "num_cpus": 0,
  "is_num_cpu_cores_present": false,
  "num_cpu_cores": 0,
  "cpu_limit": 0,
  "cpu_util_present": false,
  "os_cpu_util": 0,
  "is_os_avg_act_thr_present": false,
  "os_avg_act_thr_perc": 0,
  "runq_present": false,
  "os_runq": 0,
  "rm_runq": 0,
  "total_runq": 0,
  "total_runq_perc": 0,
  "orcl_runq": 0,
  "orcl_runq_perc": 0
}
JSON member JSON type Meaning and availability
init boolean True if CPU stats available and cached.
fgcpu number Foreground CPU time
bgcpu number Background CPU time
prscpu number Parse CPU time
rmancpu number RMAN CPU time
orclcpu number Total CPU time consumed by Oracle
orcl_cpu_perc number Oracle CPU usage in percentage of DB time
availcpu number Total available CPU time in interval of the analysis period, taking into account instance caging
totalcpu number Total available CPU time in the interval of the analysis period based on physical CPUs, without considering instance caging.
orcl_cpu_util number Initially 100 * orclcpu / availcpu, capped at 100; when host utilization is present, it is capped again by os_cpu_util. The two utilization values use different scales, so do not assume this field is consistently a percentage.
num_cpus number Number of physical CPUs
is_num_cpu_cores_present boolean True if number of CPU cores is available, else False
num_cpu_cores number Count of CPU cores
cpu_limit number Count of CPU cores available for Oracle, based on cpu_count stat and CPU cores count
cpu_util_present boolean True if CPU utilization stats are are available
os_cpu_util number Host utilization returned by safe_div(busy_ticks, busy_ticks + idle_ticks, 0, FALSE); the expression has no multiplication by 100.
is_os_avg_act_thr_present boolean True if average active threads can be calculated else False
os_avg_act_thr_perc number OS average active active threads. = (OS CPU busy time / elapsed time) / (Count of CPU cores) Presented as percentage.
runq_present boolean True if OS CPU Run queue stats available
os_runq number Time spent in OS CPU RunQ
rm_runq number Time spent in Oracle resource manager RunQ
total_runq number Total time spent in CPU RunQ
total_runq_perc number Total OS plus Resource Manager queue time as a percentage of DB time
orcl_runq number Estimated Oracle OS CPU RunQ
orcl_runq_perc number Estimated Oracle OS CPU RunQ as percentage of DB time

Most numeric fields initialize to 0 and availability flags to false; such defaults do not prove a measured zero. CPU/queue times are microseconds; *_perc and os_avg_act_thr_perc express percentages. The os_cpu_util and orcl_cpu_util values use different scales, as described in their rows.

DATAFILE_NAME Function

Looks up the name associated with a file number in the analysis database’s AWR datafile metadata.

Purpose

Looks up the name associated with a file number in the analysis database’s AWR datafile metadata. In CDB-root analysis, pass P_CON_DBID so the file number is resolved within the correct container; without a match the result is NULL. Outside the root, the query uses the analysis database ID and file number and does not filter on P_CON_DBID.

Syntax

DBMS_ADDM_UTIL.DATAFILE_NAME (
    p_file_number IN NUMBER,
    p_con_dbid IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Parameters Type Value Range Required Description Notes
P_FILE_NUMBER NUMBER Not specified Yes Datafile number to resolve in AWR metadata. Mode: IN.
P_CON_DBID NUMBER Not specified No Container DBID required to resolve a file in a CDB-root analysis. Ignored by the non-root lookup. Mode: IN. Default: NULL.

DB_OBJ_STATS Function

Resolves a DATABASE OBJECT descriptor into object, tablespace, and optional index/base-object metadata captured by AWR.

Purpose

Resolves a DATABASE OBJECT descriptor into object, tablespace, and optional index or base-object metadata captured by AWR. This lets a rule replace an object-number-only explanation with a meaningful owner and object name. Returns NULL when no object ID or matching metadata is available; invalid object kinds can raise ORA-13722. Several activity members are initialized placeholders rather than collected statistics.

Syntax

DBMS_ADDM_UTIL.DB_OBJ_STATS (
    dbobj IN JSON_OBJECT_T)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
DBOBJ JSON_OBJECT_T Not specified Yes DATABASE OBJECT descriptor, normally produced by DATA_OBJECT. Includes object_id and optionalcon_dbid; NULL or 0 con_dbiddefaults to SOURCE_DBID. Mode:IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "object_id": 0,
  "name": "<name>",
  "subname": "<subname>",
  "full_name": "<full_name>",
  "type": "<type>",
  "owner": "<owner>",
  "tablespace": {
    "id": 0,
    "name": "<name>",
    "wait_time": 0,
    "backup": 0,
    "content": "<content>",
    "autossm": false,
    "con_dbid": 0
  },
  "index_type": "<index_type>",
  "base_object_id": 0,
  "base_object_name": "<base_object_name>",
  "base_object_owner": "<base_object_owner>",
  "con_dbid": 0,
  "object_time": 0,
  "scans": 0,
  "reads": 0,
  "writes": 0,
  "direct_reads": 0,
  "seg_adv_time": 0,
  "seg_adv_loaded": false
}
JSON member JSON type Meaning and availability
object_id number Dictionary object number
name string Object name (128 chars)
subname string Name of the subobject (128 chars)
full_name string Concat name and subname as name.subname. If subname does not exist, full_name is just the name (257 chars)
type string Type of the object (128 chars)
owner string Owner of the object (128 chars)
tablespace object Tablespace metadata for the segment.
tablespace.id number tablespace ID
tablespace.name string Tablespace name (128 chars)
tablespace.wait_time number Initialized to 0; not collected.
tablespace.backup number Initialized to 0; not collected.
tablespace.content string Tablespace content type. Either 'PERMANENT' or 'TEMPORARY' (9 chars)
tablespace.autossm boolean True if auto segment space management is ON
tablespace.con_dbid number Container associated with tablespace
index_type string Type of INDEX. For example, NORMAL, IOT - TOP (128 chars)
base_object_id number Base object dictionary number
base_object_name string Base object name (128 chars)
base_object_owner string Base object owner name (128 chars)
con_dbid number Container DBID used for the lookup.
object_time number Initialized to 0; activity or advisor detail is not collected.
scans number Initialized to 0; activity or advisor detail is not collected.
reads number Initialized to 0; activity or advisor detail is not collected.
writes number Initialized to 0; activity or advisor detail is not collected.
direct_reads number Initialized to 0; activity or advisor detail is not collected.
seg_adv_time number Initialized to 0; activity/advisor detail is not collected.
seg_adv_loaded boolean Initialized to false; segment-advisor data has not been loaded.

subname and index Or base-object metadata can be null. Index metadata is useful only for supported, non-partitioned index cases. Use tablespace.backup for the backup-mode count. The returned placeholder activity members are listed explicitly so zero values are not mistaken for collected I/O or segment-advisor evidence.

EXADATA_CELL_IO_STATS Function

Returns Exadata capacity and workload measurements for each cell and disk type.

Purpose

Returns Exadata capacity and workload measurements for each cell and disk type. Compare requested IOPS and throughput with capacity, and use the latency and workload-share members to identify overloaded or imbalanced cells. Data comes from the Exadata temporary tables prepared by ADDM; unavailable cached source data yields an empty array.

Syntax

DBMS_ADDM_UTIL.EXADATA_CELL_IO_STATS
RETURN JSON_ARRAY_T;

Parameters

None.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "cell_hash": 0,
    "cell_name": "<cell_name>",
    "cell_model": "<cell_model>",
    "disk_type": "<disk_type>",
    "avg_max_cell_iops": 0,
    "avg_max_cell_mbs": 0,
    "avg_req_cell_iops": 0,
    "avg_req_cell_mbs": 0,
    "avg_req_cell_small_reads": 0,
    "avg_avg_cell_small_reads_lat": 0,
    "avg_per_cell_iops": 0,
    "avg_per_cell_mbs": 0,
    "avg_per_cell_small_reads_lat": 0,
    "avg_tot_per_cell_iops": 0,
    "avg_tot_per_cell_mbs": 0,
    "avg_req_cell_large_writes": 0,
    "avg_avg_cell_large_writes_lat": 0
  }
]
JSON member JSON type Meaning and availability
cell_hash number or null Cell hash identifier
cell_name string or null Cell name.
cell_model string or null Cell model (4000 chars)
disk_type string or null Disk type (4000 chars)
avg_max_cell_iops number or null Average max IOPS across snapshots in cell
avg_max_cell_mbs number or null Average max MBS across snapshots in cell
avg_req_cell_iops number or null Average requested IOPS across snapshots range in cell
avg_req_cell_mbs number or null Average requested MBS across snapshots range in cell
avg_req_cell_small_reads number or null Average requested Small reads across snapshots range in cell
avg_avg_cell_small_reads_lat number or null Weighted average requested Small reads latency across snapshots range in cell
avg_per_cell_iops number or null Requested IOPS as percentage of max capacity across snapshots range in cell
avg_per_cell_mbs number or null Requested MBS as percentage of max capacity across snapshots range in cell
avg_per_cell_small_reads_lat number or null Small read latency as percentage of expected value
avg_tot_per_cell_iops number or null Total requested IOPS in the cell as percentage of total of cells of same model
avg_tot_per_cell_mbs number or null Total requested MBS in the cell as percentage of total of cells of same model
avg_req_cell_large_writes number or null Average requested Large writes across snapshots range in cell
avg_avg_cell_large_writes_lat number or null Weighted average requested Large writes latency across snapshots range in cell

Numeric source values are returned through GREATEST(0, value); SQL nulls remain null. IOPS denotes operations per second, MBS denotes throughput in MB/s, and percentage members are identified in their descriptions. Preserve the underlying Exadata statistic’s unit when comparing latency values.

EXADATA_CELL_CFG_STATS Function

Returns each cell’s name, model, and flash-cache mode from the ending snapshot’s cached configuration.

Purpose

Returns each cell’s name, model, and flash-cache mode from the ending snapshot’s cached configuration. Use it to qualify Exadata findings by the configuration actually analyzed. Returns an empty array when the source configuration data was not cached or has no matching rows.

Syntax

DBMS_ADDM_UTIL.EXADATA_CELL_CFG_STATS
RETURN JSON_ARRAY_T;

Parameters

None.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "cell_name": "<cell_name>",
    "cell_model": "<cell_model>",
    "flash_cache_mode": "<flash_cache_mode>"
  }
]
JSON member JSON type Meaning and availability
cell_name string or null Cell name at the ending snapshot.
cell_model string or null Cell hardware model.
flash_cache_mode string or null Configured flash-cache mode at the ending snapshot.

EXADATA_GLOBAL_SYS_IO_STATS Function

Returns global Exadata cache-space, large-write rejection, and scrub activity statistics.

Purpose

Returns global Exadata cache-space, large-write rejection, and scrub activity statistics. Although the measurements are global, the declared and implemented return type is a JSON array. The result is built from ADDM’s prepared Exadata data and can be empty when that data is unavailable.

Syntax

DBMS_ADDM_UTIL.EXADATA_GLOBAL_SYS_IO_STATS
RETURN JSON_ARRAY_T;

Parameters

None.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "tot_spc_rqs_oltp_f": 0,
    "tot_spc_rqs_oltp_s": 0,
    "tot_large_writes_rej_cg": 0,
    "tot_large_writes_rej_cg_lat": 0,
    "tot_large_writes_rej_lw": 0,
    "tot_large_writes_rej_lw_lat": 0,
    "tot_large_writes_rej_ml": 0,
    "tot_large_writes_rej_ml_lat": 0,
    "tot_large_writes_rej_mgl": 0,
    "tot_large_writes_rej_mgl_lat": 0,
    "tot_scrub_iops": 0,
    "tot_scrub_mbs": 0
  }
]
JSON member JSON type Meaning and availability
tot_spc_rqs_oltp_f number or null Cache space request failed
tot_spc_rqs_oltp_s number or null Cache space sequest suc
tot_large_writes_rej_cg number or null Large writes rejected CG
tot_large_writes_rej_cg_lat number or null Large writes rejected CG est latency
tot_large_writes_rej_lw number or null Large writes rejected LW
tot_large_writes_rej_lw_lat number or null Large writes rejected LW est latency
tot_large_writes_rej_ml number or null Large writes rejected ML
tot_large_writes_rej_ml_lat number or null Large writes rejected ML est latency
tot_large_writes_rej_mgl number or null Large writes rejected MGL
tot_large_writes_rej_mgl_lat number or null Large writes rejected MGL est latency
tot_scrub_iops number or null Scrub IOPS
tot_scrub_mbs number or null Scrub MBS

EXADATA_MODEL_IO_STATS Function

Aggregates Exadata I/O capacity and workload across cells of the same model and disk type.

Purpose

Aggregates Exadata I/O capacity and workload across cells of the same model and disk type. Use it to compare hardware groups without assuming that unlike cell models have the same capacity. Returns an array of model/disk-type rows, or an empty array when source data is unavailable.

Syntax

DBMS_ADDM_UTIL.EXADATA_MODEL_IO_STATS
RETURN JSON_ARRAY_T;

Parameters

None.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "cell_model": "<cell_model>",
    "disk_type": "<disk_type>",
    "avg_max_mod_iops": 0,
    "avg_max_mod_mbs": 0,
    "avg_req_mod_iops": 0,
    "avg_req_mod_mbs": 0,
    "avg_req_mod_small_reads": 0,
    "avg_mod_small_reads_lat": 0,
    "avg_per_mod_iops": 0,
    "avg_per_mod_mbs": 0,
    "avg_per_mod_small_reads_lat": 0,
    "avg_req_mod_large_writes": 0,
    "avg_mod_large_writes_lat": 0
  }
]
JSON member JSON type Meaning and availability
cell_model string or null Cell model (4000 chars)
disk_type string or null Disk type (4000 chars)
avg_max_mod_iops number or null Max IOPS across cells of same model by disk type
avg_max_mod_mbs number or null Max MBS across cells of same model by disk type
avg_req_mod_iops number or null Requested IOPS across cells of same model by disk type
avg_req_mod_mbs number or null Requested MBS across cells of same model by disk type
avg_req_mod_small_reads number or null Requested Small reads across cells of same model by disk type
avg_mod_small_reads_lat number or null Requested Small latency across cells of same model by disk type
avg_per_mod_iops number or null Requested IOPS of cells in the same model by disk type as percentage of total in the system
avg_per_mod_mbs number or null Requested MBS of cells in the same model by disk type as percentage of total in the system
avg_per_mod_small_reads_lat number or null Average Small reads latency in the same model cells by disk type as percentage of expected value
avg_req_mod_large_writes number or null Requested Large writes across cells of same model by disk type
avg_mod_large_writes_lat number or null Requested Large writes latency across cells of same model by disk type

Numeric source values are returned through GREATEST(0, value); SQL nulls remain null. IOPS denotes operations per second, MBS denotes throughput in MB/s, and percentage members are identified in their descriptions. Preserve the underlying Exadata statistic’s unit when comparing latency values.

EXADATA_SYSTEM_IO_STATS Function

Aggregates Exadata capacity, I/O requests, throughput, and latency for the system by disk type.

Purpose

Aggregates Exadata capacity, I/O requests, throughput, and latency for the system by disk type. Use it to assess system-wide storage demand before examining individual models or cells. Returns an empty array when the ADDM Exadata data is unavailable.

Syntax

DBMS_ADDM_UTIL.EXADATA_SYSTEM_IO_STATS
RETURN JSON_ARRAY_T;

Parameters

None.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "disk_type": "<disk_type>",
    "avg_max_sys_iops": 0,
    "avg_max_sys_mbs": 0,
    "avg_req_sys_iops": 0,
    "avg_req_sys_mbs": 0,
    "avg_req_sys_small_reads": 0,
    "avg_sys_small_reads_lat": 0,
    "avg_per_sys_iops": 0,
    "avg_per_sys_mbs": 0,
    "avg_per_sys_small_reads_lat": 0,
    "avg_req_sys_large_writes": 0,
    "avg_sys_large_writes_lat": 0
  }
]
JSON member JSON type Meaning and availability
disk_type string or null Disk type (4000 chars)
avg_max_sys_iops number or null Max IOPS across systems by disk type
avg_max_sys_mbs number or null Max MBS across systems by disk type
avg_req_sys_iops number or null Requested IOPS by disk type across the system
avg_req_sys_mbs number or null Requested MBS by disk type across the systems
avg_req_sys_small_reads number or null Requested Small reads by disk type across the system
avg_sys_small_reads_lat number or null Requested Small reads latency by disk type across the system
avg_per_sys_iops number or null Requested IOPS by disk type as percentage of total in the system
avg_per_sys_mbs number or null Requested MBS by disk type as percentage of total in the system
avg_per_sys_small_reads_lat number or null Average small read latency by disk type as percentage of expected value
avg_req_sys_large_writes number or null Requested Large writes by disk type across the system
avg_sys_large_writes_lat number or null Requested Large writes latency by disk type across the system

Numeric source values are returned through GREATEST(0, value); SQL nulls remain null. IOPS denotes operations per second, MBS denotes throughput in MB/s, and percentage members are identified in their descriptions. Preserve the underlying Exadata statistic’s unit when comparing latency values.

GET_EFFEC_NUM_CORES Procedure

Determines the number of cores effectively available to the database and reports whether the CPU limit supplied the answer.

Purpose

Determines the number of cores effectively available to the database and reports whether the CPU limit supplied the answer. For a PDB it uses the CPU limit; otherwise it prefers the OS core count, falls back to CPU count, and applies a smaller positive CPU limit. If neither OS count is available outside a PDB, the initial result is zero.

Syntax

DBMS_ADDM_UTIL.GET_EFFEC_NUM_CORES (
    num_cores OUT NUMBER,
    is_cpu_lim_used OUT BOOLEAN);

Parameters

Parameters Type Value Range Required Description Notes
NUM_CORES NUMBER Not specified No Effective core count. Uses the CPU limit for PDB analysis; otherwise OS core count, OS CPU count as fallback, or zero if unavailable, capped by a smaller positive CPU limit. Mode: OUT. Default: Not applicable.
IS_CPU_LIM_USED BOOLEAN Not specified No TRUE when NUM_CORES is determined by the database CPU limit (including the PDB path);FALSE when OS counts supplied the result without a smaller limit. Mode: OUT. Default: Not applicable.

GET_TOP_SQL Function

Returns cached ASH estimates and, where available, AWR SQL-statistic deltas for one SQL ID and container.

Purpose

Returns cached ASH estimates and, where available, AWR SQL-statistic deltas for one SQL ID and container. It operates on the SQL statements selected for ADDM’s top-SQL analysis, so it is not an arbitrary SQL-history lookup. A missing statement or NULL SQL ID returns {"init": false}, while sql_stats_found separately indicates whether the top-SQL entry has SQLSTAT data. SQL text is intentionally separate; retrieve it through GET_SQL_TEXT when needed.

Syntax

DBMS_ADDM_UTIL.GET_TOP_SQL (
    sql_id IN VARCHAR2,
    con_dbid IN NUMBER DEFAULT NULL)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
SQL_ID VARCHAR2 Not specified Yes SQL statement identifier. GET_TOP_SQL and GET_SQL_TEXT require exactly 13 characters when non-NULL. Mode:IN.
CON_DBID NUMBER Not specified No Container DBID for the requested SQL. NULL or 0 selects the analysis record’s SOURCE_DBID. Mode: IN. Default: NULL.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "init": true,
  "sql_id": "a2k1zqcbp5nxf",
  "opcode": 0,
  "command": "<command>",
  "con_dbid": 0,
  "ash_stats": {
    "db_time": 0,
    "cpu_time": 0,
    "io_time": 0,
    "cluster_time": 0,
    "parse_time": 0,
    "java_exec_time": 0,
    "plsql_exec_time": 0,
    "has_pq_exec": false,
    "plans_observed": 0,
    "top_events": [
      {
        "event_name": "<event_name>",
        "event_time": 0
      }
    ],
    "top_segments": [
      {
        "segment_id": 0,
        "segment_time": 0,
        "full_scan_time": 0
      }
    ]
  },
  "entry_sql_id": "a2k1zqcbp5nxf",
  "entry_opcode": 0,
  "entry_command": "<entry_command>",
  "entry_percentage": 0,
  "sql_stats_found": true,
  "cpu_time": 0,
  "db_time": 0,
  "plsql_exec_time": 0,
  "java_exec_time": 0,
  "io_blocks": 0,
  "io_time": 0,
  "cluster_time": 0,
  "rows_processed": 0,
  "fetches": 0,
  "end_of_fetches": 0,
  "executions": 0,
  "has_pq_exec": false,
  "plans_observed": 0
}
JSON member JSON type Meaning and availability
init boolean True for a cached SQL entry. An unavailable SQL ID returns only init: false.
sql_id string SQL ID for requested SQL. (13 chars)
opcode number Opcode for requested SQL
command string Description of opcode. (64 chars)
con_dbid number Container associated with requested SQL
ash_stats object Sample-based SQL time estimates and related activity.
ash_stats.db_time number DB time spent on requested SQL
ash_stats.cpu_time number CPU time spent on requested SQL
ash_stats.io_time number User I/O wait time on requested SQL
ash_stats.cluster_time number Cluster wait class time on requested SQL
ash_stats.parse_time number Parse time spent on requested SQL
ash_stats.java_exec_time number Java execution time spent on requested SQL
ash_stats.plsql_exec_time number PL/SQL execution time for requested SQL
ash_stats.has_pq_exec boolean True if PQ slaves exist in ASHfor the requested SQL, else False
ash_stats.plans_observed number Count of unique plans available
ash_stats.top_events array or null Selected top waits; null if no event array was populated. User I/O and Cluster waits are excluded from this detailed event list.
ash_stats.top_events[].event_name string Top event name (64 chars)
ash_stats.top_events[].event_time number Foreground Time spent waiting for event
ash_stats.top_segments array or null Selected top segments; null if no segment array was populated.
ash_stats.top_segments[].segment_id number Top segment object ID
ash_stats.top_segments[].segment_time number Foreground time spent using or waiting segment
ash_stats.top_segments[].full_scan_time number Foreground time spent in full scans
entry_sql_id string or null Top entry point SQL ID (13 chars)
entry_opcode number or null Top entry point SQL opcode
entry_command string or null Top entry point description of SQL opcode. (64 chars)
entry_percentage number or null Time spent in top entry SQL as percentage of total DB time of requested SQL
sql_stats_found boolean True if AWR SQLSTAT deltas are available; otherwise the outer SQLSTAT measurement members are null.
cpu_time number or null CPU time spent on requested SQL
db_time number or null Elapsed-time delta from AWR SQLSTAT, in microseconds.
plsql_exec_time number or null PL/SQL execution time for requested SQL
java_exec_time number or null Java execution time spent on requested SQL
io_blocks number or null Count of blocks from physical I/O
io_time number or null User I/O wait time on requested SQL
cluster_time number or null Cluster wait class time on requested SQL
rows_processed number or null Count of rows requested SQL returns
fetches number or null Count of fetches for requested SQL
end_of_fetches number or null Number of times the cursor for requested SQL was fully executed. Partial executions excluded.
executions number or null Count of executions for requested SQL
has_pq_exec boolean or null True if PQ slaves exist for the requested SQL, else False
plans_observed number or null Count of unique plans available

If unavailable, the entire result is only {"init": false}. For a cached entry, init is a JSON boolean. ash_stats contains sample-derived times in microseconds; outer SQLSTAT time fields are also microseconds but are independent measurements and need not match. When sql_stats_found is false, the outer SQLSTAT fields remain null. Entry-point members and the two nested top arrays may also be null.

GET_SQL_TEXT Function

Returns cached SQL text for a top SQL statement or a top-level SQL statement associated with it.

Purpose

Returns cached SQL text for a top SQL statement or a top-level SQL statement associated with it. The CLOB contains SQL text, not JSON. Returns NULL for a null SQL ID or when the text is absent from the analysis cache; a non-null ID must contain exactly 13 characters.

Syntax

DBMS_ADDM_UTIL.GET_SQL_TEXT (
    sql_id IN VARCHAR2)
RETURN CLOB;

Parameters

Parameters Type Value Range Required Description Notes
SQL_ID VARCHAR2 Not specified Yes SQL statement identifier. GET_TOP_SQL and GET_SQL_TEXT require exactly 13 characters when non-NULL. Mode: IN.

IS_PARAMETER_MODIFIED Function

Reports whether the historical parameter values indicate variation over the analysis period.

Purpose

Reports whether the historical parameter values indicate variation over the analysis period. The function returns FALSE for an unavailable parameter or equal non-null minimum and maximum values, and TRUE otherwise. This is a min/max comparison, not simply a test of the start/end modification flags, and unknown bounds on an initialized parameter count as modified.

Syntax

DBMS_ADDM_UTIL.IS_PARAMETER_MODIFIED (
    parameter_name IN VARCHAR2)
RETURN BOOLEAN;

Parameters

Parameters Type Value Range Required Description Notes
PARAMETER_NAME VARCHAR2 Not specified Yes Requested parameter name. Task-parameter APIs enforce 128 bytes; initialization-parameter APIs enforce 64 bytes. Mode: IN.

IO_CAPACITY_STATS Function

Returns estimates and thresholds used to assess slow file I/O, including expected latency, minimum significant activity, and excess-latency impact.

Purpose

Returns estimates and thresholds used to assess slow file I/O, including expected latency, minimum significant activity, and excess-latency impact. The has_impact flag indicates whether an impact estimate was obtained independently of the availability of the threshold values. Returns SQL NULL when the capacity structure cannot be acquired.

Syntax

DBMS_ADDM_UTIL.IO_CAPACITY_STATS
RETURN JSON_OBJECT_T;

Parameters

None.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "expected_iotime": 0,
  "sig_time": 0,
  "sig_read_time": 0,
  "sig_read_reqs": 0,
  "has_impact": false,
  "impact": 0,
  "scale": 0
}
JSON member JSON type Meaning and availability
expected_iotime number expected read I/O time in microseconds for reading one block
sig_time number extra time in microseconds spent on latency to be considered significant for a slow file
sig_read_time number read time in microseconds considered enough to make a file significant
sig_read_reqs number number of reads considered enough to make a file significant
has_impact boolean True if impact could be acquired, else False
impact number impact (extra latency) of slow IO
scale number Scaling factor expressed in millionths: 1000000 represents 1.0. Applied to excess latency asimpact * scale / 1000000.

IOSTAT_DETAIL Function

Returns I/O activity grouped by file type and database function, with optional filters on both dimensions.

Purpose

Returns I/O activity grouped by file type and database function, with optional filters on both dimensions. This helps separate, for example, datafile reads by the buffer cache from writes by DBWR or LGWR. A NULL or empty filter array leaves that dimension unrestricted; when both dimensions are supplied, a row must match both. Returns an empty array when data cannot be acquired. During CDB-root analysis, the aggregated rows include PDB I/O.

Syntax

DBMS_ADDM_UTIL.IOSTAT_DETAIL (
    filetypes IN JSON_ARRAY_T DEFAULT NULL,
    functions IN JSON_ARRAY_T DEFAULT NULL)
RETURN JSON_ARRAY_T;

Parameters

Parameters Type Value Range Required Description Notes
FILETYPES JSON_ARRAY_T Not specified No Array of exact file-type names. NULL or [] disables this dimension of filtering; multiple entries are alternatives. Mode: IN. Default: NULL.
FUNCTIONS JSON_ARRAY_T Not specified No Array of exact I/O function names. NULL or []disables this dimension of filtering; a result must also match any file-type filter. Mode: IN. Default: NULL.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "filetype_id": 0,
    "filetype_name": "<filetype_name>",
    "function_id": 0,
    "function_name": "<function_name>",
    "small_read_mb": 0,
    "small_write_mb": 0,
    "large_read_mb": 0,
    "large_write_mb": 0,
    "small_read_reqs": 0,
    "small_write_reqs": 0,
    "large_read_reqs": 0,
    "large_write_reqs": 0,
    "number_of_waits": 0,
    "wait_time": 0
  }
]
JSON member JSON type Meaning and availability
filetype_id number Filetype ID
filetype_name string Filetype Name
function_id number Function ID
function_name string Function Name
small_read_mb number # of single block megabytes read
small_write_mb number # of single block megabytes written
large_read_mb number # of multiblock megabytes read
large_write_mb number # of multiblock megabytes written
small_read_reqs number # of single block read requests
small_write_reqs number # of single block write requests
large_read_reqs number # of multiblock read requests
large_write_reqs number # of multiblock write requests
number_of_waits number Number of I/O waits by functionality
wait_time number Total wait time (in milliseconds)

Accepted file-type names are Archive Log, Archive Log Backup, Control File, Data File, Data File Copy, Data File Incremental Backup, Data Pump Dump File, Flashback Log, Log File, Other, and Temp File.

Accepted function names are ARCH, Buffer Cache Reads, DBWR, Data Pump, Direct Reads, Direct Writes, LGWR, Others, RMAN, Recovery, and Streams AQ. Comparisons use the stored names exactly.

IO_STATS Function

Returns database I/O totals together with a breakdown by block size.

Purpose

Returns database I/O totals together with a breakdown by block size. Use the per-size rows when converting blocks to bytes or comparing single-block read latency across differently sized files. Times are in microseconds and sizes are in bytes; returns SQL NULL if the I/O statistics cannot be obtained.

Syntax

DBMS_ADDM_UTIL.IO_STATS
RETURN JSON_OBJECT_T;

Parameters

None.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "default_block_size": 0,
  "block_size_stats": [
    {
      "block_size": 0,
      "read_blocks": 0,
      "read_time": 0,
      "write_blocks": 0,
      "write_time": 0,
      "read_reqs": 0,
      "write_reqs": 0,
      "singleblk_read_reqs": 0,
      "singleblk_read_time": 0,
      "files": 0
    }
  ],
  "read_blocks": 0,
  "read_bytes": 0,
  "write_blocks": 0,
  "write_bytes": 0,
  "read_time": 0,
  "write_time": 0,
  "read_reqs": 0,
  "write_reqs": 0,
  "singleblk_read_reqs": 0,
  "singleblk_read_time": 0,
  "files": 0
}
JSON member JSON type Meaning and availability
default_block_size number Default db block size in bytes
block_size_stats array One statistics object per block size.
block_size_stats[].block_size number Block size in bytes
block_size_stats[].read_blocks number Number of physical block reads
block_size_stats[].read_time number Read time in microseconds
block_size_stats[].write_blocks number Number of physical block writes
block_size_stats[].write_time number Write time in microseconds
block_size_stats[].read_reqs number Total number of read requests
block_size_stats[].write_reqs number Total number of write requests
block_size_stats[].singleblk_read_reqs number Number of single-block read requests
block_size_stats[].singleblk_read_time number Total time for single-block reads in microseconds
block_size_stats[].files number Number of files with read activity
read_blocks number Total blocks read (all sizes)
read_bytes number Total bytes read
write_blocks number Total blocks written (all sizes)
write_bytes number total bytes written
read_time number Total read time in microseconds
write_time number Total write time in microseconds
read_reqs number Total number of read requests
write_reqs number Total number of write requests
singleblk_read_reqs number Number of single-block read requests
singleblk_read_time number Total time for single-block reads in microseconds
files number Number of files with read activity

MEMORY_ADVICE Function

Returns memory-advisor estimates used to identify a useful memory target and estimate database time that could be saved.

Purpose

Returns memory-advisor estimates used to identify a useful memory target and estimate database time that could be saved. The function selects memory-target advice when available and otherwise works with SGA-target advice. Check missdata, wasoff, and changed before interpreting the benefit; curr and target are MB, while impact and benefit are microseconds. Acquisition requires a single-instance analysis and raises ORA-13729 for a multi-instance run.

Syntax

DBMS_ADDM_UTIL.MEMORY_ADVICE
RETURN JSON_OBJECT_T;

Parameters

None.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "impact": 0,
  "benefit": 0,
  "curr": 0,
  "target": 0,
  "changed": false,
  "missdata": false,
  "mt_off": false,
  "wasoff": false,
  "sigmem": false,
  "param": "<param>"
}
JSON member JSON type Meaning and availability
impact number Potential DB time saved at the largest simulated memory size, in microseconds.
benefit number Estimated DB time saved at the selected target, in microseconds.
curr number Current size of SGA in Mb
target number Target size of SGA in Mb
changed boolean True if memory size was changed else False
missdata boolean True if data is missing for memory analysis
mt_off boolean True if memory_target_advice off else False
wasoff boolean True if both memory_target_advice and sga_target_advice are off, else False
sigmem boolean True if ORA-04031 is significant for memory_advice, else False
param string Parameter selected for the advice, normally memory_target or sga_target.

ORA_PARAMETER Function

Returns historical initialization-parameter values, default flags, and modification indicators for the analysis period.

Purpose

Returns historical initialization-parameter values, default flags, and modification indicators for the analysis period. Use it to reason about the configuration captured by AWR rather than the database’s current parameter value. The actual name member is param_name; unavailable parameters return init: false with null values and omit the two default flags.

Syntax

DBMS_ADDM_UTIL.ORA_PARAMETER (
    parameter_name IN VARCHAR2)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
PARAMETER_NAME VARCHAR2 Not specified Yes Requested parameter name. Task-parameter APIs enforce 128 bytes; initialization-parameter APIs enforce 64 bytes. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "init": true,
  "param_name": "<param_name>",
  "start_value": "<start_value>",
  "end_value": "<end_value>",
  "start_isdefault": 0,
  "end_isdefault": 0,
  "default_value": "<default_value>",
  "min_value": "<min_value>",
  "max_value": "<max_value>",
  "start_modified": 0,
  "end_modified": 0
}
JSON member JSON type Meaning and availability
init boolean True if init.ora parameter is found, else false
param_name string Parameter name (64 chars)
start_value string Value in start snapshot (512 chars)
end_value string Value in end snapshot (512 chars)
start_isdefault number Is value in start snapshot set to default value. 0 => value is not set to default value. In case of RAC at least one instance is not set to default value. 1 => value is set to default value. In case of RAC all instances are set to default value.
end_isdefault number Is value in end snapshot set to default value. 0 => value is not set to default value. In case of RAC at least one instance is not set to default value. 1 => value is set to default value. In case of RAC all instances are set to default value.
default_value string Default parameter value (512 chars)
min_value string Minimum value of parameter between snapshots (512 chars)
max_value string Maximum value of parameter between snapshots (512 chars)
start_modified number Is the parameter modified in the start snapshot. 0 => not modified after start up 1 => modified using alter session 2 => modified using alter system
end_modified number Is the parameter modified in the end snapshot.0 => not modified after start up 1=> modified using alter session 2 => modified using alter system

The successful shape below includes both default flags. When a parameter is unavailable, init is false, param_name still identifies the requested parameter. All value and modification members are null, and start_isdefault/end_isdefault are absent. Modification codes are 0 (not modified since start up), 1 (ALTER SESSION), and 2 (ALTER SYSTEM). Default flags are numeric 0 or 1, not JSON booleans.

ORA_PARAM_END_VALUE Function

Returns the initialization parameter’s string value from the ending snapshot.

Purpose

Returns the initialization parameter’s string value from the ending snapshot. This is useful for messages that should describe the configuration associated with the analyzed period. Returns NULL when the parameter is unavailable; it does not read the current live setting.

Syntax

DBMS_ADDM_UTIL.ORA_PARAM_END_VALUE (
    parameter_name IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Parameters Type Value Range Required Description Notes
PARAMETER_NAME VARCHAR2 Not specified Yes Requested parameter name. Task-parameter APIs enforce 128 bytes; initialization-parameter APIs enforce 64 bytes. Mode: IN.

OS_STAT Function

Returns begin and end values of an operating-system statistic for a single-instance analysis.

Purpose

Returns begin and end values of an operating-system statistic for a single-instance analysis. The function rejects a multi-instance analysis with ORA-13729 because host-level counters cannot be interpreted as one machine’s statistics. A missing individual statistic returns init: false with zero endpoints; native units depend on the OS statistic.

Syntax

DBMS_ADDM_UTIL.OS_STAT (
    stat_name IN VARCHAR2)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "init": true,
  "begin_value": 0,
  "end_value": 0
}
JSON member JSON type Meaning and availability
init boolean True if begin/end values have been initialized, else False
begin_value number Stat value from begin snapshot
end_value number Stat value from end snapshot

An unavailable individual statistic returns {"init": false, "begin_value": 0, "end_value": 0}. Units depend on STAT_NAME.

OS_STAT_VALUE Function

Returns the nonnegative delta of a cumulative OS statistic through STAT_DIFF(OS_STAT(...)).

Purpose

Returns the non negative delta of a cumulative OS statistic through STAT_DIFF(OS_STAT(...)). The same single-instance restriction applies as for OS_STAT. Use the endpoint values directly for gauges such as CPU count instead of taking their difference.

Syntax

DBMS_ADDM_UTIL.OS_STAT_VALUE (
    stat_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode: IN.

PDB_NAME Function

Resolves a container DBID to the container name known to the ADDM analysis.

Purpose

Resolves a container DBID to the container name known to the ADDM analysis. Use it to qualify messages for root-level analysis where results can refer to several containers. A null or zero DBID returns NULL, as does unavailable name information.

Syntax

DBMS_ADDM_UTIL.PDB_NAME (
    con_dbid IN NUMBER)
RETURN VARCHAR2;

Parameters

Parameters Type Value Range Required Description Notes
CON_DBID NUMBER Not specified Yes Container DBID to resolve. NULL or 0 returns NULL. Mode: IN.

RAC_MISC_STAT_VALUE Function

Returns the analysis-period delta of one miscellaneous RAC statistic from AWR_PDB_DLM_MISC.

Purpose

Returns the analysis-period delta of one miscellaneous RAC statistic from AWR_PDB_DLM_MISC. The function treats the statistic as cumulative, so callers should choose a counter for which subtraction is meaningful. An unavailable named statistic returns zero.

Syntax

DBMS_ADDM_UTIL.RAC_MISC_STAT_VALUE (
    stat_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode: IN.

RAC_STATS Function

Returns interconnect and global-cache metrics for the analysis period, separating messaging, lost blocks, multiblock requests, contention, congestion, and network traffic.

Purpose

Returns interconnect and global-cache metrics for the analysis period, separating messaging, lost blocks, multiblock requests, contention, congestion, and network traffic. Use it to explain RAC-related database time and distinguish latency from volume. Members that cannot be acquired remain JSON null; the object does not have an init flag.

Syntax

DBMS_ADDM_UTIL.RAC_STATS
RETURN JSON_OBJECT_T;

Parameters

None.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "msg_time_us": 0,
  "lb_time_us": 0,
  "mb_time_us": 0,
  "ntwk_time_us": 0,
  "ntwk_cnt": 0,
  "ntwk_size": 0,
  "busy_time_us": 0,
  "busy_cnt": 0,
  "cong_time_us": 0,
  "cong_cnt": 0,
  "bbusy_time_us": 0,
  "bbusy_rel_us": 0,
  "ping_time_us": 0,
  "est_latency_us": 0,
  "gc_bytes": 0,
  "lock_bytes": 0,
  "pq_bytes": 0,
  "other_bytes": 0
}
JSON member JSON type Meaning and availability
msg_time_us number or null Impact (time in microseconds) for all messaging events
lb_time_us number or null Impact (time in microseconds) for lost blocks
mb_time_us number or null Total time (in microseconds) for multiblock requests
ntwk_time_us number or null Total time (in microseconds) for network messages
ntwk_cnt number or null total network messages (count)
ntwk_size number or null Total bytes in network messages
busy_time_us number or null Time (in microseconds) spent on contention requests
busy_cnt number or null Number of contention requests
cong_time_us number or null Time spent (in microseconds) on congestion requests
cong_cnt number or null Number of congestion requests
bbusy_time_us number or null Time spent (in microseconds) in gc buffer busy event (acquire)
bbusy_rel_us number or null Time spent (in microseconds) in gc buffer busy (release)
ping_time_us number or null Average time(in microseconds) for a ping
est_latency_us number or null Estimated minimal latency (in microseconds) of request, usually the same as ping time
gc_bytes number or null Bytes used for gc traffic
lock_bytes number or null Bytes used for lock traffic
pq_bytes number or null Bytes used for PQ traffic
other_bytes number or null All other traffic in bytes

All members exist; individual unavailable measurements remain JSON null. Time members ending in _us are microseconds, *_cnt are counts, and size/byte members are bytes.

SERVICE_NAME Function

Resolves an ASH service-name hash within its container to a readable service name.

Purpose

Resolves an ASH service-name hash within its container to a readable service name. Supply the container DBID for CDB-root analysis to avoid ambiguity between PDBs. For non-CDB or PDB analysis the analysis container is the default; unavailable name information returns NULL.

Syntax

DBMS_ADDM_UTIL.SERVICE_NAME (
    service_hash IN NUMBER,
    con_dbid IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Parameters Type Value Range Required Description Notes
SERVICE_HASH NUMBER Not specified Yes Service-name hash from ASH or AWR to resolve within its container. Mode: IN.
CON_DBID NUMBER Not specified No Container DBID for service lookup. Required in CDB-root analysis; the analysis container is the default for non-CDB/PDB analysis. Mode: IN. Default: NULL.

STAT_DIFF Function

Calculates a nonnegative analysis-period delta from a generic statistic object: GREATEST(0, NVL(end_value,0) - NVL(begin_value,0)).

Purpose

Calculates a non negative analysis-period delta from a generic statistic object: GREATEST(0, NVL(end_value,0) - NVL(begin_value,0)). This is appropriate for cumulative counters, not instantaneous values such as CPU count. The helper does not inspect init; check that flag first when distinguishing missing data from a real zero is important.

Syntax

DBMS_ADDM_UTIL.STAT_DIFF (
    stat IN JSON_OBJECT_T)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
STAT JSON_OBJECT_T Not specified Yes Generic statistic object with numeric begin_value and end_value. Check init separately if missing data must not be interpreted as zero. Mode: IN.

SYS_STAT Function

Returns begin and end values for a database system statistic, acquiring the requested statistic if it was not already requested by the rule.

Purpose

Returns begin and end values for a database system statistic, acquiring the requested statistic if it was not already requested by the rule. Interpret the values according to the statistic’s native units and whether it is cumulative. A missing individual statistic returns init: false and zero endpoints.

Syntax

DBMS_ADDM_UTIL.SYS_STAT (
    stat_name IN VARCHAR2)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "init": true,
  "begin_value": 0,
  "end_value": 0
}
JSON member JSON type Meaning and availability
init boolean True if begin/end values have been initialized, else False
begin_value number Stat value from begin snapshot
end_value number Stat value from end snapshot

An unavailable individual statistic returns {"init": false, "begin_value": 0, "end_value": 0}. Units depend on STAT_NAME.

SYS_STAT_VALUE Function

Returns STAT_DIFF(SYS_STAT(STAT_NAME)) for a cumulative system statistic.

Purpose

Returns STAT_DIFF(SYS_STAT(STAT_NAME)) for a cumulative system statistic. The units depend on the selected statistic, such as blocks, requests, or time units defined by that counter. Missing individual statistics produce zero; use SYS_STAT when availability must be checked.

Syntax

DBMS_ADDM_UTIL.SYS_STAT_VALUE (
    stat_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode: IN.

TOP_ENQUEUE_STATS Function

Returns up to ten enqueue types with the largest increase in successful requests over the analysis period.

Purpose

Returns up to ten enqueue types with the largest increase in successful requests over the analysis period. Each row includes request, wait, failure, and cumulative wait-time deltas, helping distinguish frequently acquired locks from expensive waits. The documented ordering is by successful requests, not wait time; unavailable underlying data or no qualifying rows yields an empty array.

Syntax

DBMS_ADDM_UTIL.TOP_ENQUEUE_STATS
RETURN JSON_ARRAY_T;

Parameters

None.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "eq_type": "<eq_type>",
    "total_req#": 0,
    "total_wait#": 0,
    "succ_req#": 0,
    "failed_req#": 0,
    "cum_wait_time": 0
  }
]
JSON member JSON type Meaning and availability
eq_type string Type of enqueue requested
total_req# number Total number of enqueue requests or enqueue conversions for this enqueue type
total_wait# number Total number of times an enqueue request or conversion resulted in a wait
succ_req# number Number of times an enqueue request or conversion was granted
failed_req# number Number of times an enqueue request or conversion failed
cum_wait_time number Total amount of time (milliseconds) spent waiting for the enqueue or conversion

TOP_LIBRARYCACHE_STATS Function

Returns up to ten library-cache namespaces ranked by the analysis-period increase in distributed lock and pin requests.

Purpose

Returns up to ten library-cache namespaces ranked by the analysis-period increase in distributed lock and pin requests. The rows combine local gets, pins, and cache-hit statistics with distributed-lock-manager activity for diagnosis of shared metadata contention. Returns an empty array when the supporting data is unavailable or no rows qualify.

Syntax

DBMS_ADDM_UTIL.TOP_LIBRARYCACHE_STATS
RETURN JSON_ARRAY_T;

Parameters

None.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "namespace": "<namespace>",
    "gets": 0,
    "gethits": 0,
    "pins": 0,
    "pinhits": 0,
    "reloads": 0,
    "invalidations": 0,
    "dlm_lock_requests": 0,
    "dlm_pin_requests": 0,
    "dlm_pin_releases": 0,
    "dlm_invalidation_requests": 0,
    "dlm_invalidations": 0
  }
]
JSON member JSON type Meaning and availability
namespace string Library cache namespace
gets number # lock requested for namespace objects
gethits number # object’s handle found in memory
pins number # PIN requested for namespace objects
pinhits number # metadata pieces of the library object were found in memory
reloads number # object is loaded from disk since the PIN is not the first PIN of the object handle
invalidations number # objects in the namespace were marked invalid because a dependent object was modified
dlm_lock_requests number # GET requests lock instance locks
dlm_pin_requests number # PIN requests lock instance locks
dlm_pin_releases number # release requests PIN instance locks
dlm_invalidation_requests number Distributed-lock-manager invalidation requests during the analysis period
dlm_invalidations number Returns the same local invalidation’s delta as invalidations; it does not report distributed invalidation pings

TOP_ROWCACHE_STATS Function

Returns up to ten dictionary-cache parameters ranked by distributed-lock-manager requests in the analysis period.

Purpose

Returns up to ten dictionary-cache parameters ranked by distributed-lock-manager requests in the analysis period. Use the rows to compare cache usage, lookup misses, scans, modifications, and cross-instance coordination. The parameter member identifies a row-cache category, not an initialization-parameter value.

Syntax

DBMS_ADDM_UTIL.TOP_ROWCACHE_STATS
RETURN JSON_ARRAY_T;

Parameters

None.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "parameter": "<parameter>",
    "total_usage": 0,
    "usage": 0,
    "gets": 0,
    "getmisses": 0,
    "scans": 0,
    "scanmisses": 0,
    "scancompletes": 0,
    "modifications": 0,
    "flushes": 0,
    "dlm_requests": 0,
    "dlm_conflicts": 0,
    "dlm_releases": 0
  }
]
JSON member JSON type Meaning and availability
parameter string Dictionary-cache category/parameter name identifying this summary row.
total_usage number Sum of total number of entries in cache
usage number # cache entries that contain valid data
gets number # info requests on the data object
getmisses number # data requests resulting in cache misses
scans number # scan requests
scanmisses number # scan failed to find data in the cache
scancompletes number # list was scanned completely (for a list of subordinate entries
modifications number # inserts, updates, and deletions
flushes number # flushed to disk
dlm_requests number # DLM requests
dlm_conflicts number # DLM conflicts
dlm_releases number # DLM releases

TIME_STAT Function

Returns the beginning and ending values of a named time-model statistic for the analysis period.

Purpose

Returns the beginning and ending values of a named time-model statistic for the analysis period. Values are in microseconds, and TIME_STAT_VALUE provides the non negative delta for a cumulative statistic. A missing individual statistic returns init: false and zero endpoints; failure to acquire required time-model data can raise ORA-13711.

Syntax

DBMS_ADDM_UTIL.TIME_STAT (
    stat_name IN VARCHAR2)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "init": true,
  "begin_value": 0,
  "end_value": 0
}
JSON member JSON type Meaning and availability
init boolean True if begin/end values have been initialized, else False
begin_value number Stat value from begin snapshot
end_value number Stat value from end snapshot

An unavailable individual statistic returns {"init": false, "begin_value": 0, "end_value": 0}. Endpoints for time-model statistics are microseconds.

TIME_STAT_VALUE Function

Returns the non negative delta for a cumulative time-model statistic in microseconds.

Purpose

Returns the non negative delta for a cumulative time-model statistic in microseconds. It is equivalent to applying STAT_DIFF to TIME_STAT and therefore returns zero for a missing individual statistic. Use TIME_STAT when you need to distinguish missing data from zero measured time.

Syntax

DBMS_ADDM_UTIL.TIME_STAT_VALUE (
    stat_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode:IN.

WAIT_EVENT Function

Returns the selected event’s foreground and combined foreground/background waits, wait time, and ASH sample counts.

Purpose

Returns the selected event’s foreground and combined foreground/background waits, wait time, and ASH sample counts. The object also identifies the event and describes its three wait parameters. A missing event returns the requested name, an ID of zero, empty/null metadata, and zero counters; reading an existing event also records it as handled by rule evaluation.

Syntax

DBMS_ADDM_UTIL.WAIT_EVENT (
    stat_name IN VARCHAR2)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "name": "<name>",
  "id": 0,
  "p1_text": "<p1_text>",
  "p2_text": "<p2_text>",
  "p3_text": "<p3_text>",
  "class": "<class>",
  "waits_fg": 0,
  "time_fg": 0,
  "ash_fg": 0,
  "waits_total": 0,
  "time_total": 0,
  "ash_total": 0
}
JSON member JSON type Meaning and availability
name string Name of the wait event (64 chars)
id number wait event ID. 0 in case wait event is not found in AWR for requested analysis period.
p1_text string Description of P1 (64 chars)
p2_text string Description of P2 (64 chars)
p3_text string Description of P3 (64 chars)
class string wait class the event belongs to
waits_fg number Count of Foreground waits for the event
time_fg number Total Foreground time spent waiting for event Unit: microseconds.
ash_fg number Count of Foreground ASH rows for the event
waits_total number Count of total waits for the event
time_total number Total time spent waiting for the event Unit: microseconds.
ash_total number Count of total ASH rows for the event

WAIT_EVENTS Function

Returns a JSON object keyed by the requested wait-event names, with one WAIT_EVENT object per key.

Purpose

Returns a JSON object keyed by the requested wait-event names, with one WAIT_EVENT object per key. The overload with EVENTS_TOTAL also calculates six aggregate counters; it does not sum event IDs or copy descriptive fields into the total. Use unique event names to avoid double-counting totals. A NULL input array returns SQL NULL; an empty array returns an empty object.

Syntax

DBMS_ADDM_UTIL.WAIT_EVENTS (
    stat_names IN JSON_ARRAY_T)
RETURN JSON_OBJECT_T;
DBMS_ADDM_UTIL.WAIT_EVENTS (
    stat_names IN JSON_ARRAY_T,
    events_total OUT JSON_OBJECT_T)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAMES JSON_ARRAY_T Not specified Yes Array of wait-event names, each at most 64 bytes. Use unique names when requesting sums. Mode: IN.
EVENTS_TOTAL JSON_OBJECT_T Not specified No Aggregate object returned by the two-argument overload: sums waits_fg, time_fg, ash_fg, waits_total, time_total, and ash_total. Descriptive fields and event IDs are not included. Mode: OUT. Default: Not applicable

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "<event name>": {
    "name": "<name>",
    "id": 0,
    "p1_text": "<p1_text>",
    "p2_text": "<p2_text>",
    "p3_text": "<p3_text>",
    "class": "<class>",
    "waits_fg": 0,
    "time_fg": 0,
    "ash_fg": 0,
    "waits_total": 0,
    "time_total": 0,
    "ash_total": 0
  }
}
JSON member JSON type Meaning and availability
<event name> object A WAIT_EVENT structure for that requested name
<event name>.name string name of wait event. (64 chars)
<event name>.id number wait event ID. 0 in case wait event is not found in AWR for requested analysis period.
<event name>.p1_text string Description of P1 (64 chars)
<event name>.p2_text string Description of P2 (64 chars)
<event name>.p3_text string Description of P3 (64 chars)
<event name>.class string wait class the event belongs to
<event name>.waits_fg number Count of Foreground waits for the event
<event name>.time_fg number Total Foreground time spent waiting for event Unit: microseconds
<event name>.ash_fg number Count of Foreground ASH rows for the event
<event name>.waits_total number Count of total waits for the event
<event name>.time_total number Total time spent waiting for the event Unit: microseconds
<event name>.ash_total number Count of total ASH rows for the event

The overload’s OUT object contains only the six counters listed below. A NULL input returns before constructing the total, so callers must handle NULL separately.

The two-argument overload also populates EVENTS_TOTAL as follows (counter values are illustrative):

{
  "waits_fg": 0,
  "time_fg": 0,
  "ash_fg": 0,
  "waits_total": 0,
  "time_total": 0,
  "ash_total": 0
}

Time totals are microseconds. Counts are summed for every array entry, even when duplicate names replace the same map key.

WAIT_EVENT_FG Function

Returns one wait event’s foreground wait time in microseconds.

Purpose

Returns one wait event’s foreground wait time in microseconds. Use it when a rule needs only impact and not the event’s descriptive fields or wait counts. It shares the missing-event and acquisition behavior of WAIT_EVENT, including zero for an absent individual event.

Syntax

DBMS_ADDM_UTIL.WAIT_EVENT_FG (
    stat_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAME VARCHAR2 Not specified Yes Exact statistic name. The maximum length is 64 bytes. Mode: IN.

WAIT_EVENTS_FG Function

Adds the foreground wait times of the requested events and returns the sum in microseconds.

Purpose

Adds the foreground wait times of the requested events and returns the sum in microseconds. It is a convenience for a rule that covers several related waits. Supply unique names so repeated entries do not add the same event’s time more than once.

Syntax

DBMS_ADDM_UTIL.WAIT_EVENTS_FG (
    stat_names IN JSON_ARRAY_T)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
STAT_NAMES JSON_ARRAY_T Not specified Yes Array of wait-event names, each at most 64 bytes. Use unique names when requesting sums. Mode: IN.

WAIT_CLASS Function

Returns aggregate foreground and total wait statistics for a wait class.

Purpose

Returns aggregate foreground and total wait statistics for a wait class. Use it to diagnose a broad category before drilling into individual events. A class absent from the analysis returns the requested class name and zero counters; missing required wait-statistic data can raise an error.

Syntax

DBMS_ADDM_UTIL.WAIT_CLASS (
    class_name IN VARCHAR2)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
CLASS_NAME VARCHAR2 Not specified Yes Wait-class name, at most 64 bytes. Use a WAIT_CLASS_* constant or its exact string value. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "class": "<class>",
  "waits_fg": 0,
  "time_fg": 0,
  "ash_fg": 0,
  "waits_total": 0,
  "time_total": 0,
  "ash_total": 0
}
JSON member JSON type Meaning and availability
class string Wait class name requested (64 chars)
waits_fg number Count of Foreground waits for wait class
time_fg number Foreground time spent waiting for wait class Unit: microseconds
ash_fg number Count of Foreground ASH rows for wait class
waits_total number Count of all waits for wait class
time_total number Total time spent waiting for wait class Unit: microseconds.
ash_total number Count of all ASH rows for wait class

WAIT_CLASS_FG Function

Returns the aggregate foreground wait time for a wait class in microseconds.

Purpose

Returns the aggregate foreground wait time for a wait class in microseconds. This is the scalar counterpart of WAIT_CLASS and is useful for calculating a broad rule’s impact. An absent individual class contributes zero time.

Syntax

DBMS_ADDM_UTIL.WAIT_CLASS_FG (
    class_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
CLASS_NAME VARCHAR2 Not specified Yes Wait-class name, at most 64 bytes. Use a WAIT_CLASS_* constant or its exact string value. Mode: IN.

Rule Helper Functions and Templates

These helpers convert measured cost, apply thresholds, and attribute activity using ASH.

These helpers convert measured cost, apply thresholds, and attribute activity using ASH. Template evaluators pass a JSON rule context through nested findings, recommendations, actions, and rationales. Each API documents whether it returns an object/array, serializes a final result, or mutates a supplied construct and returns a Boolean success flag.

ADD_INSTANCE_BREAKDOWN Procedure

Adds an ASH-based instance_breakdown to a finding in multi-instance analysis when its impact definition can be mapped to ASH.

Purpose

Adds an ASH-based instance_breakdown to a finding in multi-instance analysis when its impact definition can be mapped to ASH. Each row gives an instance’s share of impact, database time, and average active sessions. It leaves the finding unchanged for a single-instance run or when the definition/data cannot support a breakdown. No local-task fallback is available.

Syntax

DBMS_ADDM_UTIL.ADD_INSTANCE_BREAKDOWN (
    finding IN OUT NOCOPY JSON_OBJECT_T);

Parameters

Parameters Type Value Range Required Description Notes
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. Use NEW_FINDING to construct it before calling mutation helpers. Mode: IN OUT NOCOPY.

When attribution succeeds, the finding gains the following member. Values below are illustrative:

{
  "instance_breakdown": [
    {
      "instance_number": 1,
      "perc_active_sessions": 60,
      "database_time": 600000,
      "active_sessions": 0.01
    }
  ]
}

perc_active_sessions is the instance’s percentage of the finding’s impact, database_time is that share in microseconds, and active_sessions divides its database time in seconds by the analysis duration in seconds. This helper’s ASH call inherits the default maximum of five groups.

ADD_TOP_SQL_RTNS Procedure

Finds the top SQL statements associated with the selected impact and adds one supporting rationale per qualifying result.

Purpose

Finds the top SQL statements associated with the selected impact and adds one supporting rationale per qualifying result. The rationale identifies the SQL statement, may include SQL text, and can include its percentage of the filtered impact. When the target is a finding, the rationales are added to its last recommendation; supply a message ID or text template. Only SQL IDs present in the SQL-text cache are included, even when text display is disabled.

Syntax

DBMS_ADDM_UTIL.ADD_TOP_SQL_RTNS (
    target IN OUT NOCOPY JSON_OBJECT_T,
    impact_def IN JSON_OBJECT_T,
    rtn_msg_id IN NUMBER DEFAULT NULL,
    rtn_msg_text IN VARCHAR2 DEFAULT NULL,
    max_results IN NUMBER DEFAULT 5,
    min_rows_filter IN NUMBER DEFAULT 5,
    min_percent_db_time_filter IN NUMBER DEFAULT 2,
    filters IN JSON_OBJECT_T DEFAULT NULL,
    show_sql_text IN BOOLEAN DEFAULT TRUE,
    pct_impact_in_msg IN BOOLEAN DEFAULT FALSE);

Parameters

Parameters Type Value Range Required Description Notes
TARGET JSON_OBJECT_T Not specified Yes Recommendation to update, or a finding with at least one recommendation. For a finding the last recommendation receives the rationales. Mode: IN OUT NOCOPY.
IMPACT_DEF JSON_OBJECT_T Not specified Yes Impact-definition object selecting wait events/a wait class or additive/subtractive time-model statistics. Mode: IN.
RTN_MSG_ID NUMBER Not specified No SMG message ID. The first argument receives SQL_ID; an optional second receives the rounded impact percentage. Takes precedence over RTN_MSG_TEXT. Mode: IN. Default: NULL.
RTN_MSG_TEXT VARCHAR2 Not specified No Text-template alternative for the SQL rationale; %1 receives SQL_ID. Supply this or RTN_MSG_ID. Mode: IN. Default: NULL.
MAX_RESULTS NUMBER Not specified No Maximum number of qualifying ASH groups to return or turn into rationales. The default is 5. Mode: IN. Default: 5.
MIN_ROWS_FILTER NUMBER Not specified No Minimum ASH samples per group, combined with the percentage threshold and rounded up to a whole sample count. Default 5. Mode: IN. Default: 5.
MIN_PERCENT_DB_TIME_FILTER NUMBER Not specified No Minimum percentage of the entire analysis’s foreground ASH samples required per group. Combined with MIN_ROWS_FILTER using the larger threshold; default 2. Mode: IN. Default: 2.
FILTERS JSON_OBJECT_T Not specified No Object of ASH-column/value equality predicates. Keys are validated column names (normally uppercase), values are strings or numbers, and predicates are combined with AND. Mode: IN. Default: NULL.
SHOW_SQL_TEXT BOOLEAN Not specified No Whether the related SQL object includes cached SQL text. The helper still requires a top-SQL text-cache entry even when FALSE. Mode: IN. Default: TRUE.
PCT_IMPACT_IN_MSG BOOLEAN Not specified No When TRUE on the SMG message-ID path, supplies rounded percent_impact as the second argument after SQL_ID. The text-template path supplies only SQL_ID. Mode: IN. Default: FALSE.

ADD_TOP_OBJ_RTNS Procedure

Finds the top database objects associated with the selected impact and adds rationales describing them.

Purpose

Finds the top database objects associated with the selected impact and adds rationales describing them. It uses the full object type, owner, and name when metadata is available, with an ID-only message as a fallback. Provide the message variants needed for both cases so useful ASH evidence can still be reported when object names are unavailable. The procedure requires both the name and ID-only message variants (an ID or text for each) before doing any work.

Syntax

DBMS_ADDM_UTIL.ADD_TOP_OBJ_RTNS (
    target IN OUT NOCOPY JSON_OBJECT_T,
    impact_def IN JSON_OBJECT_T,
    msg_id_by_id IN NUMBER DEFAULT NULL,
    msg_text_by_id IN VARCHAR2 DEFAULT NULL,
    msg_id_by_name IN NUMBER DEFAULT NULL,
    msg_text_by_name IN VARCHAR2 DEFAULT NULL,
    max_results IN NUMBER DEFAULT 5,
    min_rows_filter IN NUMBER DEFAULT 5,
    min_percent_db_time_filter IN NUMBER DEFAULT 2,
    filters IN JSON_OBJECT_T DEFAULT NULL);

Parameters

Parameters Type Value Range Required Description Notes
TARGET JSON_OBJECT_T Not specified Yes Recommendation to update, or a finding with at least one recommendation. For a finding the last recommendation receives the rationales. Mode: IN OUT NOCOPY.
IMPACT_DEF JSON_OBJECT_T Not specified Yes Impact-definition object selecting wait events/a wait class or additive/subtractive time-model statistics. Mode: IN.
MSG_ID_BY_ID NUMBER Not specified No SMG message ID for the ID-only fallback. Its first argument receives the object ID. Takes precedence over MSG_TEXT_BY_ID. Mode: IN. Default: NULL.
MSG_TEXT_BY_ID VARCHAR2 Not specified No Text-template alternative for the ID-only fallback, with %1 representing the object ID. Mode: IN. Default: NULL.
MSG_ID_BY_NAME NUMBER Not specified No SMG message ID when object metadata is available. Arguments are object type, owner, full object name, and object ID, in that order. Mode: IN. Default: NULL.
MSG_TEXT_BY_NAME VARCHAR2 Not specified No Text-template alternative with %1 object type, %2 owner, %3 full object name, and%4 object ID. Mode: IN. Default: NULL.
MAX_RESULTS NUMBER Not specified No Maximum number of qualifying ASH groups to return or turn into rationales. The default is 5. Mode: IN. Default: 5.
MIN_ROWS_FILTER NUMBER Not specified No Minimum ASH samples per group, combined with the percentage threshold and rounded up to a whole sample count. Default 5. Mode: IN. Default: 5.
MIN_PERCENT_DB_TIME_FILTER NUMBER Not specified No Minimum percentage of the entire analysis’s foreground ASH samples required per group. Combined with MIN_ROWS_FILTER using the larger threshold; default 2. Mode: IN. Default: 2.
FILTERS JSON_OBJECT_T Not specified No Object of ASH-column/value equality predicates. Keys are validated column names (normally uppercase), values are strings or numbers, and predicates are combined with AND. Mode: IN. Default: NULL.

CHECK_EXT_PRIV

APIs that expose protected ADDM data invoke DBMS_ADDM_UTIL.CHECK_EXT_PRIV.

Purpose

APIs that expose protected ADDM data invoke DBMS_ADDM_UTIL.CHECK_EXT_PRIV. The check requires the ADVISOR privilege and SELECT_CATALOG_ROLE; it raises an error when either requirement is not met. Calling this procedure explicitly does not initialize the analysis context or grant privileges.

EVALUATE_ACTION Function

Evaluates an action template and appends successful actions to a recommendation.

Purpose

Evaluates an action template and appends successful actions to a recommendation. It supports applicability, alternatives, command metadata, and ASH drilldowns. Returns TRUE if any action is added; for an ASH template with sum_into_benefit: true, it replaces the recommendation benefit with the sum of the successful groups’ dbtime values.

Syntax

DBMS_ADDM_UTIL.EVALUATE_ACTION (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    rec IN OUT NOCOPY JSON_OBJECT_T,
    act_spec IN JSON_OBJECT_T)
RETURN BOOLEAN;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Current evaluation context. Common members are impact_def, node_cost, ash, dbtime, ash_cnt, and percent_impact, with optional sql or object metadata. IN OUT NOCOPY allows documented context updates. Mode: IN OUT NOCOPY.
REC JSON_OBJECT_T Not specified Yes Recommendation object. At finding construction this is an optional initial recommendation; mutation helpers require an existing recommendation. Mode: IN OUT NOCOPY.
ACT_SPEC JSON_OBJECT_T Not specified Yes Action template with message; optional object, command,param_name, from_value orto_value argument specifications, applicable, options, top_from_ash, andsum_into_benefit. Mode: IN.

The mutation appends the NEW_REC Function action structure to rec.actions. Parameter templates use param_name, from_value, and to_value; the last two are argument specifications evaluated by EVALUATE_ARGUMENT.

EVALUATE_APPLICABLE_SPEC Function

Checks the specification’s applicable array against the analysis metadata and returns FALSE for a null specification.

Purpose

Checks the specification’s applicable array against the analysis metadata and returns FALSE for a null specification. The conditions member is accepted but currently has no effect; callers must not rely on it to enforce context conditions. RULE_CONTEXT is not modified.

Syntax

DBMS_ADDM_UTIL.EVALUATE_APPLICABLE_SPEC (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    spec IN JSON_OBJECT_T)
RETURN BOOLEAN;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Context supplied for applicability evaluation. Not modified by this function; conditions currently always pass. Mode: IN OUT NOCOPY.
SPEC JSON_OBJECT_T Not specified Yes Construct specification with optional applicable and conditions members.NULL returns FALSE; conditions are currently not enforced. Mode: IN.

EVALUATE_ARGUMENT Function

Resolves one template argument into a string from a constant, rule context, nested ASH/SQL/object data, a database initialization parameter, or a system statistic.

Purpose

Resolves one template argument into a string from a constant, rule context, nested ASH or SQL object data, a database initialization parameter, or a system statistic. The function requires source and name in ARG_SPEC; name, not value, identifies the selected member or literal. Returns NULL when a source or value cannot be resolved.

Syntax

DBMS_ADDM_UTIL.EVALUATE_ARGUMENT (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    arg_spec IN JSON_OBJECT_T)
RETURN VARCHAR2;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Current evaluation context. Common members areimpact_def, node_cost, ash, dbtime, ash_cnt, andpercent_impact, with optional sql orobject metadata. IN OUT NOCOPY allows documented context updates. Mode: IN OUT NOCOPY.
ARG_SPEC JSON_OBJECT_T Not specified Yes Argument object with source and name, plus optional digits. Constants use name as the literal string; numeric context values are rounded to digits (default 2). Mode: IN.

Supported argument forms:

source name identifies Behavior
A string beginning with const Literal text Returns name directly.
context Top-level context member Reads a string or number; numeric values are rounded.
ash, sql, object, plsql, var Member of that nested context object ASH names are uppercased. The other names are used as supplied.
A string beginning with param and ending in min Initialization-parameter name Returns its historicalmin_value.
Any other source beginning with param Initialization-parameter name Returns its historical max_value.
A source matching sys%stat System-statistic name Returns SYS_STAT_VALUE as text.

For example:

{"source": "ash", "name": "SQL_ID"}

digits optionally controls rounding. A string value with digits specified must convert to a number; missing values, unsupported sources, and failed conversions return NULL. Availability of a context source does not imply that EVALUATE_TOP_FROM_ASH populates it; in particular, that helper does not populate plsql.

EVALUATE_FINDING Function

Evaluates a finding template into an array of zero or more finding objects.

Purpose

Evaluates a finding template into an array of zero or more finding objects. A basic template needs a name and message; top_from_ash can generate a finding for each qualifying context, and recommendations are evaluated within that context. Impact comes from context dbtime or node_cost. With sum_into_node_cost, this function can update RULE_CONTEXT.node_cost.

Syntax

DBMS_ADDM_UTIL.EVALUATE_FINDING (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    fdg_spec IN JSON_OBJECT_T)
RETURN JSON_ARRAY_T;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Context for finding impact and argument resolution. Can be updated at node_cost whensum_into_node_cost istrue for an ASH drilldown. Mode: IN OUT NOCOPY.
FDG_SPEC JSON_OBJECT_T Not specified Yes Finding template with name andmessage; optional object, recommendations, top_from_ash, applicable, options, andsum_into_node_cost. Mode:IN.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "name": {
      "msg_text": "Application waits"
    },
    "message": {
      "msg_text": "Application waits consumed significant database time."
    },
    "impact_definition": {
      "wait_class": "Application"
    },
    "impact": 1000000,
    "type": "SYMPTOM"
  }
]
JSON member JSON type Meaning and availability
[item] object A complete finding object, with the NEW_FINDING structure.

Returns [] when no finding is produced. Each item can contain optional objects and recommendations, including the nested action or rationale structures documented under NEW_FINDING and NEW_REC. Additional information is currently read from mode_info only when more_info is present; a template containing only more_info therefore does not populate the finding’s additional information.

EVALUATE_IMPACT Function

Calculates impact in microseconds from a wait or time-model definition and limits the result to the range from zero through the analysis period’s database time.

Purpose

Calculates impact in microseconds from a wait or time-model definition and limits the result to the range from zero through the analysis period’s database time. For waits it sums foreground time and subtracts base_latency multiplied by the number of waits; for time-model definitions it subtracts the selected subtractive counters from the additive counters. Wait definitions take precedence when both kinds of members are present.

Syntax

DBMS_ADDM_UTIL.EVALUATE_IMPACT (
    impact_def IN JSON_OBJECT_T)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
IMPACT_DEF JSON_OBJECT_T Not specified Yes Impact-definition object selecting wait events or a wait class or additive or subtractive time-model statistics. Mode: IN.

EVALUATE_MESSAGE Function

Evaluates a message specification into a message group.

Purpose

Evaluates a message specification into a message group. Numeric specifications select an SMG message, strings provide text, arrays of object-form specifications concatenate messages, and an options array tries alternatives in order until one succeeds. Object-form arguments are resolved through EVALUATE_ARGUMENT; an unresolved required argument or inapplicable message can produce SQL NULL.

Syntax

DBMS_ADDM_UTIL.EVALUATE_MESSAGE (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    message_spec IN JSON_ELEMENT_T)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Context used to resolve arguments and applicability. This function does not modify the context. Mode: IN OUT NOCOPY.
MESSAGE_SPEC JSON_ELEMENT_T Not specified Yes JSON number (SMG ID), string (text), object (message specification or options), orarray of object-form message specifications to concatenate.Object-formargs usesource orname and optional digits orredact. Mode: IN.

Returned JSON structure

The return is a JSON_OBJECT_T.

{
  "msg_group": [
    {
      "msg_text": "Object %1 was observed.",
      "p1": "ORDERS",
      "r1": 1
    }
  ],
  "msg_text": "Object ORDERS was observed."
}
JSON member JSON type Meaning and availability
msg_group array Ordered text-message definitions.
msg_group[].msg_text string Text template containing %1 through%5 placeholders.
msg_group[].p1 string Optional substitution argument 1.
msg_group[].r1 number Optional redaction level for p1; 0 or NULL meansnone, 1 means user, 2 means system, 3 means both.
msg_group[].p2 string Optional substitution argument 2.
msg_group[].r2 number Optional redaction level for p2; 0 or NULL meansnone, 1 means user, 2 means system, 3 means both.
msg_group[].p3 string Optional substitution argument 3.
msg_group[].r3 number Optional redaction level for p3; 0 or NULL means none, 1 means user, 2 means system, 3 means both.
msg_group[].p4 string Optional substitution argument 4.
msg_group[].r4 number Optional redaction level for p4; 0 or NULL means none, 1 means user, 2 means system, 3 means both.
msg_group[].p5 string Optional substitution argument 5.
msg_group[].r5 number Optional redaction level for p5;0 or NULL means none, 1 means user, 2 means system, 3 means both.
msg_text string Rendered text of the complete message group.

The example shows the text-template branch. SMG branches instead use msg_id as shown under MSG. A plain string produces a group containing one msg_text component and the rendered msg_text. For concatenation, supply an array of object-form message specifications; each element must be a JSON_OBJECT_T. For text-template arguments, an omitted redact value is written as 0.

EVALUATE_OBJECT Function

Builds a related-object descriptor from values already present in the rule context.

Purpose

Builds a related-object descriptor from values already present in the rule context. The function supports SQL, database object, block, file, tablespace, and enqueue variants. Latch objects are not supported. Returns SQL NULL if the required context is unavailable or the type is unsupported.

Syntax

DBMS_ADDM_UTIL.EVALUATE_OBJECT (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    obj_spec IN JSON_OBJECT_T)
RETURN JSON_OBJECT_T;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Current evaluation context. Common members areimpact_def,node_cost, ash,dbtime, ash_cnt, and percent_impact, with optional sql or object metadata. IN OUT NOCOPY permits documented context updates. Mode: IN OUT NOCOPY.
OBJ_SPEC JSON_OBJECT_T Not specified Yes Object template with a type member. Supported values and required context are listed in the return-structure variants below. Mode: IN.

Returned JSON structures

The return value is one of the following variants, depending on OBJ_SPEC.type:

Type Required context Returned members
SQL context.sql type, sql_id, con_dbid, sql_text. This evaluator does not add plan_hash.
DATABASE OBJECT context.ash.CURRENT_OBJ# type, object_id, con_dbid.
DATABASE BLOCK context.ash.CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK# type, object_id, file_number, block_number, con_dbid.
DATABASE FILE context.ash.CURRENT_FILE# type, file_number, con_dbid, file_name.
TABLESPACE context.object.tablespace type, tablespace_id, name.
DATABASE ENQ An enqueue EVENT in context.ash, or exactly one enqueue event in context.impact_def.wait_events type, code, id1, id2. P2 or P3 supply the identifiers; missing values default to 0. 
{
  "type": "SQL",
  "sql_id": "a2k1zqcbp5nxf",
  "con_dbid": 54321,
  "sql_text": "<SQL text or null when unavailable>"
}
{"type": "DATABASE OBJECT", "object_id": 12345, "con_dbid": 54321}
{
  "type": "DATABASE BLOCK",
  "object_id": 12345,
  "file_number": 7,
  "block_number": 100,
  "con_dbid": 54321
}
{
  "type": "DATABASE FILE",
  "file_number": 7,
  "con_dbid": 54321,
  "file_name": "<file name or null when unavailable>"
}
{"type": "TABLESPACE", "tablespace_id": null, "name": "USERS"}
{"type": "DATABASE ENQ", "code": "TX", "id1": 0, "id2": 0}

Unlike the constructors’ optional container members, the SQL, object, block, file paths explicitly put con_dbid, which can be null. The file variant also contains file_number, unlike FILE_OBJECT. The tablespace path reads tablespace_id from the context, while DB_OBJ_STATS supplies tablespace.id; a context produced directly by that API can therefore yield a null tablespace_id. A latch specification returns NULL.

EVALUATE_RATIONALE Function

Evaluates a rationale template and appends each successful result to the supplied recommendation.

Purpose

Evaluates a rationale template and appends each successful result to the supplied recommendation. It supports applicability, ordered alternatives, and a top_from_ash drilldown that can generate multiple rationales. Returns TRUE when at least one rationale is added and FALSE otherwise; the updated recommendation contains the JSON output.

Syntax

DBMS_ADDM_UTIL.EVALUATE_RATIONALE (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    rec IN OUT NOCOPY JSON_OBJECT_T,
    rtn_spec IN JSON_OBJECT_T)
RETURN BOOLEAN;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Current evaluation context. Common members are impact_def, node_cost, ash,dbtime, ash_cnt, and percent_impact, with optionalsql orobject metadata. IN OUT NOCOPY permits documented context updates. Mode: IN OUT NOCOPY.
REC JSON_OBJECT_T Not specified Yes Recommendation object. At finding construction this is an optional initial recommendation; mutation helpers require an existing recommendation. Mode: IN OUT NOCOPY.
RTN_SPEC JSON_OBJECT_T Not specified Yes Rationale template with message and optional object, applicable, options, or top_from_ash. Mode: IN.

The mutation appends objects with the ADD_RTN Procedure rationale structure to rec.rationales. The Boolean return is a success indicator, not the JSON rationale itself.

EVALUATE_REC Function

Evaluates a recommendation template and appends successful recommendations to the supplied finding.

Purpose

Evaluates a recommendation template and appends successful recommendations to the supplied finding. A recommendation needs a recognized type and at least one successfully evaluated action; rationales are optional. Benefit defaults to context dbtime, falling back to the finding’s impact. Returns whether any recommendation was added, and an ASH template can use sum_into_impact to update the finding’s impact.

Syntax

DBMS_ADDM_UTIL.EVALUATE_REC (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    finding IN OUT NOCOPY JSON_OBJECT_T,
    rec_spec IN JSON_OBJECT_T)
RETURN BOOLEAN;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Current evaluation context. Common members are impact_def, node_cost, ash, dbtime, ash_cnt, and percent_impact, with optional sql or object metadata. IN OUT NOCOPY permits documented context updates. Mode: IN OUT NOCOPY.
FINDING JSON_OBJECT_T Not specified Yes Finding object to consume or update. Use NEW_FINDING to construct it before calling mutation helpers. Mode: IN OUT NOCOPY.
REC_SPEC JSON_OBJECT_T Not specified Yes Recommendation template with a supported type and actions; optional rationales, rank, top_from_ash, applicable, options, and sum_into_impact. Mode: IN.

The mutation appends the NEW_REC Function recommendation structure to finding.recommendations. The Boolean result indicates whether any recommendation was added; the finding’s type and benefit bounds are handled through ADD_REC.

EVALUATE_RULE Function

Evaluates a complete rule template and returns a serialized no-problem or findings result, or SQL NULL for ignore.

Purpose

Evaluates a complete rule template and returns a serialized no-problem or findings result, or SQL NULL for ignore. It checks applicability, evaluates the impact definition (defaulting to all DB time), applies the task significance threshold, and evaluates finding when significant. A missing no-problem message, no successful findings, or an exception produces ignore; this evaluator does not automatically generate a no-data result.

Syntax

DBMS_ADDM_UTIL.EVALUATE_RULE (
    rule_spec IN JSON_OBJECT_T)
RETURN CLOB;

Parameters

Parameters Type Value Range Required Description Notes
RULE_SPEC JSON_OBJECT_T Not specified Yes Complete rule template. Optional applicable and impact; impact defaults to {"time_model":["DB time"]}. no_problem supplies an insignificant-impact message; finding supplies the finding template. Mode:IN.

Returned JSON structure

The return is a CLOB containing the following JSON structure.

{
  "findings": [
    {
      "name": {
        "msg_text": "Application waits"
      },
      "message": {
        "msg_text": "Application waits consumed significant database time."
      },
      "impact_definition": {
        "wait_class": "Application"
      },
      "impact": 1000000,
      "type": "SYMPTOM"
    }
  ],
  "node_cost": 1000000
}
JSON member JSON type Meaning and availability
no_problem object Present only for the no-problem result.
impact number Present with no_problem; evaluated microseconds.
findings array Present only for a findings result; items have the NEW_FINDING structure.
node_cost number Present with findings; evaluated rule impact in microseconds.

The two object forms are mutually exclusive: {"no_problem": <message>, "impact": <number>} or {"findings": [<finding>, ...], "node_cost": <number>}. Ignore is SQL NULL, not JSON. EVALUATE_RULE passes its original evaluated impact as node_cost, even when nested EVALUATE_FINDING updates its local rule_context.node_cost.

EVALUATE_TOP_FROM_ASH Function

Runs a template-defined ASH grouping and returns a new rule-context object for each qualifying group.

Purpose

Runs a template-defined ASH grouping and returns a new rule-context object for each qualifying group. Each context retains prior values, adds ASH grouping values, and derives ash_cnt, dbtime, and percent_impact; available SQL, object, service, and PDB metadata can enrich it further. Existing ash values become filters for nested drilldowns. Returns NULL for an unusable specification, no usable ASH rows, or an evaluation error; a final percentage filter can leave an empty array.

Syntax

DBMS_ADDM_UTIL.EVALUATE_TOP_FROM_ASH (
    rule_context IN OUT NOCOPY JSON_OBJECT_T,
    ash_spec IN JSON_OBJECT_T)
RETURN JSON_ARRAY_T;

Parameters

Parameters Type Value Range Required Description Notes
RULE_CONTEXT JSON_OBJECT_T Not specified Yes Input context containing impact_def and optional prior ash values. The returned array contains enriched clones; the parent context is not replaced. Mode:IN OUT NOCOPY.
ASH_SPEC JSON_OBJECT_T Not specified Yes ASH template with group_by array and optional max_results (5), min_rows (5), pct_dbtime (2), and min_percent_impact (2). Prior context.ash supplies equality filters. Mode: IN.

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "impact_def": {
      "wait_class": "Application"
    },
    "node_cost": 1000000,
    "ash": {
      "SQL_ID": "a2k1zqcbp5nxf",
      "SQL_OPNAME": "SELECT",
      "CON_DBID": 54321
    },
    "ash_cnt": 50,
    "dbtime": 250000,
    "percent_impact": 25,
    "sql": {
      "init": false
    },
    "pdb_name": "<PDB name>"
  }
]
JSON member JSON type Meaning and availability
impact_def object Inherited impact definition.
node_cost number Inherited rule cost in microseconds.
ash object Prior and newly grouped ASHcolumns, using uppercase keys.
ash_cnt number Count for this ASH group.
dbtime number Group count multiplied by analysis DB time / total foreground ASH count, in microseconds.
percent_impact number Percentage of the preceding filtered ASH population.
sql object Optional GET_TOP_SQL structure; can contain only init: false.
object object Optional DB_OBJ_STATS structure.
service_name string Optional resolved service name.
pdb_name string Optional resolved PDB name.

Each row is a clone, so other caller-supplied context members can remain present. Optional sql or object metadata uses the complete schemas documented under GET_TOP_SQL and DB_OBJ_STATS. The plsql member is not populated during enrichment.

IS_SIGNIFICANT Function

Compares the impact’s percentage of database time with the requested significance threshold.

Purpose

Compares the impact’s percentage of database time with the requested significance threshold. The comparison is strictly greater than the threshold, so equality is not significant. If the threshold argument is omitted or null, the EVT_ELPCT_MIN task parameter is used.

Syntax

DBMS_ADDM_UTIL.IS_SIGNIFICANT (
    impact IN NUMBER,
    percentage_threshold IN NUMBER DEFAULT NULL)
RETURN BOOLEAN;

Parameters

Parameters Type Value Range Required Description Notes
IMPACT NUMBER Not specified Yes Measured database time attributable to the diagnosed issue, in microseconds. Mode: IN.
PERCENTAGE_THRESHOLD NUMBER Not specified No Percentage of DB time that impact must strictly exceed. NULL selects the EVT_ELPCT_MIN task parameter. Mode: IN. Default: NULL.

PERCENTAGE_OF_DB_TIME Function

Converts an impact in microseconds to its percentage of the current analysis period’s database time.

Purpose

Converts an impact in microseconds to its percentage of the current analysis period’s database time. Use it when reporting a measured impact or comparing it with a percentage threshold. A null impact is treated as zero before the engine performs the conversion.

Syntax

DBMS_ADDM_UTIL.PERCENTAGE_OF_DB_TIME (
    impact IN NUMBER)
RETURN NUMBER;

Parameters

Parameters Type Value Range Required Description Notes
IMPACT NUMBER Not specified Yes Measured database time attributable to the diagnosed issue, in microseconds. Mode: IN.

TOP_FROM_ASH Function

Groups foreground ASH rows selected by an impact definition and optional equality filters, returning the most frequent groups first.

Purpose

Groups foreground ASH rows selected by an impact definition and optional equality filters, returning the most frequent groups first. Each result contains the grouping columns, a sample count, and its percentage of all rows matching that filtered impact definition. The baseline latency is not subtracted from ASH samples. Low sample coverage or query errors can yield an empty array, while an unsupported column can yield SQL NULL.

Syntax

DBMS_ADDM_UTIL.TOP_FROM_ASH (
    impact_def IN JSON_OBJECT_T,
    group_by_cols IN JSON_ARRAY_T,
    max_results IN NUMBER DEFAULT 5,
    min_rows_filter IN NUMBER DEFAULT 5,
    min_percent_db_time_filter IN NUMBER DEFAULT 2,
    filters IN JSON_OBJECT_T DEFAULT NULL)
RETURN JSON_ARRAY_T;

Parameters

Parameters Type Value Range Required Description Notes
IMPACT_DEF JSON_OBJECT_T Not specified Yes Impact-definition object selecting wait events or a wait class or additive or subtractive time-model statistics. Mode: IN.
GROUP_BY_COLS JSON_ARRAY_T Not specified Yes Non empty array of approved ASH column names, for example ["SQL_ID","CON_DBID"]. Output uses uppercase column names. Rows with null grouping values are excluded from groups. Mode: IN.
MAX_RESULTS NUMBER Not specified No Maximum number of qualifying ASH groups to return or turn into rationales. The default is 5. Mode: IN. Default: 5.
MIN_ROWS_FILTER NUMBER Not specified No Minimum ASH samples per group, combined with the percentage threshold and rounded up to a whole sample count. Default 5. Mode: IN. Default: 5
MIN_PERCENT_DB_TIME_FILTER NUMBER Not specified No Minimum percentage of the entire analysis’s foreground ASH samples required per group. Combined with MIN_ROWS_FILTER using the larger threshold; default 2. Mode: IN. Default: 2
FILTERS JSON_OBJECT_T Not specified No Object of ASH-column/value equality predicates. Keys are validated column names (normally uppercase), values are strings or numbers, and predicates are combined with AND. Mode: IN. Default: NULL

Returned JSON structure

The return is a JSON_ARRAY_T; one representative element is shown.

[
  {
    "SQL_ID": "a2k1zqcbp5nxf",
    "CON_DBID": 54321,
    "cnt": 50,
    "percent_impact": 25
  }
]
JSON member JSON type Meaning and availability
<GROUP_BY column> string or number One member for each requested column, using the uppercase column name.
cnt number Number of foreground ASH rows in this group.
percent_impact number 100 * cnt / all rows matching the impact definition and filters, before the per-group threshold/limit.

The result does not contain dbtime or ash_cnt. To estimate raw sampled database time, multiply cnt by ASH_STATS.time_per_row (microseconds). To apportion an already evaluated impact, multiply that impact by percent_impact / 100. These estimates differ when a baseline or custom impact calculation is involved. The percentage filter parameter uses total analysis DB time, while returned percent_impact uses the filtered population.