Modify AWR collection intervals and manually create or delete AWR collections
-- the default AWR is executed every hour and retained for 8 days
SQL > select * from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
2478546149 + 00000 01 DEFAULT 00.0 + 00008 0000 VR 00.0 00.0
You can modify the collection interval through dbms_workload_repository.modify_snapshot_settings, and the retention time of the AWR report. If the collection interval parameter is set to 0, all statistics collection will be stopped.
Example: modified AWR is collected every 15 minutes, and snapshot data retention time is set to 20160 minutes (14 days)
Exec dbms_workload_repository.modify_snapshot_settings (retention= > 20160 Art intervale = > 15)
-- you can find out that it has been modified. AWR collects every 15 minutes, and snapshot data is retained for 14 days.
SQL > select * from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
2478546149 + 00000 DEFAULT 1500 DEFAULT 00.0 + 00014 0000 virtual 00.0
You can create AWR snapshots manually
Exec dbms_workload_repository.create_snapshot
Delete AWR Snapshot
Exec dbms_workload_repository.drop_snapshot_range (low_snap_id= > 300)
-- View all snapshots of AWR
SQL > select SNAP_ID,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_LEVEL from dba_hist_snapshot order by 1
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
302 2 27-OCT-15 04.20.07.131 PM 27-OCT-15 05.00.16.054 PM 1
302 1 27-OCT-15 04.20.06.956 PM 27-OCT-15 05.00.15.862 PM 1
303 2 27-OCT-15 05.00.16.054 PM 27-OCT-15 06.00.29.251 PM 1
303 1 27-OCT-15 05.00.15.862 PM 27-OCT-15 06.00.27.468 PM 1
304 1 27-OCT-15 06.00.27.468 PM 27-OCT-15 07.00.12.771 PM 1
304 2 27-OCT-15 06.00.29.251 PM 27-OCT-15 07.00.12.570 PM 1
305 1 27-OCT-15 07.00.12.771 PM 27-OCT-15 08.00.21.897 PM 1
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
305 2 27-OCT-15 07.00.12.570 PM 27-OCT-15 08.00.21.743 PM 1
306 1 27-OCT-15 08.00.21.897 PM 27-OCT-15 09.00.02.813 PM 1
306 2 27-OCT-15 08.00.21.743 PM 27-OCT-15 09.00.02.923 PM 1
307 1 27-OCT-15 09.00.02.813 PM 27-OCT-15 10.00.11.459 PM 1
307 2 27-OCT-15 09.00.02.923 PM 27-OCT-15 10.00.12.006 PM 1
308 1 27-OCT-15 10.00.11.459 PM 27-OCT-15 11.00.50.885 PM 1
308 2 27-OCT-15 10.00.12.006 PM 27-OCT-15 11.00.49.493 PM 1
309 1 27-OCT-15 11.00.50.885 PM 28-OCT-15 12.14.20.256 AM 1
309 2 27-OCT-15 11.00.49.493 PM 28-OCT-15 12.00.57.972 AM 1
310 2 28-OCT-15 12.00.57.972 AM 28-OCT-15 01.00.06.613 AM 1
311 1 28-OCT-15 01.11.44.978 AM 28-OCT-15 02.00.40.285 AM 1
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
311 2 28-OCT-15 01.00.06.613 AM 28-OCT-15 02.16.06.729 AM 1
312 2 28-OCT-15 02.16.06.729 AM 28-OCT-15 03.00.06.136 AM 1
312 1 28-OCT-15 02.00.40.285 AM 28-OCT-15 03.00.06.072 AM 1
313 2 28-OCT-15 03.00.06.136 AM 28-OCT-15 04.00.15.598 AM 1
313 1 28-OCT-15 03.00.06.072 AM 28-OCT-15 04.00.15.535 AM 1
314 1 28-OCT-15 04.00.15.535 AM 28-OCT-15 05.00.35.594 AM 1
314 2 28-OCT-15 04.00.15.598 AM 28-OCT-15 05.00.35.487 AM 1
315 2 28-OCT-15 05.00.35.487 AM 28-OCT-15 06.00.07.814 AM 1
315 1 28-OCT-15 05.00.35.594 AM 28-OCT-15 06.00.07.743 AM 1
316 2 28-OCT-15 06.00.07.814 AM 28-OCT-15 07.00.17.015 AM 1
316 1 28-OCT-15 06.00.07.743 AM 28-OCT-15 07.00.16.955 AM 1
-- create a baseline
SQL > exec dbms_workload_repository.create_baseline (start_snap_id = > 309, end_snap_id = > 314, baseline_name = > 'EMO BASELINE')
PL/SQL procedure successfully completed.
SQL > select BASELINE_NAME,BASELINE_TYPE,BASELINE_ID,START_SNAP_ID,END_SNAP_ID from dba_hist_baseline
BASELINE_NAME BASELINE_TYPE BASELINE_ID START_SNAP_ID END_SNAP_ID
--
EMO BASELINE STATIC 2 309 314
SYSTEM_MOVING_W MOVING_WINDOW 0 300 343
INDOW
-Delete the baseline
SQL > exec dbms_workload_repository.drop_baseline (baseline_name= > 'EMO BASELINE',Cascade= > FALSE)
Note: if the parameter Cascade= > true, all related snapshots will be deleted. Otherwise, the AWR automatic process clears these snapshots automatically, and the default is FALSE.
PL/SQL procedure successfully completed.
SQL > select SNAP_ID, INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot order by 1
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
300 2 21-OCT-15 03.00.25.000 PM 21-OCT-15 03.12.07.602 PM
300 1 21-OCT-15 03.03.08.000 PM 21-OCT-15 03.12.07.787 PM
301 1 27-OCT-15 04.08.36.000 PM 27-OCT-15 04.20.06.956 PM
301 2 27-OCT-15 04.10.55.000 PM 27-OCT-15 04.20.07.131 PM
302 2 27-OCT-15 04.20.07.131 PM 27-OCT-15 05.00.16.054 PM
302 1 27-OCT-15 04.20.06.956 PM 27-OCT-15 05.00.15.862 PM
303 2 27-OCT-15 05.00.16.054 PM 27-OCT-15 06.00.29.251 PM
303 1 27-OCT-15 05.00.15.862 PM 27-OCT-15 06.00.27.468 PM
304 1 27-OCT-15 06.00.27.468 PM 27-OCT-15 07.00.12.771 PM
304 2 27-OCT-15 06.00.29.251 PM 27-OCT-15 07.00.12.570 PM
305 1 27-OCT-15 07.00.12.771 PM 27-OCT-15 08.00.21.897 PM
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
305 2 27-OCT-15 07.00.12.570 PM 27-OCT-15 08.00.21.743 PM
306 1 27-OCT-15 08.00.21.897 PM 27-OCT-15 09.00.02.813 PM
306 2 27-OCT-15 08.00.21.743 PM 27-OCT-15 09.00.02.923 PM
307 1 27-OCT-15 09.00.02.813 PM 27-OCT-15 10.00.11.459 PM
307 2 27-OCT-15 09.00.02.923 PM 27-OCT-15 10.00.12.006 PM
308 1 27-OCT-15 10.00.11.459 PM 27-OCT-15 11.00.50.885 PM
308 2 27-OCT-15 10.00.12.006 PM 27-OCT-15 11.00.49.493 PM
309 1 27-OCT-15 11.00.50.885 PM 28-OCT-15 12.14.20.256 AM
309 2 27-OCT-15 11.00.49.493 PM 28-OCT-15 12.00.57.972 AM
310 2 28-OCT-15 12.00.57.972 AM 28-OCT-15 01.00.06.613 AM
311 1 28-OCT-15 01.11.44.978 AM 28-OCT-15 02.00.40.285 AM
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
311 2 28-OCT-15 01.00.06.613 AM 28-OCT-15 02.16.06.729 AM
312 2 28-OCT-15 02.16.06.729 AM 28-OCT-15 03.00.06.136 AM
312 1 28-OCT-15 02.00.40.285 AM 28-OCT-15 03.00.06.072 AM
313 2 28-OCT-15 03.00.06.136 AM 28-OCT-15 04.00.15.598 AM
313 1 28-OCT-15 03.00.06.072 AM 28-OCT-15 04.00.15.535 AM
314 1 28-OCT-15 04.00.15.535 AM 28-OCT-15 05.00.35.594 AM
314 2 28-OCT-15 04.00.15.598 AM 28-OCT-15 05.00.35.487 AM
315 2 28-OCT-15 05.00.35.487 AM 28-OCT-15 06.00.07.814 AM
315 1 28-OCT-15 05.00.35.594 AM 28-OCT-15 06.00.07.743 AM
316 2 28-OCT-15 06.00.07.814 AM 28-OCT-15 07.00.17.015 AM
316 1 28-OCT-15 06.00.07.743 AM 28-OCT-15 07.00.16.955 AM
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
317 2 28-OCT-15 07.00.17.015 AM 28-OCT-15 08.00.03.044 AM
317 1 28-OCT-15 07.00.16.955 AM 28-OCT-15 08.00.03.110 AM
318 2 28-OCT-15 08.00.03.044 AM 28-OCT-15 09.00.12.153 AM
318 1 28-OCT-15 08.00.03.110 AM 28-OCT-15 09.00.12.216 AM
319 2 28-OCT-15 09.00.12.153 AM 28-OCT-15 10.00.31.063 AM
319 1 28-OCT-15 09.00.12.216 AM 28-OCT-15 10.00.31.153 AM
320 2 28-OCT-15 10.37.05.000 AM 28-OCT-15 11.00.52.462 AM
320 1 28-OCT-15 10.34.51.000 AM 28-OCT-15 11.00.52.640 AM
321 2 28-OCT-15 11.00.52.462 AM 28-OCT-15 12.00.01.420 PM
321 1 28-OCT-15 11.00.52.640 AM 28-OCT-15 12.00.01.555 PM
322 1 28-OCT-15 02.25.56.000 PM 28-OCT-15 02.35.37.639 PM
