Changes in AWR behavior in versions 12cR1 and 12cR2

Introduction

AWR reports are available as part of the Diagnostics pack (extra cost option to the Enterprise Edition). AWR reports are commonly used by Oracle DBAs and they can be extremely useful when diagnosing performance issues. In this short article I will show the differences between AWR in version 12c Release 1 (12.1) and 12c Release 2 (12.2)

12c Release 1 (12.1) with Multitenant

In Oracle 12c Release 1, Oracle introduced the Multitenant architecture (I've posted a separate article to cover the great benefits of Oracle Multitenant. Read more here). However, the main challenge with Oracle 12cR1 is that AWR data is stored at the CDB$ROOT container level. This has several implications:

• AWR reports are available only at CDB level - it is not possible to run AWR for a specific pluggable database.
• AWR management operations (e.g. snapshots schedule, data retention, taking manual snapshots, purging snapshots) could be done only at CDB level.
• Unplugged PDB does not contain AWR information, so when unplugging a PDB and plugging it into a different CDB all the AWR data will be lost.

Having said that, it's important to mention that even that it is only possible to generate AWR reports at CDB level, Oracle added column "PDB Name" to the tables in the AWR so it allows us to understand to which PDB the information is associated with. Here is an example:

12c Release 2 (12.2) with Multitenant

Good new is that in Oracle 12c Release 2, Oracle added AWR at PDB-Level meaning that AWR information will also be stored in each PDB (under SYSAUX tablespace). This has several implications:

• AWR reports are available at both CDB and PDB level
• AWR management operations (e.g. snapshots schedule, data retention, taking manual snapshots, purging snapshots) can be done at both CDB and PDB level
• Unplugged PDB does contain AWR information, so when unplugging a PDB and plugging it into a different CDB all the AWR data will be available.

Let's see an example of how it looks like when running AWR report in 12c Release 2:

As you can see, Oracle enables us to choose whether we would like to run a CDB-level AWR report by specifying "AWR_ROOT" which is the default, or running a PDB-level AWR report by specifying "AWR_PDB". If you choose PDB-level AWR, Oracle will generate a report that displays performance data only for a particular PDB, as follows:

Pro Tip

In Oracle 12c Release 2, Oracle will automatically create AWR snapshots only at CDB-level by default. If you would like to change this default behavior and enable automatic AWR snapshots for PDBs, you would need to alter the new AWR_PDB_AUTOFLUSH_ENABLED parameter which can be set at either CDB level or PDB level.