oracle11g awr报告设置

1.查询当前的设置

SQL> select * from dba_hist_wr_control;      DBID----------SNAP_INTERVAL---------------------------------------------------------------------------RETENTION---------------------------------------------------------------------------TOPNSQL----------1448896496+00000 01:00:00.0+00008 00:00:00.0DEFAULT

字段snap_interval表示时间间隔，这里为每1小时收集一次AWR.

2.查看moving_window_size的大小

2.1.关于awr报告中的基准线(baseline)

  A baseline is created with the CREATE_BASELINE procedure, A baseline is simply performance data for

a set of snapshots that is preserved and used for comparisons with other similar workload periods when

performance problems occur. You can review the existing snapshots in the DBA_HIST_SNAPSHOT view to

determine the range of snapshots that you want to use.

即baseline使用dbms_workload_repository.create_baseline存储过程创建，baseline生成两次快照统计信息值的

对比数据，用以确定性能问题或实施性能调整后观察调整效果，可以查询DBA_HIST_SNAPSHOT视图用于确定

要保留那些baseline.

创建一个baseline:

SQL> Begin  2  dbms_workload_repository.create_baseline(  3  start_snap_id => 7,  4  end_snap_id => 8,  5  baseline_name => 'HIS_SLOWLY');  6  End;  7  /PL/SQL procedure successfully completed.

可以通过DBA_HIST_BASELINE查看baseline信息

要注意Bug 4597354 在创建基线数据的时候，对性能有很大影响。在一个非常繁忙的系统上不要进行此操作。

SQL> select baseline_id,baseline_name from dba_hist_baseline;         1 HIS_SLOWLY          0 SYSTEM_MOVING_WINDOW

通过dbms_workload_repository.select_baseline_details函数也能返回相关信息

SQL> Select baseline_id,start_snap_time,end_snap_id From Table((Select dbms_workload_repository.select_baseline_details(l_baseline_id => 1) From dual ));

也可以通过dbms_workload_repository.select_baseline_meric函数查看两次快照统计信息的差异对比

SQL> Select baseline_id,start_snap_time,end_snap_id From Table((Select dbms_workload_repository.select_baseline_details(l_baseline_id => 1) From dual ));

2.2 如果查询moving_window_size的大小大于要修改为的保留天数，则必须执行一下命令进行修改，这里修改为3

SQL> select baseline_name,start_snap_time,end_snap_time,moving_window_size from dba_hist_baseline_details;

SQL> begin DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(WINDOW_SIZE=>3);  2  end;   3  /PL/SQL procedure successfully completed.

3. 修改awr设置：

modify_snapshot_settings的三个参数：

Retention ：设置快照保存的时间，单位是分钟。可设置的值最小为1天，最大为100年。设置该参数值为0的话，就表示永久保留收集的快照信息。
Interval ：设置快照收集的频率，以分钟为单位。可设置的值最小为10分钟，最大为1年。如果设置该参数值为0，就表示禁用AWR特性。
Topnsql ：指定收集的比较占用资源的SQL数量，可设置的值最小为30，最大不超过100000000。

SQL> begin  2  dbms_workload_repository.modify_snapshot_settings(INTERVAL =>240,retention =>3*24*60);  3  end;  4  /PL/SQL procedure successfully completed.

修改的的单位均为分钟，示例中即表示awr收集频率为4小时一次，awr快照每次保留3天。

总结:Oracle10 g后新增表空间SYSAUX表空间，默认情况下AWR的信息每隔一个小时收集一次，并保存7天。这些数据存放在SYSAUX

表空间中，如果SYSAUX空间严重不足，将会在alter日志报ORA-1683和ORA-1688错误，建议每修改为每隔4小时收集一次并保留3天的数据。

4. 生成awr报告

手工创建一个快照(可选)：

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();PL/SQL procedure successfully completed.

生成awr报告：

(注意)如果是除sys用户生成报告需要进行相应的授权

SQL> GRANT EXECUTE ON sys.dbms_workload_repository TO <oracle用户>;

SQL> @/u01/app/oracle/product/11.2.0/rdbms/admin/awrrpt.sqlCurrent Instance~~~~~~~~~~~~~~~~   DB Id    DB Name      Inst Num Instance----------- ------------ -------- ------------ 1448896496 ORCL                1 orclSpecify the Report Type~~~~~~~~~~~~~~~~~~~~~~~Would you like an HTML report, or a plain text report?Enter 'html' for an HTML report, or 'text' for plain textDefaults to 'html'Enter value for report_type: htmlType Specified:  html     --指定awr报告的格式，默认为htmlInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   DB Id     Inst Num DB Name      Instance     Host------------ -------- ------------ ------------ ------------* 1448896496        1 ORCL         orcl         centos6Using 1448896496 for database IdUsing          1 for instance numberSpecify the number of days of snapshots to choose from~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Entering the number of days (n) will result in the most recent(n) days of snapshots being listed.  Pressing <return> withoutspecifying a number lists all completed snapshots.Enter value for num_days: 2     --选择快照的时间范围Listing the last 2 days of Completed Snapshots                                                        SnapInstance     DB Name        Snap Id    Snap Started    Level------------ ------------ --------- ------------------ -----orcl         ORCL                10 09 Oct 2016 19:00      1                                 11 09 Oct 2016 19:52      1Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 10     --开始快照IDBegin Snapshot Id specified: 10Enter value for end_snap: 11       --结束快照IDEnd   Snapshot Id specified: 11Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is awrrpt_1_10_11.html.  To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name: /home/oracle/20161009.html  --生成快照名

参考：http://www.askoracle.org/oracle/DBA/845.html

          http://blog.itpub.net/223653/viewspace-1329476/

          http://blog.itpub.net/7839206/viewspace-1007358/