On the Fifth Day of 12.2, my DBA gave to me…
SQL Performance Tuning New Features
AWR at PDB Level
Automatic workload repository reports can now be generated at PDB level. This feature will help a lot in narrowing down performance issues in a very active 12c multi-tenant environment, which has multiple PDBS. In 12.1, the AWR reports could be created only at CDB level, and it was quiet cumbersome to work through them when trying to resolve an issue with a particular PDB.
This feature is not enabled by default and is recommended to be used only when debugging. The snapshot interval and retention can be controlled at PDB level.
To support the above feature, new views, AWR_ROOT% and AWR_PDB% are now introduced to check AWR information, at both ROOT(container) and PDB level respectively.
AWR for Active Data Guard
Oracle 12.2, comes with an enhanced AWR framework to support capture of AWR snapshots on Active Data Guard databases. The primary database or any other remote database, (can be termed as AWR-catalog database), can host the snapshots repository. AWR tables on the catalog database gather information on the snapshot of ADG database, using database links.
The snapshots can be taken manually or automated.
Optimizer Statistics Advisor
Another great tool for a DBA, is the Optimizer Statistics Advisor. Often it was found that statistics were stale in the databases and not collected by the nightly auto stats job, due to …
To receive a full version emailed copy of this document, including more on Per-Process PGA Limits, Index Monitoring, and Adaptive Plans, please complete the download form provided.