Oracle DBA Lesson 96 Part 1 — Validate an advisor finding

An advisor recommendation becomes useful when you can explain which work it should reduce, which measurement would test that explanation, and how an unsuccessful change would be reversed. Start with the affected workload and the report's scope. Then use the finding as a reason to investigate a particular mechanism.

Oracle DBA Lesson 96 Part 1 — Validate an advisor finding

What ADDM analyzes

The Automatic Database Diagnostic Monitor, or ADDM, analyzes AWR performance data over a snapshot interval. It identifies significant contributors to database time and can provide findings, recommendations, actions, and rationales. Database time includes foreground CPU time and non-idle foreground waits across sessions. It is not the same quantity as a user's end-to-end response time.

Read these parts together:

Report item	Practical interpretation
Finding	The performance issue, symptom, information or warning identified by the analysis
Impact	An estimated portion of database time associated with a problem finding
Recommendation	A proposed way to reduce the issue, supported by actions and rationale
Benefit	An estimated portion of database time that could be saved by implementing that recommendation
Warning	A condition, such as missing data, that can affect the completeness or accuracy of the analysis

A completed analysis can still contain warnings and can describe workload that differs from the complaint. Review both its scope and its rationale.

Read impact and benefit

Suppose the analyzed interval contains 2,000 seconds of database time. A problem finding has 60% impact, or 1,200 seconds. A recommendation has 25% estimated benefit, or 500 seconds of that interval's database time.

The benefit estimate is not a promise that the batch will finish 25% faster. Its denominator is the analyzed database time, which can include concurrent work from other services. Application queues, network time, and client work can also affect response time. Measure the batch's elapsed time and the relevant database work after a test.

Multiple findings can include the same work. An I/O-heavy SQL finding and a cache-related finding can overlap, so their impacts may sum to more than 100%. Recommendations for one problem may also be alternatives. Adding their benefits does not establish a combined saving. Read each action and rationale, select one test, and evaluate the actual outcome.

See Automatic Performance Diagnostics.

Diagnostics Pack and Tuning Pack

ADDM, AWR analysis, DBMS_ADDM, ADDM reports, and ADDM-related rows in DBA_ADVISOR_ views require Oracle Diagnostics Pack entitlement. Querying a view or enabling a feature does not establish contractual entitlement. The advisor APIs require the ADVISOR privilege. A follow-up that uses SQL Tuning Advisor also requires Tuning Pack rights. An ADDM recommendation does not grant those rights.

See licensing information and DBMS_ADVISOR.

Find the task and report

This query lists the five newest completed ADDM tasks in the task catalog:

SELECT owner, task_name, status, created
FROM dba_advisor_tasks
WHERE advisor_name = 'ADDM'
  AND status = 'COMPLETED'
ORDER BY created DESC FETCH FIRST 5 ROWS ONLY;

OWNER and TASK_NAME identify the task in its owner context. Task names are unique to their owner, rather than globally unique across every owner. STATUS = 'COMPLETED' means the analysis operation completed. CREATED is the task creation date. It does not establish the incident's snapshot interval. The newest task is only a candidate for review. Incomplete or failed tasks require separate examination of their status and messages.

For an existing executed task, the documented report interface is:

VARIABLE task_name VARCHAR2(128)
EXEC :task_name := '<ACCESSIBLE_EXECUTED_TASK_NAME>';
SET LONG 1000000 PAGESIZE 0 LINESIZE 120
SELECT DBMS_ADDM.GET_REPORT(:task_name) FROM dual;

Replace the placeholder with the task name in its owner context. GET_REPORT returns a text report as a CLOB. SET LONG controls how much SQL*Plus displays. Preserve the report header and warnings with the finding. Do not infer cross-owner access from the catalog query alone.

This SQL*Plus script is the entry point for an analysis over selected snapshots and for report generation:

@?/rdbms/admin/addmrpt.sql

? denotes the Oracle home used by SQL*Plus. The script does not apply the advisor's tuning recommendation. Review the installed script and its prompts in that Oracle home. A manually requested analysis can create an advisor task and a report file. Displaying an existing report does not create a task or a report file. Prefer the existing-report path when no new analysis is needed.

See DBMS_ADDM and DBA_ADVISOR_TASKS.

Verify that the report covers the complaint

For a cache-sizing finding, use an instance report from the container database root. In Oracle Database 19c, PDB-level ADDM can provide PDB-specific findings when it has appropriate local AWR data, but it excludes CDB-wide issues such as an undersized buffer cache. A root report is not automatically a service-specific diagnosis. Confirm that the affected service and workload are represented.

Record:

• Database identifier, instance, and container scope, with the relevant restart identity.
• Begin and end snapshot identifiers, their actual timestamps and time zone, and whether they cover the slow batch.
• Startup continuity and collection completeness. Do not use an unsupported interval across a restart.
• The affected service, SQL, or batch activity and its contribution during the interval.
• Warnings, missing captures, and any scope limitation that could change the conclusion.

