AWR in Multitenant Environment : Oracle 12cR2

AWR in Multitenant Environment : Oracle 12cR2 

Starting with Oracle Database 12c Release 1, Multitenancy is the core part of Database, where a container database (CDB) can include multiple pluggable database (PDBs).

Oracle Database 12c Release 1 (12.1.01) is having centralized Automatic Workload Repository (AWR) repository to store performance data related to CDB and PDBs. One can take an AWR snapshot only at a CDB-level, i.e CDB root. This AWR snapshot is for the whole database system, i.e it contains the statistical information about the CDB as well as all the PDBs in a multitenant environment.

Starting with Oracle Database 12c Release 2 (12.2), CDB root as well as individual PDBs store performance related data, view, and manage AWR data. One can take an AWR snapshot at a CDB-level, i.e on the CDB root, as well as at a PDB-level, i.e. on the individual PDBs.

The CDB-level snapshot data is stored in the SYSAUX tablespace of a CDB root.
The PDB-level snapshot data is stored in the SYSAUX tablespace of a PDB.

The AWR_PDB_AUTOFLUSH_ENABLED initialization parameter enables you to specify whether to enable or disable automatic snapshots for all the PDBs or for individual PDBs. The automatic snapshot operations are enabled by default for a CDB, but are disabled by default 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.

NOTE : PDB snapshot ID's can be different from CDB snapshot ID's

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

Step 1: Login sqlplus as command line.

Step 2: Run awrrpt.sql file as shown in below screen shot.

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

 
Step 3: Select AWR Type

Choose AWR_PDB to generate PDB level AWR report.

It will ask few things as below,

Enter value for report_type: html

Enter value for awr_location: AWR_PDB

Step 4: Input snap id and other parameters, example shown in screenshot.

Enter value for num_days: 1

Enter value for begin_snap: 30

Enter value for end_snap: 31

How PDB AWR looks like  ?

Here you can see, container name, Instance name, Unique name, CDB.

Hope this article help you to generate PDB level AWR report.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert