awr快照的设置
SQL> select snap_interval, retention from dba_hist_wr_control; SNAP_INTERVAL RETENTION --------------------------------------------------------------------------- --------------------------------------------------------------------------- +00000 01:00:00.0 +00008 00:00:00.0
这个 SQL 语句显示快照每小时采集一次，采集的数据保留 8天。要修改设置 – 例如，快照时间间隔为 30 分钟，保留时间为30天 ，可以发出以下命令。参数以分钟为单位。
begin dbms_workload_repository.modify_snapshot_settings ( interval => 30, retention => 30*24*60 ); end;SQL> 2 / PL/SQL procedure successfully completed. SQL> select snap_interval, retention from dba_hist_wr_control; SNAP_INTERVAL RETENTION --------------------------------------------------------------------------- --------------------------------------------------------------------------- +00000 00:30:00.0 +00030 00:00:00.0
在性能测试前后创建AWR快照进行分析
在测试开始前和结束后各创建一个AWR快照，然后生成 AWR比对报告。
SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. col END_INTERVAL_TIME form a30 col BEGIN_INTERVAL_TIME form a30 set linesize 200 select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,dbid from dba_hist_snapshot ORDER BY SNAP_ID; SNAP_ID BEGIN_INTERVAL_TIME DBID ...... 4 27-FEB-20 02.00.15.863 PM 1561251360 5 27-FEB-20 02.20.25.357 PM 1561251360 SQL> @?/rdbms/admin/awrddrpt
基线
创建现在基线
对于有代表性的性能测试可以创建一个基线，可以长期保存这个时段的性能数据，用于和以后的性能进行对比分析。
根据snap_id创建：
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE( start_snap_id IN NUMBER, end_snap_id IN NUMBER, baseline_name IN VARCHAR2, dbid IN NUMBER DEFAULT NULL, expiration IN NUMBER DEFAULT NULL);
根据时间创建：
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
start_time IN DATE,
end_time IN DATE,
baseline_name IN VARCHAR2,
dbid IN NUMBER DEFAULT NULL,
expiration IN NUMBER DEFAULT NULL);
dbid为空默认为本地的数据库
expiration参数为空表示用不过期，否则是以天为单位的数字。
SQL> set linesize 200 SQL> col baseline_name for a40 SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>4,end_snap_id=>5,baseline_name=>'test_bs1'); PL/SQL procedure successfully completed. SQL> select dbid,baseline_id,baseline_name,EXPIRATION,CREATION_TIME from dba_hist_baseline; DBID BASELINE_ID BASELINE_NAME EXPIRATION CREATION_TIME ---------- ----------- ---------------------------------------- ---------- ------------------- 1561251360 1 test_bs1 2020-02-27 14:47:54 1561251360 0 SYSTEM_MOVING_WINDOW 2020-02-27 11:11:03
创建未来基线
我们可以使用基线模板创建在将来要捕获的基线，CREATE_BASELINE_TEMPLATE存储过程定义单一基线或重复基线的捕获。
创建单一基线模板。
SQL> exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template(start_time=>TO_DATE('27-FEB-2020 17:30', 'DD-MON-YYYY HH24:MI'),end_time=>TO_DATE('27-FEB-2020 18:40', 'DD-MON-YYYY HH24:MI'),baseline_name =>'TEST_BS1',template_name =>'TEST_TP1'); PL/SQL procedure successfully completed. SQL> col REPEAT_INTERVAL for a10 SQL> select DBID,TEMPLATE_ID,TEMPLATE_NAME,TEMPLATE_TYPE,DURATION,EXPIRATION,REPEAT_INTERVAL from dba_hist_baseline_template; DBID TEMPLATE_ID TEMPLATE_NAME TEMPLATE_ DURATION EXPIRATION REPEAT_INT ---------- ----------- ------------------------------ --------- ---------- ---------- ---------- 1561251360 1 TEST_TP1 SINGLE
重复基线模板和单一基线模板稍微有点不同，因为它需要调度信息，START_TIME和END_TIME参数分别在模板激活和释放时定义，DAY_OF_WEEK，HOUR_IN_DAY和DURATION定义产生基线的日期，时间和持续时间，因为模板会产生多个基线，基线名是以BASELINE_NAME_PREFIX开始的，下面的基线模板例子将在未来12个月内运行，每周星期一10:00至11:00收集基线。
SQL> begin 2 DBMS_WORKLOAD_REPOSITORY.create_baseline_template( day_of_week=>'MONDAY', hour_in_day=>10,duration=>1, start_time=>SYSDATE, end_time=>ADD_MONTHS(SYSDATE, 12), baseline_name_prefix =>'monday_morning_bl', template_name=>'Monday_morning_1', expiration=> NULL ); 3 4 5 6 7 8 9 10 11 end; 12 / PL/SQL procedure successfully completed. SQL> select DBID,TEMPLATE_ID,TEMPLATE_NAME,TEMPLATE_TYPE,DURATION,EXPIRATION,REPEAT_INTERVAL from dba_hist_baseline_template; DBID TEMPLATE_ID TEMPLATE_NAME TEMPLATE_ DURATION EXPIRATION REPEAT_INT ---------- ----------- ------------------------------ --------- ---------- ---------- ---------- 1561251360 1 TEST_TP1 SINGLE 1561251360 2 Monday_morning_1 REPEATING 1 FREQ=WEEKL Y;INTERVAL =1;BYDAY=M ON;BYHOUR= 10;BYMINUT E=0;BYSECO ND=0
删除基线模板
SQL> exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name =>'TEST_TP1'); PL/SQL procedure successfully completed.
将两个需要比较的数据库的awr数据导入到一个数据库上进行比较
导出awr数据
SQL> @?/rdbms/admin/awrextr.sql ~~~~~~~~~~~~~ AWR EXTRACT ~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ This script will extract the AWR data for a range of snapshots ~ ~ into a dump file. The script will prompt users for the ~ ~ following information: ~ ~ (1) database id ~ ~ (2) snapshot range to extract ~ ~ (3) name of directory object ~ ~ (4) name of dump file ~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ......... Enter value for directory_name: DATA_PUMP_DIR Using the dump file prefix: awrlocal_nvme1_31 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR extract dump file will be located | in the following directory/file: | /u01/app/oracle/admin/orcl/dpdump/ | awrlocal_nvme1_31.dmp | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | *** AWR Extract Started ... | | This operation will take a few moments. The | progress of the AWR extract operation can be | monitored in the following directory/file: | /u01/app/oracle/admin/orcl/dpdump/ | awrlocal_nvme1_31.log |
导入awr数据
@?/rdbms/admin/awrload.sql
不能导入到同一个数据库中
begin * ERROR at line 1: ORA-20105: unable to move AWR data to SYS ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2984 ORA-20107: not allowed to move AWR data for local dbid ORA-06512: at line 3 ... Dropping AWR_STAGE user End of AWR Load
基线对比
可以对两个时间段的性能数据进行对比：
SQL> spool awr_compare_report.html set echo off; set veri off; set feedback off; set head off set verify off set lines 8000 select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(1561251360,1,2,3, 1561251360,1,9,10)); spool off SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 <html lang="en"><head><title>AWR Compare Period Report for (1) DB: ORCL, Inst: orcl, Snaps: 2-3 (2) DB: ORCL, Inst: orcl, Snaps: 9-10</title> <style type="text/css"> ......
从对比结果中可以看到指标的差异：
