Updated DBA AWR Blitz Reports now work with pluggable databases

With introduction of container databases Oracle changed the way AWR data is stored and accessed. You can read about that in more detail in this article from Franck Pachot.

As we used AWR data in our Diagnostic Pack reports we also had to make some adjustments due to various issues.

The first issue we have noticed was performance degradation. Some reports ran much slower and "EXTENDED DATA LINK" was displayed in queries' action plans.

According to the following articles we applied Patch 31142749 and set container_data parameter to CURRENT:

Dictionary Queries Having Objects Reference To "EXTENDED DATA LINK" Sharing Are Running Slower In PDB (Doc ID 2745111.1)

Slow Dictionary Query on ALL_ARGUMENTS (Doc ID 2707499.1)

After that queries worked much faster. So if you face similar issues accessing Oracle supplied objects like DBA_, ALL_ views, consider applying the above fix.

The next challenge we faced that many AWR reports didn't show any data as the new parameter prohibits data retrieval from CDB, and a PDB doesn't contain any AWR data by default. The following document gives instructions to enable AWR data collection at the PDB level:

How to Create an AWR Report at the PDB level on 12.2 or later (Doc ID 2334006.1)

We set awr_pdb_autoflush_enabled to true and the reports started to return data.

As we are aware that our customers rely on different Oracle database versions, we introduced a new parameter to the Diagnostic Pack reports. It is similar to the database parameter container_data, but the possible values are slightly different: 'All', 'Current PDB', 'N/A'. For example if the database parameter container_data is set to ALL, then you can choose if Blitz Report should retrieve data from CDB+PDBs ('All') or from the Current PDB only. If container_data is set to CURRENT or CURRENT_DICTIONARY, then the only available Blitz Report parameter value is Current PDB. If your database is not container enabled, then the parameter value is automatically set to 'N/A'.

Please try the updated Diagnostic Pack reports for solving performance issues in your multitenant databases. If you have any questions or comments please post them.