Creating AWR report for PDB database on 12.2 Oracle

Following are steps to enable AWR report for PDB database on 12.2 or later version of Oracle:

1. Connect to the PDB database with admin option:

SQLPLUS sys@pdb2 as sysdba
Password


2. Check the AWR parameter set at PDB level.

SQL> show parameter awr

NAME                          TYPE     VALUE
----------------------------- -------- --------
awr_pdb_autoflush_enabled     boolean  FALSE
awr_pdb_max_parallel_slaves   integer  10
awr_snapshot_time_offset      integer  0

3. Enabled the AWR PDB AUTOFLUSH ENABLED parameter.

alter system set awr_pdb_autoflush_enabled=true;


4. Check AWR Snapshot time and set it for 60 minutes:

-- Check
select * from cdb_hist_wr_control;

     DBID  SNAP_INTERVAL     RETENTION         TOPNSQL  CON_ID SRC_DBID   SRC_DBNAME
---------- ----------------- ----------------- -------- ------ ---------- ----------
3687486700 +40150 00:01:00.0 +00008 00:00:00.0 DEFAULT  5      3687486700 

     DBID  SNAP_INTERVAL     RETENTION         TOPNSQL  CON_ID SRC_DBID   SRC_DBNAME
---------- ----------------- ----------------- -------- ------ ---------- ----------
3687486700 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT  5      3687486700 

5. Note for avoid performance issue when multiple PDBs creating snapshots in same time.
Set the following parameter:

-- For avoid performance issues
alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;


6. For testing the AWR Report manually create the snapshot and fetch the report:

-- Manually create snapshots:
exec dbms_workload_repository.create_snapshot();

--Fetch AWR report:
@?/rdbms/admin/awrrpt.sql;


Note: For PDB Snapshot view for check snapshot:

select * from awr_pdb_snapshot;