Managing Automatic Workload Repository in a Multitenant Environment

Categorization of AWR Data in a Multitenant Environment

In a multitenant environment, AWR data falls into different categories.

The categories are as follows:

General AWR data

This data has no security implications. It is safe to be shared among all tenants in a CDB. This data is accessible by all PDBs and is captured in both CDB-level and PDB-level snapshots. Examples of general AWR data include the names of statistics, latches, and parameters.
AWR data for a CDB

This category aggregates data for all tenants in a CDB. This data contains the status of the database as a whole and is useful only for the CDB administrator. This data is captured only in the CDB-level snapshots.
AWR data for individual PDBs

This data describes the individual PDBs in a CDB. It shows container-specific data that represents the contribution of each individual PDB to the whole database instance. Therefore, this data is useful for both the CDB and the PDB administrators. This data is captured in both CDB-level and PDB-level snapshots.

AWR Data Storage and Retrieval in a Multitenant Environment

This section describes the process of managing snapshots, and exporting and importing AWR data in a multitenant environment.

Managing Snapshots

Starting with Oracle Database 12c Release 2 (12.2), you can take an AWR snapshot at a CDB-level, that is, on a CDB root, as well as at a PDB-level, that is, on an individual PDB. By default, the CDB-level snapshot data is stored in the SYSAUX tablespace of a CDB root and the PDB-level snapshot data is stored in the SYSAUX tablespace of a PDB.

A CDB-level snapshot contains information about the CDB statistics as well as all the PDB statistics, such as ASH, SQL statistics, and file statistics. The CDB administrator can perform CDB-specific management operations, such as setting AWR data retention period, setting snapshot schedule, taking manual snapshots, and purging snapshot data for a CDB root.

A PDB-level snapshot contains the PDB statistics and also some global statistics that can be useful for diagnosing the performance problems related to the PDB. The PDB administrator can perform PDB-specific management operations, such as setting AWR data retention period, setting snapshot schedule, taking manual snapshots, and purging snapshot data for a PDB.

The CDB-level and PDB-level snapshot operations, such as creating snapshots and purging snapshots, can be performed in either the automatic mode or the manual mode.

The automatic snapshot operations are scheduled, so that they get executed automatically at a particular time. The AWR_PDB_AUTOFLUSH_ENABLED initialization parameter enables you to specify whether to enable or disable automatic snapshots for all the PDBs in a CDB or for individual PDBs in a CDB.

The automatic snapshot operations are enabled by default for a CDB and (in Oracle Database 23ai ) for a PDB. To enable automatic snapshots for a PDB, the PDB administrator must connect to that PDB, set the value for the AWR_PDB_AUTOFLUSH_ENABLED parameter to true, and set the snapshot generation interval to a value greater than 0.

See Also:

Oracle Database Reference for more information about the AWR_PDB_AUTOFLUSH_ENABLED initialization parameter

The manual snapshot operations are explicitly initiated by users. The automatic snapshots and manual snapshots capture the same AWR information. Oracle recommends to generally use manual snapshots for a PDB. You should enable automatic snapshots only selectively for a PDB for performance reasons.

The primary interface for managing snapshots is Oracle Enterprise Manager Cloud Control (Cloud Control). If Cloud Control is not available, then you can use the procedures in the DBMS_WORKLOAD_REPOSITORY package to manage snapshots. The Oracle DBA role is required to use the procedures in the DBMS_WORKLOAD_REPOSITORY package. The SQL procedures to create, drop, and modify snapshots for a CDB root and a PDB are the same as that for a non-CDB. These SQL procedures perform their operations on the local database by default, if the target database information is not provided in their procedure call.

Note:

The PDB-level snapshots have unique snapshot IDs and are not related to the CDB-level snapshots.
The plugging and unplugging operations of a PDB in a CDB do not affect the AWR data stored on a PDB.
The CDB administrator can use the PDB lockdown profiles to disable the AWR functionality for a PDB by executing the following SQL statement on that PDB:
```
SQL> alter lockdown profile profile_name disable feature=('AWR_ACCESS');
```
Once the AWR functionality is disabled on a PDB, snapshot operations cannot be performed on that PDB.

The AWR functionality can be enabled again for a PDB by executing the following SQL statement on that PDB:
```
SQL> alter lockdown profile profile_name enable feature=('AWR_ACCESS');
```
Snapshot data is stored in the SYSAUX tablespace of a CDB and a PDB by default. Starting with Oracle Database 19c, you can specify any other tablespace to store snapshot data for a CDB and a PDB by modifying snapshot settings.

