-- How to quickly change the AWR Interval and how to create one on demand --

Check the current defaults.

set linesize 100

col snap_interval format a20

col retention format a20

col topnsql format a20

select * from dba_hist_wr_control;

DBID SNAP_INTERVAL        RETENTION            TOPNSQL            

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

3610569430 0 0:60:0.0           7 0:0:0.0            DEFAULT              

Below we are changing the interval from 1 hour (Default) to 30 min.

execute dbms_workload_repository.modify_snapshot_settings ( interval => 30);

anonymous block completed

Check that the change was applied.

set linesize 100

col snap_interval format a20

col retention format a20

col topnsql format a20

select * from dba_hist_wr_control;

DBID SNAP_INTERVAL        RETENTION            TOPNSQL            

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

3610569430 0 0:30:0.0           7 0:0:0.0            DEFAULT    

Create AWR on demand

When debugging SQLs you may want to get an AWR snapshot before and after you run an specific SQL.

exec dbms_workload_repository.create_snapshot();

select * from xxxx /* Your query */


exec dbms_workload_repository.create_snapshot();