目前从网上搜索到比较靠谱的2种AWR自动生成方法

第一SQL法：

参考sql链接：http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql

-- ------------------------------------------------------------------------------------- File Name    : http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql-- Author       : DR Timothy S Hall-- Description  : Generates AWR reports for all snapsots between the specified start and end point.-- Requirements : Access to the v$ views, UTL_FILE and DBMS_WORKLOAD_REPOSITORY packages.-- Call Syntax  : Create the directory with the appropriate path.--                Adjust the start and end snapshots as required.--                @generate_multiple_awr_reports.sql-- Last Modified: 02/08/2007-- -----------------------------------------------------------------------------------CREATE OR REPLACE DIRECTORY awr_reports_dir AS ‘/tmp/‘;DECLARE  -- Adjust before use.  l_snap_start       NUMBER := 1;  l_snap_end         NUMBER := 10;  l_dir              VARCHAR2(50) := ‘AWR_REPORTS_DIR‘;    l_last_snap        NUMBER := NULL;  l_dbid             v$database.dbid%TYPE;  l_instance_number  v$instance.instance_number%TYPE;  l_file             UTL_FILE.file_type;  l_file_name        VARCHAR(50);BEGIN  SELECT dbid  INTO   l_dbid  FROM   v$database;  SELECT instance_number  INTO   l_instance_number  FROM   v$instance;      FOR cur_snap IN (SELECT snap_id                   FROM   dba_hist_snapshot                   WHERE  instance_number = l_instance_number                   AND    snap_id BETWEEN l_snap_start AND l_snap_end                   ORDER BY snap_id)  LOOP    IF l_last_snap IS NOT NULL THEN      l_file := UTL_FILE.fopen(l_dir, ‘awr_‘ || l_last_snap || ‘_‘ || cur_snap.snap_id || ‘.htm‘, ‘w‘, 32767);            FOR cur_rep IN (SELECT output                      FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))      LOOP        UTL_FILE.put_line(l_file, cur_rep.output);      END LOOP;      UTL_FILE.fclose(l_file);    END IF;    l_last_snap := cur_snap.snap_id;  END LOOP;  EXCEPTION  WHEN OTHERS THEN    IF UTL_FILE.is_open(l_file) THEN      UTL_FILE.fclose(l_file);    END IF;    RAISE; END;/

具体做法是：

1.先将上面的SQL保存到DB主机上，generate_multiple_awr_reports.sql

2.查看系统生成的AWR快照：

SELECT snap_id, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot；

3.将第二步所得结果中的snap_id的值手动修改到generate_multiple_awr_reports.sql,替换掉1和10这2个数：

l_snap_start NUMBER := 1;

 l_snap_end NUMBER := 10;

4.在sqlplus中调用sql文件

sql>@generate_multiple_awr_reports.sql

5.最后去/tmp目录html的文件是否生成

结论：其实该方法只适合于替换sqlplus交互命令行，实际作用不是很大，现在TOAD等工具直接提供了AWR查看工具

自动生成AWR1（sql）