Note:

A multitenant container database is the only supported architecture in Oracle Database 21c and later releases. While the documentation is being revised, legacy terminology may persist. In most cases, "database" and "non-CDB" refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, "non-CDB" refers to a non-CDB from a previous release.

See Also:

"Creating Snapshots"
"Dropping Snapshots"
"Modifying Snapshot Settings"
Oracle Database Security Guide for more information about the PDB lockdown profiles

Exporting and Importing AWR Data

The process of exporting and importing AWR data for a CDB root and a PDB in a multitenant environment is similar to the process of exporting and importing AWR data for a non-CDB.

See Also:

"Exporting AWR Data" for information about exporting AWR data from an Oracle database
"Importing AWR Data" for information about importing AWR data into an Oracle database

Viewing AWR Data in a Multitenant Environment

You can view the AWR data in a multitenant environment using various Oracle Database reports and views.

AWR Reports

The primary interface for generating AWR reports is Oracle Enterprise Manager Cloud Control (Cloud Control). Whenever possible, generate AWR reports using Cloud Control.

See Also:

Oracle Database 2 Day + Performance Tuning Guide for more information about generating AWR report using Cloud Control

If Cloud Control is unavailable, then you can generate the AWR reports by running SQL scripts as described below. The DBA role is required to run these scripts.

You can generate a CDB-specific AWR report from a CDB root that shows the global system data statistics for the whole multitenant environment. You can generate this AWR report using the SQL scripts described in the section "Generating an AWR Report for the Local Database".
You can generate a PDB-specific AWR report from a PDB that shows the statistics related to that PDB. You can generate this AWR report using the SQL scripts described in the section "Generating an AWR Report for the Local Database".
You can generate a PDB-specific AWR report from a CDB root that shows the statistics related to a specific PDB. You can generate this AWR report using the SQL scripts described in the section "Generating an AWR Report for a Specific Database".

AWR Views

The following table lists the Oracle Database views for accessing the AWR data stored on the CDB root and the individual PDBs in a multitenant environment.

See Also:

"Using Automatic Workload Repository Views" for more information about these AWR views

Table 6-2 Views for Accessing AWR Data in a Multitenant Environment

Views	Description
`DBA_HIST` Views	The `DBA_HIST` views show the AWR data present only on the CDB root. When the `DBA_HIST` views are accessed from a CDB root, they show all the AWR data stored on the CDB root. When the `DBA_HIST` views are accessed from a PDB, they show the subset of the CDB root AWR data, which is specific to that PDB.
`DBA_HIST_CON` Views	The `DBA_HIST_CON` views are similar to the `DBA_HIST` views, but they provide more fine grained information about each container, and thus, they have more data than the `DBA_HIST` views. The `DBA_HIST_CON` views show the AWR data present only on the CDB root. When the `DBA_HIST_CON` views are accessed from a CDB root, they show all the AWR data stored on the CDB root. When the `DBA_HIST_CON` views are accessed from a PDB, they show the subset of the CDB root AWR data, which is specific to that PDB.
`AWR_ROOT` Views	The `AWR_ROOT` views are available starting with Oracle Database 12c Release 2 (12.2) and are available only in the Multitenant environment. The `AWR_ROOT` views are equivalent to the `DBA_HIST` views. The `AWR_ROOT` views show the AWR data present only on the CDB root. When the `AWR_ROOT` views are accessed from a CDB root, they show all the AWR data stored on the CDB root. When the `AWR_ROOT` views are accessed from a PDB, they show the subset of the CDB root AWR data, which is specific to that PDB.
`AWR_PDB` Views	The `AWR_PDB` views are available starting with Oracle Database 12c Release 2 (12.2). The `AWR_PDB` views show the local AWR data present on a CDB root or a PDB. When the `AWR_PDB` views are accessed from a CDB root, they show the AWR data stored on the CDB root. When the `AWR_PDB` views are accessed from a PDB, they show the AWR data stored on that PDB.
`CDB_HIST` Views	The `CDB_HIST` views show the AWR data stored on the PDBs. When the `CDB_HIST` views are accessed from a CDB root, they show the union of the AWR data stored on all the PDBs. When the `CDB_HIST` views are accessed from a PDB, they show the AWR data stored on that PDB.