Oracle DBA

Oracle Database 19c introduced a powerful feature for multitenant environments: the ability to run Automatic Database Diagnostic Monitor (ADDM) analysis on individual pluggable databases (PDBs). With ADDM analysis at the PDB level, administrators can now focus on specific performance issues within a single PDB, making it easier to tune and optimize performance in a multitenant container database (CDB).

Overview of ADDM in Multitenant Environments

Starting with Oracle Database 12c, ADDM was automatically enabled in the root container of a CDB. However, it was only with Oracle Database 19c that ADDM analysis became available for individual PDBs. This means:

• CDB Root: ADDM is enabled by default and provides diagnostic insights across the entire container database.
• PDBs: ADDM is not enabled by default for PDBs, as they do not take automatic AWR (Automatic Workload Repository) snapshots by default. To enable ADDM in a PDB, automatic AWR snapshots must first be activated in that PDB.

How ADDM Works in CDBs and PDBs

In both CDB and non-CDB environments, ADDM operates by analyzing data from the last two AWR snapshots, typically covering a one-hour period. However, the behavior differs slightly between the CDB root and PDBs:

• In the CDB Root: ADDM runs with each AWR snapshot on the CDB root, providing diagnostic data that spans the entire multitenant environment, potentially including data from multiple PDBs. These results are accessible only when the current container is the CDB root.
• In a PDB: If AWR snapshots are enabled, ADDM runs in the same way as it would in a non-CDB, but its analysis is limited to the specific PDB. The findings from ADDM in a PDB are specific to that PDB and exclude any CDB-wide issues. Additionally, ADDM results from the PDB cannot be accessed from the CDB root.

Setting Up ADDM in a PDB

To enable ADDM for a PDB, follow these steps:

1. Enable AWR Snapshot Auto-Flush: Run this command in the PDB to allow automatic snapshot collection:

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;

• 2. Set Snapshot Interval: Define the snapshot interval (in minutes) to control how frequently AWR snapshots are taken in the PDB. For example:

SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>60);

This setup allows ADDM to run in the PDB and provides PDB-specific performance insights.

Viewing ADDM Results

ADDM results can be accessed through:

• Enterprise Manager Cloud Control: Provides an intuitive way to review findings and recommendations.
• DBA Views: Use the DBA_ADDM_* and DBA_ADVISOR_* views to query detailed ADDM results.

PDB-Level ADDM Restrictions

Although ADDM for PDBs offers valuable insights, it has certain limitations. Unlike ADDM analysis at the CDB level, PDB-level ADDM cannot report issues that pertain to the entire CDB environment. Some of these limitations include:

1. I/O Issues: ADDM in a PDB does not report on issues such as undersized buffer cache, excessive temporary or undo writes, and other I/O-related problems that impact the entire CDB.
2. SQL Parsing Issues: SQL hard parsing issues due to memory constraints or cursor aging are omitted, as these are managed at the CDB level.
3. Cluster and Resource Contention Issues: ADDM in a PDB will not report on cluster messaging or network issues (e.g., latency or contention) that apply to the entire CDB.
4. System-Wide Wait Events: Issues like log buffer waits, CPU bottlenecks, or operating system paging are excluded, as they require CDB-level analysis.
5. RMAN Wait Events: ADDM at the PDB level does not capture RMAN-specific wait events, such as I/O queue waits or PGA limit waits.

Key Takeaways

• CDB-Level ADDM: Automatically enabled and provides insights across the entire CDB environment.
• PDB-Level ADDM: Available in Oracle Database 19c but must be enabled by activating AWR snapshots. It focuses on PDB-specific issues only.
• Limitations: PDB-level ADDM cannot diagnose issues that affect the entire CDB, making CDB root ADDM crucial for a comprehensive performance overview.

Oracle’s enhancement of ADDM functionality in multitenant environments represents a significant step forward in database tuning. By enabling ADDM in specific PDBs, administrators gain targeted insights for PDB-level tuning, ensuring optimized performance across the multitenant landscape.