How to Create AWR for PDB level on 12.2 Step by Steps

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.

How to create AWR for PDB level on 12.2?

This is a new feature on 12.2.

Oracle Database Performance Tuning Guide
12c Release 2 (12.1)
6.2.7 Managing Automatic Workload Repository in a Multitenant Environment

Steps:
1)Set awr_pdb_autoflush_enabled=true on PDB level:

alter session set container=PDB1;

alter system set awr_pdb_autoflush_enabled=true;

2)Set AWR snpashot properly(It’s not mentioned on other notes but it’s critical if you don’t change it):

select * from cdb_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
2580889417 +40150 00:01:00.0 +00008 00:00:00.0 DEFAULT 3

execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

select * from cdb_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
2580889417 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 3

3)Also please set AWR_SNAPSHOT_TIME_OFFSET to 1000000 to avoid performance issue when multiple PDBs creating snapshots in same time.

Oracle Database 12c Release 2
Database Reference
1.26 AWR_SNAPSHOT_TIME_OFFSET

alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;

4)Wait for 1-2 hours, you can create AWR after snapshots being generated:

select * from awr_pdb_snapshot;

Or you can creae snapshots manually:

SQL> connect / as sysdba
SQL> alter session set container=PDB1;
SQL> exec dbms_workload_repository.create_snapshot();

Then create AWR report:

@?/rdbms/admin/awrrpt

Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT – Use AWR data from root (default)
AWR_PDB – Use AWR data from PDB <<<<<<<