用Oracle存储来分析Unix系统的性能

核心提示： 1． 创建crontab作业，每天7点定时执行vmstat_alert.ksh脚本$ crontab –l > oracle.cron$ echo ’00 7 * * * /export/home/oracle/vmstat/vmstat_alert.ksh

1． 创建crontab作业，每天7点定时执行vmstat_alert.ksh脚本

$ crontab –l > oracle.cron
$ echo ’00 7 * * * /export/home/oracle/vmstat/vmstat_alert.ksh kamusdb
>> /export/home/oracle/vmstat/runalert.lst 2>&1’ >> oracle.cron
$ crontab oracle.cron

2) 每小时趋势报告

# rpt_vmstat_hr.sql
REM ----------------------------------------
REM 该SQL用于报告Oracle环境中每个服务器一天内小时平均的CPU使用率
REM 接受一个参数，用于指定需要报告的日期，格式为YYYYMMDD
REM ----------------------------------------
connect perfstat/perfstat;
set pages 9999;
set feedback off;
set verify off;
column server_name heading 'server' format a10
column my_hour heading 'hour' format a10
column c2 heading runq format 9999
column c3 heading pg_in format 9999
column c4 heading pg_ot format 9999
column c5 heading usr format 9999
column c6 heading sys format 9999
column c7 heading idl format 9999
column c8 heading wt format 9999
select server_name,
to_char(start_date, 'HH24') my_hour,
avg(runing_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu + system_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from stats$vmstat
where trunc(start_date) = to_date(&1,'yyyymmdd')
group BY server_name,to_char(start_date, 'HH24')
order by server_name,to_char(start_date, 'HH24');

3) 周趋势报告

# rpt_vmstat_dy.sql
REM ----------------------------------------
REM 该SQL用于报告本周内Oracle环境中每个服务器的日平均CPU使用率
REM ----------------------------------------
connect perfstat/perfstat;
set pages 9999;
set feedback off;
set verify off;
column server_name heading 'server' format a10
column my_day heading 'day' format a20
column c2 heading runq format 9999
column c3 heading pg_in format 9999
column c4 heading pg_ot format 9999
column c5 heading usr format 9999
column c6 heading sys format 9999
column c7 heading idl format 9999
column c8 heading wt format 9999
select server_name,
to_char(start_date, 'day') my_day,
avg(runing_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu + system_cpu) c5,
avg(idle_cpu) c7
from stats$vmstat
where trunc(start_date) >= trunc(next_day(sysdate, 'MONDAY')) - 7
and trunc(start_date) < trunc(next_day(sysdate, 'MONDAY'))
group BY server_name, to_char(start_date, 'day')
order by server_name, to_char(start_date, 'day');

好了，有了这些报告。我们就只需要编写一些脚本就可以实现自动的后台监控，当有问题的时候再自动发送邮件给DBA。