Oracle AWR（自动工作量资料档案库）的管理与维护详解
前言
awr是automatic workload repository的简称，中文叫着自动工作量资料档案库。对于awr的管理，主要是针对快照和基线的管理而言。比如设定快照的间隔，删除快照，设定快照的保留时间。那对于基线而言则是基线的创建，删除，自动创建等。本文主要描述这2部分的内容。
一、快照管理
默认情况下，oracle数据库每小时生成一次快照，并在工作负载库中保留8天的统计信息。必要时，你可以使用dbms_workload_repository程序手动创建，删除或者修改快照等。可以使用oem以及dbms_workload_repository包管理快照。
1. 手动创建快照
使用以下过程手工创建快照，创建完成后可在数据字典dba_hist_snapshot查看所有快照相关信息。
begin dbms_workload_repository.create_snapshot (); end; / select snap_id, dbid, begin_interval_time, end_interval_time from dba_hist_snapshot order by end_interval_time desc; snap_id dbid begin_interval_time end_interval_time ---------- ---------- ---------------------------- -------------------------- 164 41924548 14-apr-17 09.38.19.467 am 14-apr-17 10.51.21.886 am 163 41924548 14-apr-17 09.00.10.470 am 14-apr-17 09.38.19.467 am 162 41924548 14-apr-17 08.00.07.242 am 14-apr-17 09.00.10.470 am 161 41924548 14-apr-17 07.00.04.120 am 14-apr-17 08.00.07.242 am
2. 删除快照
以下过程将删除snap_id从162到164的快照。在未指定41924548时，使用当前缺省的数据库
begin
dbms_workload_repository.drop_snapshot_range (low_snap_id => 162,
high_snap_id => 164, dbid => 41924548);
end;
/
3. 修改快照的保留间隔
--查看当前的保留策略，如下，为缺省值，采集间隔为1小时，保留8天
sql> select * from dba_hist_wr_control;
dbid snap_interval retention topnsql
---------- -------------------- ------------------ ----------
41924548 +00000 01:00:00.0 +00008 00:00:00.0 default
--下面将其修改为保留5天，采集间隔为30分钟,topnsql为50条
begin
dbms_workload_repository.modify_snapshot_settings( retention => 10*24*60,
interval => 30, topnsql => 50, dbid => 41924548);
end;
/
--查看修改后的结果
sql> select * from dba_hist_wr_control;
dbid snap_interval retention topnsql
---------- -------------------- ------------------ ----------
41924548 +00000 00:30:00.0 +00010 00:00:00.0 50
二、基线管理
awr基线可以手工创建和删除，也可以自动创建和删除。对于自动创建基线则是首先要创建基线样本才能实现自动创建基线。主要描述如下。
1. 创建基线
基线的创建依赖于快照，因此在创建之前应该得到所需时间范围的snap_id，可以通过查询数据字典dba_hist_snapshot获得，同时在基线创建完成后可以从数据字典dba_hist_baseline查询基线相关信息。
begin
dbms_workload_repository.create_baseline (start_snap_id => 160,
end_snap_id => 161, baseline_name => 'peak_baseline',
dbid => 41924548, expiration => 10);
end;
/
sql> select baseline_id,
2 baseline_name,
3 baseline_type,
4 expiration,
5 creation_time
6 from dba_hist_baseline;
baseline_id baseline_name baseline_type expiration creation_
----------- ----------------------- ------------- ---------- ---------
1 peak_baseline static 10 14-apr-17
0 system_moving_window moving_window 07-apr-17
--基于特定时间创建基线，如下示例
begin
dbms_workload_repository.create_baseline (
start_time => to_date ('2017-04-14 6:00:00', 'yyyy-mm-dd hh24:mi:ss'),
end_time => to_date ('2017-04-14 8:00:00', 'yyyy-mm-dd hh24:mi:ss'),
baseline_name => 'peak_baseline2',
expiration => 10);
end;
/
在上面的示例中，创建了一个范围为160-161的，名字为peak_baseline的基线，保留时间为10天。在超过10天后，该基线会被删除，相应的快照也被删除。如果为指定失效期，则该基线以及相应的快照被永久保留。
2. 删除基线
begin
dbms_workload_repository.drop_baseline (baseline_name => 'peak_baseline',
cascade => false, dbid => 41924548);
end;
/
在上面的示例中将删除名为peak_baseline的基线，同时指定了cascade为false，也就是说在删除基线的同时，不删除相应的快照。
3. 基线重命名
begin
dbms_workload_repository.rename_baseline (
old_baseline_name => 'peak_baseline',
new_baseline_name => 'peak_mondays',
dbid => 41924548);
end;
/
--验证结果
sql> select baseline_id,
2 baseline_name,
3 baseline_type,
4 expiration,
5 creation_time
6 from dba_hist_baseline;
baseline_id baseline_name baseline_type expiration creation_
----------- ----------------------- ------------- ---------- ---------
1 peak_mondays static 10 14-apr-17
0 system_moving_window moving_window 07-apr-17
在上面的示例中，将基线的名称由peak_baseline修改成peak_mondays。
4. 修改缺省移动窗口基线保留值
--查看缺省的window_size
select baseline_name, baseline_type, moving_window_size
from dba_hist_baseline
where baseline_name = 'system_moving_window';
baseline_name baseline_type moving_window_size
------------------------ ------------- ------------------
system_moving_window moving_window 8
begin
dbms_workload_repository.modify_baseline_window_size (
window_size => 7,
dbid => 41924548);
end;
/
--window_size为天，只能够小于等于当前快照保留时间，否则报错，如下：
error at line 1:
ora-13541: system moving window baseline size (864000)
greater than retention (691200)
ora-06512: at "sys.dbms_workload_repository", line 686
ora-06512: at line 2
5. 管理基线样本
创建单个基线模板
begin
dbms_workload_repository.create_baseline_template (
start_time => to_date ('2017-04-14 17:00:00', 'yyyy-mm-dd hh24:mi:ss'),
end_time => to_date ('2017-04-14 19:00:00', 'yyyy-mm-dd hh24:mi:ss'),
baseline_name => 'baseline_140414',
template_name => 'template_140414',
expiration => 10,
dbid => 41924548);
end;
/
--如果创建基线样本时间小于当前时间，则收到如下错误
error at line 1:
ora-13537: invalid input for create baseline template (end_time, end_time is less than sysdate)
ora-06512: at "sys.dbms_workload_repository", line 768
ora-06512: at "sys.dbms_workload_repository", line 818
ora-06512: at line 2
在上面的示例中，我们创建了一个单一的基线样本，并且指定了相应的时间范围，基线的名称及保留期限等。那么在这个时间范围内的相应的快照会被保留，同时这个基线可以用于后续在发现性能问题的时候进行比对。
创建重复基线样本
重复的基线样本指的是在将来某个特定的时间范围内，oracle会参照这个设定的样本自动为我们创建基线。比如，可以创建一个重复的基线样本，使得在2017年每周一9:00-11:00自动生成基线。
sql> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; begin dbms_workload_repository.create_baseline_template ( day_of_week => 'monday', hour_in_day => 9, duration => 2, expiration => 30, start_time => '2017-04-14 09:00:00', end_time => '2017-12-31 11:00:00', baseline_name_prefix => 'baseline_2017_mondays_', template_name => 'template_2017_mondays', dbid => 41924548); end; / -- author : leshami -- blog : http://blog.csdn.net/leshami -- qq(weixin) : 645746311 --查看已经创建的基线样本 sql> 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 --------------------- --------- ------------------- ------------------- --------- ----------- -------- template_140414 single 2017-04-14 17:00:00 2017-04-14 19:00:00 template_2017_mondays repeating 2017-04-14 09:00:00 2017-12-31 11:00:00 monday 17 3
在上面的示例中我们创建了一个重复从2017年4月14日起的每周一(day_of_week)会自动生成一个基线，其开始时间为9点(hour_in_day)，其持续时间为2小时(duration)，有效期为30天(expiration)，整个基线的起止时间范围为：2017-04-14 09:00:00至2017-12-31 11:00:00，同时也指定了基线样本的名称以及基线前缀名称。
基线样本的删除
begin
dbms_workload_repository.drop_baseline_template (
template_name => 'template_140414',
dbid => 41924548);
end;
/
在上面的示例中我们删除之前创建的基线样本，通过指定样本名称即可。
三、awr空间占用
awr快照的数据全部填充在sysaux表空间。因此如果要保留的快照越久，意味着需要占用的sysaux空间越大。同时可以通过awrinfo.sql查看各个对象占用sysaux的详细信息。
sql> @?/rdbms/admin/awrinfo.sql this script will report general awr information ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ specify the report file name ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ the default report file name is awrinfo.txt. to use this name, press <return> to continue, otherwise enter an alternative. enter value for report_name: using the report name awrinfo.txt no errors. -- author : leshami no errors. -- blog : http://blog.csdn.net/leshami ~~~~~~~~~~~~~~~ -- qq : 645746311 awr info report ~~~~~~~~~~~~~~~ report generated at 19:48:53 on apr 14, 2017 ( friday ) in timezone +08:00 warning: non default awr setting! -------------------------------------------------------------------------------- snapshot interval is 60 minutes and retention is 8 days db_id db name host_platform inst startup_time last_ash_sid par ------------ ------- ----------------------- ---- ----------------- ------------ --- * 41924548 ora11g ydq - linux x86 64-bit 1 15:18:26 (04/07) 617410 no ######################################################## (i) awr snapshots information ######################################################## ***************************************************** (1a) sysaux usage - schema breakdown (dba_segments) ***************************************************** | | total sysaux size 617.4 mb ( 2% of 32,768.0 mb max with autoextend on ) | | schema sys occupies 206.6 mb ( 33.5% ) | schema xdb occupies 157.7 mb ( 25.5% ) | schema apex_030200 occupies 85.4 mb ( 13.8% ) | schema mdsys occupies 73.9 mb ( 12.0% )
## 四、生成awr报告
--单实例下生成awr报告
sql> @?/rdbms/admin/awrrpt.sql
--rac环境下生成awr报告
sql> @$oracle_home/rdbms/admin/awrgrpt.sql
--指定数据库实例生成awr报告
sql> @$oracle_home/rdbms/admin/awrrpti.sql
--生成sql语句awr报告
sql> @$oracle_home/rdbms/admin/awrsqrpt.sql
--指定实例生成sql语句awr报告
sql> @$oracle_home/rdbms/admin/awrsqrpi.sql
--生成比较的awr报告
sql> @$oracle_home/rdbms/admin/awrddrpt.sql
--rac环境下生成比较的awr报告
@$oracle_home/rdbms/admin/awrgdrpt.sql
五、awr相关的重要视图和数据字典
v$active_session_history : 显示活跃的数据库会话的活动，每秒采样一次
v$metric和v$metric_history：
提供度量数据来跟踪系统性能。视图被组织成好几个组，这些组定义在v$metricgroup视图中
dba_hist_active_sess_history
展示内存中活动会话历史信息
dba_hist_baseline
展示捕获的基线的信息
dba_hist_baseline_details
展示特定基线的明细信息
dba_hist_baseline_template
基线模板相关信息
dba_hist_database_instance
数据库环境
dba_hist_db_cache_advice
根据历史数据预测在不同的cache size下的物理读
dba_hist_dispatcher
每个snapshot下调度进程的信息
dba_hist_dyn_remaster_stats
动态remastering进程的统计信息
dba_hist_iostat_detail
按未见类型和功能来统计的历史i/o信息
dba_hist_shared_server_summary
共享服务器的统计信息
dba_hist_snapshot
快照信息
dba_hist_sql_plan
执行计划
dba_hist_wr_control
awr控制信息
六、更多参考
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。