Shulou(Shulou.com)06/01 Report--

-- 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.