1.查询一下snap信息
column begin_interval_time format a30
column end_interval_time format a30
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by 1 ;


.......................................
      7764 08-MAR-17 09.00.54.766 AM      08-MAR-17 10.00.58.343 AM
      7765 08-MAR-17 10.00.58.343 AM      08-MAR-17 11.01.02.363 AM
      7766 08-MAR-17 11.01.02.363 AM      08-MAR-17 12.00.55.675 PM
      7767 08-MAR-17 12.00.55.675 PM      08-MAR-17 01.00.27.035 PM
      7768 08-MAR-17 01.00.27.035 PM      08-MAR-17 02.00.30.535 PM
      7769 08-MAR-17 02.00.30.535 PM      08-MAR-17 03.00.48.679 PM


默认都是存放8天的


2.创建base line
SQL> begin
  2   dbms_workload_repository.create_baseline(
  3   start_time => TO_DATE('2017-03-07 12:00:00','YYYY-MM-DD HH24:Mi:ss'),
  4   end_time => TO_DATE('2017-03-07 14:00:00','YYYY-MM-DD HH24:Mi:ss'),
  5   baseline_name => 'mybase',
  6   expiration => 30 );
  7   end;
  8   /


PL/SQL procedure successfully completed.


2017-03-07 12:00:00-14:00:00  这个时间段，我们认为 是系统正常的时期,我们作为 baseline,以后数据库出现波动时,我们同这个作对比


3.收集一下base line 的统计信息


SQL> column baseline_name format a30
SQL> select instance_number,baseline_id,baseline_name,baseline_type from dba_hist_baseline_details;


INSTANCE_NUMBER BASELINE_ID BASELINE_NAME                  BASELINE_TYPE
--------------- ----------- ------------------------------ -------------
              1           2 mybase                         STATIC
              1           0 SYSTEM_MOVING_WINDOW           MOVING_WINDOW




SQL> begin
  2  dbsnmp.bsln_internal.compute_statistics(2,1);
  3   end;
  4  /


PL/SQL procedure successfully completed.


4.高潮来了,现在对比吧


需要使用oem或者em 的GUI界面去对比
或者使用下面两个函数对比
dbms_workload_repository.awr_diff_report_html(
dbid1     IN NUMBER,
inst_num1 IN NUMBER,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN NUMBER,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED;


-------------------------------------------------------------------------
DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_DIFF_REPORT_HTML (
   dbid1        IN    NUMBER,
   inst_num1    IN    AWRRPT_INSTANCE_LIST_TYPE,
   bid1         IN    NUMBER,
   eid1         IN    NUMBER,
   dbid2        IN    NUMBER,
   inst_num2    IN    AWRRPT_INSTANCE_LIST_TYPE,
   bid2         IN    NUMBER,
   eid2         IN    NUMBER)
  RETURN awrrpt_html_type_table PIPELINED;


DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_DIFF_REPORT_HTML (
   dbid1        IN    NUMBER,
   inst_num1    IN    VARCHAR2,
   bid1         IN    NUMBER,
   eid1         IN    NUMBER,
   dbid2        IN    NUMBER,
   inst_num2    IN    VARCHAR2,
   bid2         IN    NUMBER,
   eid2         IN    NUMBER)
  RETURN awrrpt_html_type_table PIPELINED;




上面可传参数确实没有baseline 信息.所以只可使用 em/oem去对比,各位小伙伴也可以自己去研究一下.




其它
删除 awr baseline


SQL>  begin
  2  dbms_workload_repository.drop_baseline('mybase');
  3   end;
  4  /


PL/SQL procedure successfully completed.


baseline 模板等就不进行测试啦