Wednesday, September 11, 2013

12c Consolidated AWR report with PDBs

 For a DBA, Automatic Workload Repository (AWR) is THE tool that helps in diagnosis and tuning.  However, with 12c multi-tenant architecture, where one Container Database (CDB) can have multiple Pluggable Database (PDB).  what will happen to the AWR ? 

The Manual  explains 12c AWR as follows.
"If a dictionary table stores information that pertains to the CDB as a whole, instead of for each PDB, then both the metadata and the data displayed in a data dictionary view are stored in the root. For example, Automatic Workload Repository (AWR) data is stored in the root and displayed in some data dictionary views, such as the DBA_HIST_ACTIVE_SESS_HISTORY view. An internal mechanism called an object link enables a PDB to access both the metadata and the data for these types of views in the root."

 It implies that, all the AWR snapshots is taken at CDB level and PDBs have appropriate views  for its corresponding con_id of individual PDBs.
It make total sense in a consolidated environment. If you want to see I/O, cpu utilization on a server, you can get a full picture at a CDB level. Until now in 11g, you needed to login to every DB to check what was happening at a particular time period during say heavy i/o. Now in 12c, you have a global view of all your database(PDBs)  activities on a server at CDB AWR. The individual PDBs still will have views to monitor sqls pertaining to it.

 A simple test is to run  exec dbms_workload_repository.create_snapshot();  in any PDB and CDB and check the max snap_id for each PDB and CDB. (select  con_id,max(snap_id) from dba_hist_sqlstat group by con_id;)
The snap_id is  the same for all PDBs and CDB. This implies, no matter where you run create_snapshot(), Repository is always at CDB level. and every PDB in the DB will get updated VIEW related to its environment.

Below is a small test...



SQL> @a
SQL> -- conn as container DB
SQL> conn sys/oracle as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB1               READ WRITE NO
     4 PDB2               MOUNTED
     5 DEV2               READ WRITE NO
SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> select con_id,max(snap_id) from dba_hist_sqlstat group by con_id order by 1;

    CON_ID MAX(SNAP_ID)
---------- ------------
     1       1204
     3       1204
     5       1204

SQL> ---
SQL> -- conn to pdb called dev2
SQL> --
SQL> conn system/manager@dev2
Connected.
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     5 DEV2               READ WRITE NO
SQL> -- check the max snap_id
SQL> select con_id,max(snap_id) from dba_hist_sqlstat group by con_id;

    CON_ID MAX(SNAP_ID)
---------- ------------
     5       1204

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> select con_id,max(snap_id) from dba_hist_sqlstat group by con_id order by 1;

    CON_ID MAX(SNAP_ID)
---------- ------------
     5       1205

SQL> --
SQL> -- connect back to cdb and recheck  max snap_id
SQL> conn sys/oracle as sysdba
Connected.
SQL> select con_id,max(snap_id) from dba_hist_sqlstat group by con_id order by 1;

    CON_ID MAX(SNAP_ID)
---------- ------------
     1       1205
     3       1205
     5       1205

------------------------------------

The next question you might ask is
" can i run AWR reports from PDBs as well as CDBs for consolidated report ? "

Answer: Yes. awrrpt.sql will run at CDB level and PDB level. 
When a snapshot is taken either in PDB or CDB, the data is first populated in the CDB repository. each PDB has all the tables that is required for running awr reports. These tables are populated through object link views from the CDB with information only pertaining to that PDB.

So awrrpt.sql will run at both PDB and CDB levels without errors. However, at PDB level, it will only have info pertaining to it.

Update: If you want to see all the objects with OBJECT link to CDB, you can run the following query.

select distinct object_name from dba_objects where sharing ='OBJECT LINK';

No comments:

Post a Comment

Feedback welcome