Home  >  Article  >  Database  >  自动生成AWR1(sql)

自动生成AWR1(sql)

WBOY
WBOYOriginal
2016-06-07 15:59:051120browse

目前从网上搜索到比较靠谱的2种AWR自动生成方法 第一SQL法: 参考sql链接:http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql -- ------------------------------------------------------------------------------------- File Name :

目前从网上搜索到比较靠谱的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查看工具

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article:CentOS下挂载U盘Next article:MongoDB:聚集管道