A task's creation date and a matching database name are insufficient. If the report omits the affected workload, or cannot support the proposed mechanism, obtain suitable evidence before deciding on a change.

Turn the recommendation into competing hypotheses

Suppose the report highlights I/O-heavy batch work and recommends evaluating a larger cache. Consider at least two plausible explanations:

Hypothesis	Evidence that would distinguish it
The access path reads many unnecessary blocks to find relatively few required rows	Required rows and predicate selectivity, actual execution plan, logical reads and table-access work
Useful blocks are repeatedly read because the cache cannot retain them	Reuse pattern, physical reads, I/O time and the effect of an appropriately controlled cache-size test

Storage latency, concurrency, plan changes, or a different input volume may also explain the observed time. A high I/O total alone does not select a remedy.

A full table scan can be efficient when the statement needs a large portion of the table. For a selective query, an appropriate index may reduce work, but index traversal, table lookups, clustering, selectivity, and competing workload affect the result. Test the actual plan and the total work. Do not decide from the words TABLE ACCESS FULL alone.

The database buffer cache is part of the SGA. It can retain reusable data blocks and reduce repeated physical reads from data files on disk. A larger cache can help when the workload reuses blocks that otherwise age out. Some operations, including direct-path reads, bypass the buffer cache. Increasing that cache does not automatically improve every scan. Physical I/O counts, I/O latency, logical reads, and elapsed time answer different questions and should be interpreted together.

A cache change also needs a resource review. Automatic shared memory management may already adjust the component. Available memory and the other SGA components constrain a useful change. The finding does not prescribe a cache parameter value.

See optimizer access paths, tuning the database buffer cache, and memory architecture.

Plan one measured, reversible test

For a selective statement, one proposed hypothesis is that an appropriate index will reduce unnecessary table reads for this batch. That statement is a test proposal, not an observed conclusion.

Write the change record before executing anything:

Field	Planning entry
Workload coverage	The affected batch and representative concurrent application work
Controlled inputs	Same data distribution, binds, input volume, execution count and relevant cache conditions
Proposed change	One index or one cache configuration change, selected from the evidence
Expected mechanism	Fewer unnecessary block accesses, or fewer rereads of useful blocks
Measurements	Batch elapsed time, logical and physical reads, I/O time, actual plan and application correctness and effects
Acceptance criteria	An agreed improvement and no unacceptable regression
Backout	A tested reversal specific to the changed object or configuration
Decision	Keep, revise or reverse after comparing the measurements

A baseline batch elapsed time of 600 seconds and a target of at most 480 seconds is a 20% reduction. Also require correct results and an agreed limit on regressions in representative DML or other important work. Meeting a single elapsed-time target is insufficient if correctness or other workload performance deteriorates.

Use representative repeats and account for cache warming, noise, concurrency, and reset boundaries. Do not flush a shared cache to manufacture comparable conditions.

For an index test, backout removes only the uniquely named index created for that test, after you confirm ownership and consequences. Index DDL has commit and resource effects. For a cache test, record the original configuration, the automatic-memory-management relationships, and the restoration procedure. Reversing a setting does not undo completed business transactions or recreate the earlier cache contents. Verify the post-backout state and the application behavior.

Recap

Match the ADDM report to the complaint: database, instance, container, snapshot interval, and warnings. Read impact and benefit as estimates of analyzed database time. Those estimates can overlap, and a benefit percentage does not promise the same reduction in batch elapsed time. Separate an access-path problem from a cache-retention problem, then test one change against comparable inputs, stated measurements, acceptance criteria, and a specific backout.

Quiz

1. If ADDM recommends more cache, does that prove every associated full scan should use an index?

Yes; every full scan is inefficient. Yes; an index always reduces physical reads. No; check required rows, actual plan and I/O before choosing a remedy. No; full scans can never be changed.

2. A finding has 60% impact and a recommendation has 25% benefit. What does the benefit describe?

A guaranteed 25% reduction in application latency. An estimate of the analyzed database time that could be saved by the recommendation. Exactly 25% of the finding's impact in every report. An instruction to implement the change immediately.

3. What does the newest completed task's creation date establish?

The exact start of the incident. That every service was slow. That the analysis operation completed and when the task was created, while its report scope still needs review. That the task belongs to the current user.

4. Where is the database buffer cache, and when might increasing it help?

In physical data files; it helps every direct-path scan. In the PGA; it replaces an index. In the SGA; it can help retain reusable blocks and reduce rereads when capacity and the access pattern justify it. In the client; it guarantees a fixed response time.

5. What makes a proposed change ready for a controlled test?

A high impact percentage alone. A copied recommendation without workload context. A hypothesis, comparable workload, measurements, acceptance criteria, and a specific backout. A successful ADDM task with no application measurements.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily