Oracle 批量生成AWR报告
最近在分析核心业务系统数据库性能,发现收集AWR报告是个体力活,由于需要收集近一个月的AWR报告,每隔一个小时生成一个AWR报告,那么一天我就得生成24个报告,一个月就得生成720个,四个节点,每个AWR都得去执行@?/rdbms/admin/awrrpti.sql,还得输入几个参数。想想这个工作量,这画面太美。。。不敢直视啊!对于一个懒人,这怎么能忍,这个必须自动化。
我们首先来看下awrrpti.sql脚本的内容,其中的各种花招我们都略去,直接看到下面的代码行:
- -- call the table function to generate the report
- select output from table(dbms_workload_repository.&fn_name( :dbid,
- :inst_num,
- :bid, :eid,
- :rpt_options ));
- set serveroutput on;
- set feedback off;
- set linesize 300;
- prompt ***************************************************************;
- prompt usage:
- prompt 1.noninteractive : SQL>@awrrpt_batch.sql dbid instance_num start_snap end_snap;
- prompt 2.interactive : SQL>@awrrpt_batch.sql;
- prompt author : Darren_Guo
- prompt ***************************************************************;
- pause press enter to continue or ctrl-c to exit.;
- col snap_id for 999999999;
- col snap dbid 9999999999;
- col startup_time for a30;
- col begin_interval_time for a30;
- col end_interval_time for a30;
- select dbid,snap_id,instance_number,startup_time,begin_interval_time,end_interval_time from dba_hist_snapshot order by dbid,instance_number,snap_id,;
- exec dbms_output.put_line(chr(13)||chr(10)||'please enter dbid,inst_number,start and end snap_id:');
- declare
- v_dbid number;
- v_instance number;
- v_b_id number;
- v_e_id number;
- v_code number;
- v_errm varchar2(300);
- v_sql varchar2(300);
- v_html varchar2(20000);
- cur_awrrpt_html SYS_REFCURSOR;
- cur_snapshot SYS_REFCURSOR;
- fileID utl_file.file_type;
- v_filename varchar2(30);
- v_snap_id number;
- v_startup_time timestamp(3);
- v_begin_snap_time timestamp(3);
- v_end_snap_time timestamp(3);
- v_dpath varchar2(60);
- begin
- v_dbid:=&1;
- v_instance:=&2;
- v_b_id:=&3;
- v_e_id:=&4;
- dbms_output.put_line(chr(13)||chr(10)||'awrrpt report files:');
- for k in v_b_id..v_e_id-1 loop
- v_filename:='pmdb_'||k||'_'||(k+1)||'.html';
- fileID:=utl_file.fopen('DATA_PUMP_DIR',v_filename,'a',32767);
- v_sql:='select output from table(dbms_workload_repository.awr_report_html('||v_dbid||','||v_instance||','||k||','||(k+1)||',8))';
- open cur_awrrpt_html for v_sql;
- loop
- exit when cur_awrrpt_html%notfound;
- fetch cur_awrrpt_html into v_html;
- utl_file.put_line(fileID,v_html);
- end loop;
- utl_file.fclose(fileID);
- execute immediate 'select directory_path from dba_directories where directory_name=:dname' intov_dpath using 'DATA_PUMP_DIR';
- dbms_output.put_line(v_dpath||v_filename);
- end loop;
- exception
- when others then
- v_code:=SQLCODE;
- v_errm:=SQLERRM;
- dbms_output.put_line('ERROR CODE'||v_code||':'||v_errm);
- end;
- /