oracle awr 数据删除,修改AWR收集时间间隔和手动创建或删除AWR收集-Oracle

最新推荐文章于 2021-07-19 13:49:24 发布

weixin_39854778

最新推荐文章于 2021-07-19 13:49:24 发布

阅读量375

点赞数

文章标签： oracle awr 数据删除

–默认的AWR是每小时执行一次，并且保留8天

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID

———- ————————————————————————— ————————————————————————— ———- ———-

2478546149 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 0

可以通过dbms_workload_repository.modify_snapshot_settings来修改收集的时间间隙，和AWR报告的保留时间。如果收集时间间隔参数设置为0会停止所有统计数据的收集。

例子：修改AWR每15分钟收集一次，快照数据保留时间设置为20160分钟(14天)

exec dbms_workload_repository.modify_snapshot_settings(retention=>20160,interval=>15);

–可以查询出已经修改完毕。AWR每15分钟收集一次，快照数据保留14天。

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID

———- ————————————————————————— ————————————————————————— ———- ———-

2478546149 +00000 00:15:00.0 +00014 00:00:00.0 DEFAULT 0

可以手动创建AWR快照

exec dbms_workload_repository.create_snapshot;

删除AWR快照

exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>300,high_snap_id=>301);

–查看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

–创建基线

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

–删除基线

SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>’EMO BASELINE’,Cascade=>FALSE);

注意：如果参数Cascade=>true,就会删除所有相关的快照。否则，AWR自动进程自动清除这些快照，默认是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

weixin_39854778

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle awr 数据删除,修改AWR收集时间间隔和手动创建或删除AWR收集-Oracle

–默认的AWR是每小时执行一次，并且保留8天SQL> select * from dba_hist_wr_control;DBID SNAP_INTERVAL RETENTION ...
复制链接

扫一扫