ITPub博客

首页 > Linux操作系统 > Linux操作系统 > AWR 修改为自动化脚本

AWR 修改为自动化脚本

原创 Linux操作系统 作者:wuft2003 时间:2011-08-04 15:44:04 0 删除 编辑

把交互式的awr 修改成自动化脚本

1. 查看参数 show parameter statistics;
    statistics_level                     string      TYPICAL 
    statistics_level 默认是typical,在10g中表监控是激活的,非特殊情况,否则不要修改。如果STATISTICS_LEVEL设置为basic,不仅不能监控表,而且将禁掉如下一些10g的新功能:
            ASH(Active Session History)
            ASSM(Automatic Shared Memory Management)
            AWR(Automatic Workload Repository)
            ADDM(Automatic Database Diagnostic Monitor)

2. /opt/app/oracle/product/10G/rdbms/admin/awrrpt.sql
调用awrrpti.sql, 需要的是修改awrrpti.sql 程序,如何修改
在awrrpti.sql 里面有详细的描述
========================================
Rem    NOTES
Rem      Run as SYSDBA.  Generally this script. should be invoked by awrrpt,
Rem      unless you want to pick a database other than the default.
Rem
Rem      If you want to use this script. in an non-interactive fashion,
Rem      without executing the script. through awrrpt, then
Rem      do something similar to the following:
Rem
Rem      define  inst_num     = 1;
Rem      define  num_days     = 3;
Rem      define  inst_name    = 'Instance';
Rem      define  db_name      = 'Database';
Rem      define  dbid         = 4;
Rem      define  begin_snap   = 10;
Rem      define  end_snap     = 11;
Rem      define  report_type  = 'text';
Rem      define  report_name  = /tmp/swrf_report_10_11.txt
Rem      @@?/rdbms/admin/awrrpti
==============================
-- ***************************************************
--   Customer-customizable report settings
--   Change these variables to run a report on different statistics
-- ***************************************************
-- The default number of days of snapshots to list when displaying the
-- list of snapshots to choose the begin and end snapshot Ids from.
--
--   List all snapshots
-- define num_days = '';
--
--   List no (i.e. 0) snapshots
-- define num_days = 0;
--
-- List past 3 day's snapshots
-- define num_days = 3;
--
-- Reports can be printed in text or html, and you must set the report_type
-- in addition to the report_name
--
-- Issue Report in Text Format
--define report_type='text';
--
-- Issue Report in HTML Format
--define report_type='html';

-- Optionally, set the snapshots for the report.  If you do not set them,
-- you will be prompted for the values.
--define begin_snap = 545;
--define end_snap   = 546;

-- Optionally, set the name for the report itself
--define report_name = 'awrrpt_1_545_546.html'

-- ***************************************************
--   End customer-customizable settings
-- **************************************************


我想收集系统在批处理时间的处理情况:(对于RAC库,只要是在不同的实例上执行就可以,脚本无需修改)
select snap_interval, retention from dba_hist_wr_control
    保留30天, 间隔时间是1小时
======================================================================================
cp awrrpt.sql auto_awrrpt.sql
cp awrrpti.sql auto_awrrpti.sql

auto_awrrpt.sql 仅仅修改为调用auto_awrrpt,既由@@awrrpti 改为@@auto_awrrpti
auto_awrrpti.sql 主要的修改有:
--   List no (i.e. 0) snapshots
   define num_days = 0;

 

-- Issue Report in HTML Format
   define report_type='html';


-- Optionally, set the snapshots for the report.  If you do not set them,
-- you will be prompted for the values.
--define begin_snap = 545;
--define end_snap   = 546;
prompt "================================================="
col begin_snap new_value begin_snap for 999999 ;
col end_snap new_value end_snap for 999999 ;

select min(snap_id) begin_snap,
max(snap_id) end_snap
from dba_hist_snapshot
where instance_number = &inst_num
and to_char(end_interval_time, 'YYYYMMDDHH24MISS') between to_char(sysdate-1, 'YYYYMMDD')||'174000' and to_char(sysdate-1, 'YYYYMMDD')||'2330
00';


---- 获取批处理时间段的snap_id

--define report_name = 'awrrpt_1_545_546.html'
  col report_name new_value report_name noprint;
  select 'awrrpt_'||&inst_num||'_'||&begin_snap||'_'||&end_snap||'.html' report_name from dual;


定时执行脚本:
sqlplus "/as sysdba"<@auto_awrrpt.sql
!

把脚本定制到crontab 中

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21634752/viewspace-704112/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-12

  • 博文量
    295
  • 访问量
    324150