您好,登錄后才能下訂單哦!
awr保留10天,每小時(shí)收集一次:
begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(14400,60); end;
手動(dòng)收集awr一次:
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
手動(dòng)執(zhí)行一個(gè)快照:
Exec dbms_workload_repository.create_snapshot; (這個(gè)要背出來哦,用的時(shí)候去翻手冊,丟臉哦)
創(chuàng)建一個(gè)AWR基線
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id,end_snap_id ,baseline_name);
@?/rdbms/admin/awrrpt 生成AWR報(bào)告
@?/rdbms/admin/awrddrpt AWR比對報(bào)告
@?/rdbms/admin/awrgrpt RAC 全局AWR
dbms_workload_repository手工管理AWR
1、修改快照設(shè)置
[sql] view plaincopyprint?
查看awr保留的天數(shù)和采集頻率:
sys@ORCL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------- ------------------------------ ----------
1301772781 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
修改awr保留的天數(shù)和采集頻率:
sys@ORCL> exec dbms_workload_repository.modify_snapshot_settings(retention => 15*1440,interval => 30);
PL/SQL 過程已成功完成。
sys@ORCL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------- ------------------------------ ----------
1301772781 +00000 00:30:00.0 +00015 00:00:00.0 DEFAULT
interval:將間隔設(shè)置為0時(shí),oracle將禁止采用自動(dòng)和手工快照的機(jī)制。
retention:當(dāng)保存時(shí)間設(shè)置為0時(shí),oracle將永久地保存這個(gè)快照。
2、創(chuàng)建和刪除快照
[sql] view plaincopyprint?
手動(dòng)執(zhí)行一個(gè)awr快照:
sys@ORCL> exec dbms_workload_repository.create_snapshot();
PL/SQL 過程已成功完成。
刪除awr報(bào)告(指定刪除范圍):
sys@ORCL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 10,high_snap_id => 12);
PL/SQL 過程已成功完成。
sys@ORCL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 628,high_snap_id => 629);
PL/SQL 過程已成功完成。
dba_hist_snapshot視圖將列出有關(guān)可用快照的信息。
SELECT * FROM dba_hist_snapshot
3、創(chuàng)建和刪除基線
[sql] view plaincopyprint?
sys@ORCL> exec dbms_workload_repository.create_baseline(start_snap_id => 630,end_snap_id => 635,baseline_name => 'base line');
PL/SQL 過程已成功完成。
sys@ORCL> select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;
BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------------------------------------------------------------- ------------- -----------
base line 630 635
SYSTEM_MOVING_WINDOW 636 637
sys@ORCL> exec dbms_workload_repository.drop_baseline(baseline_name => 'base line',cascade => true);
PL/SQL 過程已成功完成。
sys@ORCL> select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;
BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------------------------------------------------------------- ------------- -----------
SYSTEM_MOVING_WINDOW 636 637
sys@ORCL> exec dbms_workload_repository.create_baseline(to_date('2013-11-03 00:00:00','yyyy-mm-dd hh34:mi:ss'),to_date('2013-11-03 06:00:00','yyyy-mm-dd hh34:mi:ss'),'base line2');
PL/SQL 過程已成功完成。
sys@ORCL> select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;
BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------------------------------------------------------------- ------------- -----------
base line 685 686
base line2 685 686
SYSTEM_MOVING_WINDOW 640 696
4、自動(dòng)創(chuàng)建AWR基線
[sql] view plaincopyprint?
sys@ORCL> alter session set NLS_DATE_FORMAT= 'yyyy-mm-dd hh34:mi:ss';
會(huì)話已更改。
sys@ORCL> exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template( -
> day_of_week => 'WEDNESDAY', -
> hour_in_day => 0, -
> duration => 6, -
> start_time => '2013-11-01:00:00:00', -
> end_time => '2013-12-31:06:00:00', -
> baseline_name_prefix => 'Batch Baseline ', -
> template_name => 'Batch Template', -
> expiration => 365);
PL/SQL 過程已成功完成。
sys@ORCL> select t.template_name,
2 t.template_type,
3 t.start_time,
4 t.end_time,
5 t.day_of_week,
6 t.hour_in_day,
7 t.duration
8 from dba_hist_baseline_template t;
TEMPLATE_NAME TEMPLATE_ START_TIME END_TIME DAY_OF_WE HOUR_IN_DAY DURATION
------------------------------ --------- ------------------- ------------------- --------- ----------- ----------
Batch Template REPEATING 2013-11-01 00:00:00 2013-12-31 06:00:00 WEDNESDAY 0 6
sys@ORCL> exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template('Batch Template');
PL/SQL 過程已成功完成。
day_of_week:天或者星期,基線在這個(gè)時(shí)間上重復(fù)。Specify one of the following values:('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY','THURSDAY', 'FRIDAY', 'SATURDAY', 'ALL')
hour_in_day:0-23,執(zhí)行基線在這個(gè)小時(shí)開始。
duration:持續(xù)時(shí)間(小時(shí)數(shù))。
start_time:創(chuàng)建基線的開始時(shí)間。
end_time:創(chuàng)建基線的結(jié)束時(shí)間。
expiration :基線過期的天數(shù)。
對于上面這個(gè)模板,將會(huì)在每周三根據(jù)0:00到上午6:00的時(shí)間窗口創(chuàng)建固定基線。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。