AWR Comparison Report

The awrddrpt.sql SQL script generates a report that compares performance attributes and configuration settings between two selected time periods on the local database instance. 

The report compares top wait events, SQL performance, parameter changes etc between two snapshot intervals.

Sample Test Case : Change SGA of PDB from 6 GB to 3 GB and verify it in AWR comparison report

=> For the purpose of testing, I am changing the snapshot interval to 15 minutes

SQL> col SNAP_INTERVAL for a20
SQL> col retention for a20
SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 01:00:00.0    +00008 00:00:00.0

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 15,retention => 11520);

PL/SQL procedure successfully completed.

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 00:15:00.0    +00008 00:00:00.0
SQL>

=> Change SGA of PDB

SQL> alter session set container=SITPDB;

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 11G
sga_min_size                         big integer 0
sga_target                           big integer 6G
unified_audit_sga_queue_size         integer     1048576

SQL> alter system set sga_target=3g;
System altered.

SQL>

=> Wait till snapshot generation and run awr comparison report 

SQL> alter session set container=SITPDB;

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Review the init.ora parameters section in the report, to verify the sga change 

=> Useful scripts:

awrddrpi.sql => for specific instance

awrgdrpt.sql => for all instances in RAC database

awrgdrpi.sql => for a set of instances in RAC database