Oracle DBA

Automatic Database Diagnostic Monitor (ADDM) analysis is now available for pluggable databases (PDBs) in Oracle 19c. ADDM at the PDB level enables targeted performance analysis and tuning for individual PDBs in a multitenant environment. The instructions below explain how ADDM works for PDBs, the required configuration steps to enable PDB-level ADDM, the important restrictions to be aware of, and a hands-on demo showing the feature in action.

---

How ADDM for PDBs works

• ADDM runs when AWR snapshots are taken. In a multitenant environment, ADDM analysis is performed separately for the CDB root and for each PDB that has automatic AWR snapshots enabled.
• ADDM results are stored in the same container where the snapshot is taken. ADDM results generated on a PDB are visible only when your current container is that PDB. ADDM results generated on the CDB root are visible only from the root.
• PDB-level ADDM reports focus on PDB-specific findings and recommendations. Issues that apply to the CDB as a whole (for example, buffer cache sizing or cluster messaging) are not reported in PDB-level ADDM. Enabling AWR snapshots in a PDB does not change the CDB root’s ADDM reports.

---

PDB-level ADDM restrictions (important)

ADDM running in a PDB will not report issues that are global to the CDB or cannot be resolved at the PDB level. Examples of items excluded from PDB ADDM findings:

• I/O problems caused by undersized buffer cache, excessive checkpoint/temp/undo writes, I/O capacity limits, etc.
• SQL hard parsing issues due to cursor aging or out-of-memory failed parse.
• SGA sizing issues.
• Cluster messaging issues (network latency, congestion, lost blocks).
• Log file switch waits on archiving or checkpoint incomplete.
• Too many free-buffer waits, log buffer contention, CPU bottleneck waits, OS VM paging.
• Certain RMAN-related waits (PQ queued waits, PGA limit wait, I/O queue waits).

If the performance issue is controlled by CDB-level resources or configuration, ADDM in the PDB will not raise those findings. Evaluate both PDB-level ADDM and root-level ADDM when diagnosing problems that may span containers.

---

Enabling ADDM for a PDB (step-by-step)

By default automatic AWR snapshots are disabled in a PDB. To enable ADDM for a PDB you must:

1. Enable automatic AWR snapshot flushing in the PDB: set AWR_PDB_AUTOFLUSH_ENABLED = TRUE.
2. Configure the AWR snapshot interval (greater than 0).
3. Create at least two AWR snapshots in the PDB (ADDM analyzes the period defined by the last two snapshots).

Execute the following on the CDB root as a privileged user and switch into the target PDB:

-- connect as SYS or a user with SYSDBA
$ export ORACLE_SID=your_cdb
$ sqlplus / as sysdba

-- open PDBs (if needed)
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

-- switch to the target PDB
SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;

Enable AWR snapshots and set the snapshot interval:

-- check current setting
SQL> SHOW PARAMETER AWR_PDB_AUTOFLUSH_ENABLED;

-- enable automatic AWR snapshot flush in the PDB
SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED = TRUE;

-- set AWR snapshot interval (example: 60 minutes)
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60);

Create two snapshots to produce data for ADDM:

-- create two snapshots with some time between them
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
-- wait a short while (or perform workload)
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Now ADDM will run automatically after the second snapshot and the report will be stored in the PDB AWR repository.

---

Generating and viewing an ADDM report for a PDB

You can generate an ADDM report using the supplied SQL/PLSQL scripts or Enterprise Manager. From SQL*Plus in the PDB container:

-- run the ADDM utility script (interactive)
SQL> @?/rdbms/admin/addmrpt.sql

addmrpt.sql prompts for the snapshot range and report name. Example interactive selections:

Specify the Begin and End Snapshot Ids
Enter value for begin_snap: 2
Enter value for end_snap:   4
Enter value for report_name:  -- press Enter to accept default

ADDM report generated and written to addmrpt_<inst>_<begin>_<end>.txt

Alternatively, programmatic access to ADDM findings is available through views such as DBA_ADDM* and DBA_ADVISOR* in the PDB.

---

Demo (concise lab flow)

1. Open CDB and PDBs:

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
SQL> ALTER SESSION SET CONTAINER = DBWRPDB;
SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;

2. Confirm AWR_PDB_AUTOFLUSH_ENABLED is FALSE initially, then enable it:

SQL> SHOW PARAMETER AWR_PDB_AUTOFLUSH_ENABLED;
SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED = TRUE;

3. Set snapshot interval and create snapshots:

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60);
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
-- generate workload (optional)
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

4. Generate ADDM report:

SQL> @?/rdbms/admin/addmrpt.sql
-- follow prompts: choose snapshot IDs and report file name

5. Review the ADDM output file—typical sections include Analysis Period, Activity, Summary of Findings, and Recommendations.

---

Sample ADDM findings (illustrative)

A generated PDB-level ADDM report typically contains:

• Analysis period (begin/end snapshot times)
• Activity summary (DB time, active sessions)
• Top findings by impact with recommendations (SQL tuning, PL/SQL tuning, I/O wait classes)
• Estimated benefits and suggested actions (e.g., run SQL Tuning Advisor, tune PL/SQL entry points)

Example snippet from a PDB ADDM output:

Finding 1: Top SQL Statements
Impact: 0.37 active sessions (100% of activity)
Recommendation: Run SQL Tuning Advisor for SQL_ID 711q7nyk6g2y2

Finding 2: PL/SQL Execution
Impact: 0.1 active sessions (28.07% of activity)
Recommendation: Tune PL/SQL entry point ID 15115

---

Useful views and packages

• DBA_ADDM_REPORTS, DBA_ADDM_SUMMARY — access ADDM reports and summaries in the PDB.
• DBA_HIST_WR_CONTROL — shows AWR snapshot configuration and snapshot interval per source (PDB and CDB root).
• DBMS_WORKLOAD_REPOSITORY — API to manage snapshots (CREATE_SNAPSHOT, MODIFY_SNAPSHOT_SETTINGS).

---

Operational guidance and best practices

• Enable PDB-level ADDM only when you intend to perform PDB-specific tuning and you have adequate recovery area space for AWR snapshots.
• Use PDB ADDM together with root-level ADDM when investigating performance issues that might involve CDB-level resources.
• Export critical PDB-level AWR/ADDM reports and retain them with incident records for audit and historical performance comparisons.
• Automate snapshot creation for long-running tests and ensure snapshot intervals align with the workload characteristics you want to analyze.
• When tuning based on ADDM findings in a PDB, validate whether recommendations require CDB-level actions or PDB-local changes.

---

Troubleshooting

• If AWR_PDB_AUTOFLUSH_ENABLED cannot be set in a PDB, ensure you are connected to the PDB container (use ALTER SESSION SET CONTAINER = <PDB>).
• If snapshots are not created for the PDB, verify DB_RECOVERY_FILE_DEST is configured and has sufficient space.
• If ADDM output is sparse, ensure there was sufficient workload during the analysis interval — ADDM requires measurable database time to produce meaningful findings.
• Use both PDB and CDB ADDM reports to correlate local and global performance issues.