作为UNIX系统中的Oracle数据库管理员，时刻监控操作系统的性能无疑是非常重要的，幸运的是UNIX系统提供了大量的监控命令，比如vmstat, iostat, sar, top等等，这些监控命令均以字符界面输出结果，再加上UNIX系统中shell的强大分析功能，这样我们只需要编写一些脚本就可以实现自动的后台监控，当有问题的时候再自动发送邮件给DBA。
其实相类似的一些监控脚本可能已经随处可见了，但是本文提到的一个新的思路，就是利用Oracle数据库的statspack空间来存储监控的结果，再利用数据库天生的检索优势，这样比以往靠shell分析甚或是人工分析生成的监控结果文件要更加轻松，智能，同时也能实现更持久和更广泛的监控。
本文大部分素材来源自Donald K. Burleson的Oracle9i UNIX Administration Handbook，但是对于脚本中的一些错误和不合理的地方作了修改，并且添加了一些功能，本文的测试环境全部基于Sun Solaris 8 Sparc 64bit + Oracle9.2.0.5。
本文第一版主要以监控内存消耗和CPU等待的vmstat命令为例，将陆续添加iostat等其它命令的监控脚本。
二、 思路
首先安装statspack，然后手工创建一个用于存储vmstat命令输出结果的表，再作一个shell定时执行vmstat，将结果全部插入数据库表中，最后就是通过SQL定时分析表中的数据，超过预先指定的门限值就告警。同时根据表中的数据，我们还能作出系统性能的趋势报告。
三、 vmstat脚本及步骤
1. 安装statspack
sqlplus “/ as sysdba”
SQL> @?/rdbms/admin/spcreate.sql
按照提示设定perfstat用户的密码之后，由于是10g，我选择了SYSAUX表空间作为perfstat用户的默认表空间，而没有另行创建表空间。
2. 创建stats$vmstat表
SQL> connect perfstat/perfstat
SQL> @create_vmstat_tab.sql
# create_vmstat_tab.sql
drop table stats$vmstat;
create table stats$vmstat
(
start_date date,
duration number,
server_name varchar2(20),
running_queue number,
waiting_queue number,
swap_in number,
swap_out number,
kbytes_page_in number,
Kbytes_page_out number,
page_scan number,
user_cpu number,
system_cpu number,
idle_cpu number,
wait_cpu number
)
tablespace sysaux
storage (initial 10m
next 1m
pctincrease 0)
;
comment on column stats$vmstat.start_date
is ''监控时间'';
comment on column stats$vmstat.duration
is ''监控时长'';
comment on column stats$vmstat.server_name
is ''服务器名称'';
comment on column stats$vmstat.running_queue
is ''执行队列'';
comment on column stats$vmstat.waiting_queue
is ''等待队列'';
comment on column stats$vmstat.swap_in
is ''每秒平均交换进LWPs数'';
comment on column stats$vmstat.swap_out
is ''交换出进程数'';
comment on column stats$vmstat.kbytes_page_in
is ''换页进字节(K)'';
comment on column stats$vmstat.kbytes_page_out
is ''换页出字节(K)'';
comment on column stats$vmstat.page_scan
is ''换页守护进程扫描页数'';
comment on column stats$vmstat.user_cpu
is ''用户操作占用的CPU'';
comment on column stats$vmstat.system_cpu
is ''系统操作占用的CPU'';
comment on column stats$vmstat.idle_cpu
is ''CPU空闲率'';
comment on column stats$vmstat.wait_cpu
is ''CPU等待率(AIX独有)'';
3. 创建vmstat目录
在oracle用户主目录下创建用于存放所有相关脚本的vmstat目录。
$ cd
$ mkdir vmstat
4. 创建get_vmstat.ksh脚本
改脚本用于定时执行vmstat命令并且将结果存入数据库。
# get_vmstat.ksh
#!/bin/ksh
#----------------------------------------
# 首先设定环境变量，根据实际环境修改. . . .
#----------------------------------------
ORACLE_SID=kamusdb
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep \^$ORACLE_SID:|cut -f2 -d'':''`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
SERVER_NAME=`uname -a|awk ''{print $2}''`
typeset -u SERVER_NAME
export SERVER_NAME
#----------------------------------------
# 每5分钟运行一次vmstat(300秒)，可以根据实际情况修改 . . . .
#----------------------------------------
SAMPLE_TIME=300
#----------------------------------------
# 脚本一旦运行将不会停止，除非关闭操作系统 . . . .
# -S参数表示监控swap空间的情况，报告si，so列
# msg$$中的$$表示一个任意2位数字，系统自动生成
#----------------------------------------
while true
do
vmstat –S ${SAMPLE_TIME} 2 > /tmp/msg$$
#----------------------------------------
# Solaris系统的vmstat没有wait CPU统计，所以我们在那一列中存入0
# $1, $2, $6, $7等数字分别表示vmstat输出中的第几列，
# 每个Unix系统中的vmstat输出可能都不一样，
# 所以修改这些列号，就可以应对不同的操作系统。
#----------------------------------------
cat /tmp/msg$$|sed 1,3d | awk ''{ printf("%s %s %s %s %s %s %s %s %s %s \n", $1, $2, $6, $7, $8, $9, $12, $20, $21, $22) }'' | while read RUNQUE WAITQUE SWAPIN SWAPOUT PAGE_IN PAGE_OUT PAGE_SCAN USER_CPU SYSTEM_CPU IDLE_CPU
do
$ORACLE_HOME/bin/sqlplus perfstat/perfstat <<EOF
insert into perfstat.stats\$vmstat
values (
SYSDATE,
$SAMPLE_TIME,
''$SERVER_NAME'',
$RUNQUE,
$WAITQUE,
$SWAPIN,
$SWAPOUT,
$PAGE_IN,
$PAGE_OUT,
$PAGE_SCAN,
$USER_CPU,
$SYSTEM_CPU,
$IDLE_CPU,
0
);
commit;
EXIT
EOF
done
done
#----------------------------------------
#删除临时文件
#----------------------------------------
rm /tmp/msg$$
5. 创建run_vmstat.ksh脚本
该脚本放在crontab中，用来定时检查get_vmstat.ksh脚本有没有正常运行，如果在运行，那么不作任何动作，如果不在运行，那么就运行get_vmstat.ksh脚本。这个脚本的意义在于防止重新启动操作系统之后忘记运行get_vmstat.ksh脚本。
# run_vmstat.ksh
#!/bin/ksh
#----------------------------------------
# 首先设定环境变量，根据实际环境修改. . . .
#----------------------------------------
SCRIPT_PATH=`echo ~oracle/vmstat`
export SCRIPT_PATH
ORACLE_SID=kamusdb
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d'':''`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
#----------------------------------------
# 作更进一步地控制，在系统没有操作的时间内停止监控
# 晚上8点到第二天凌晨8点之间停止监控
# 其它时间如果get_vmstat不在运行，就运行它
#----------------------------------------
HOUR=`date +"%H"`
check_stat=`ps -ef|grep get_vmstat|grep -v grep|wc -l`
vmstat_num=`expr $check_stat`
if [ $HOUR -gt 19 ] || [ $HOUR -lt 8 ]
then
if [ $vmstat_num -gt 0 ]
then kill -9 `ps -ef|grep get_vmstat|grep -v grep|awk ''{print $2 }''` > /dev/null
fi
else
if [ $vmstat_num -le 0 ]
then nohup $SCRIPT_PATH/get_vmstat.ksh > /dev/null 2>&1 &
fi
fi
6. 创建crontab作业，定时执行run_vmstat.ksh脚本
该作业每半小时运行一次。
$ crontab –l > oracle.cron
$ echo ’00,30 * * * * /export/home/oracle/vmstat/run_vmstat.ksh >> /export/home/oracle/vmstat/run.lst 2>&1’ >> oracle.cron
$ crontab oracle.cron
7. 分析数据
至此已经完成了定时运行vmstat和在数据库中存储vmstat结果的步骤。自然，仅仅是搜集了统计信息是远远不够的，下面我们要分析搜集来的信息，产生操作系统的性能报告。
将报告分为以下几类，分别用脚本实现。
异常报告：显示超过了门限值的时间段（vmstat_alert.ksh + vmstat_alert.sql）
每小时趋势报告：显示一天内每小时的系统平均利用情况（rpt_vmstat_hr.sql）
周趋势报告：显示每天的系统平均利用情况（rpt_vmstat_dy.sql）
长期趋势报告：显示系统性能的一个长期趋势线（rpt_vmstat.sql））
1) 异常报告
# vmstat_alert.sql
REM ----------------------------------------
REM 该SQL用于报告Oracle环境中每个服务器的异常情况。
REM 根据get_vmstat.ksh脚本得到的信息，报告每个小时的平均值。
REM DBA发现异常时间段，则可以深入检查每5分钟的详细数据。
REM 在这个脚本中接受一个参数，表示需要报告的时间跨度。
REM 对于runing queue的门限值应该设置为CPU数，
REM 表示如果出现大量等待执行的任务就报警，这通常表示CPU负载过重。
REM 对于page scan(sr)的门限值设置为1，
REM 表示只要出现page daemon扫描页就报警，这通常表示内存不足。
REM 对于CPU利用率，设置为70，表示超过70%以上的利用率才报警。
REM 比如运行vmstat_alert 7
REM 表示输出当前日期之前7天之内的执行队列大于4，
REM sr大于1，CPU利用率超过70%的按照小时统计的报告。
REM ----------------------------------------
set lines 80;
set pages 999;
set feedback off;
set verify off;
column my_date heading ''date hour'' format a20
column c2 heading waitq 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
ttitle ''run queue > CPUs|May indicate an overloaded CPU|When runqueue exceeds the number of CPUs| on the server, tasks are waiting for service.'';
select
server_name,
to_char(start_date,''YY/MM/DD HH24'') my_date,
avg(running_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
running_queue > 4
and start_date > sysdate-&1
group by
server_name,
to_char(start_date,''YY/MM/DD HH24'')
ORDER BY
server_name,
to_char(start_date,''YY/MM/DD HH24'')
;
ttitle ''page_scan > 1|May indicate overloaded memory|Whenever Unix performs a page-in, the RAM memory | on the server has been exhausted and swap pages are being used.'';
select
server_name,
to_char(start_date,''YY/MM/DD HH24'') my_date,
avg(running_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
page_scan > 1
and start_date > sysdate-&1
group by
server_name,
to_char(start_date,''YY/MM/DD HH24'')
ORDER BY
server_name,
to_char(start_date,''YY/MM/DD HH24'')
;
ttitle ''user+system CPU > 70%|Indicates periods with a fully-loaded CPU subssystem.|Periods of 100% utilization are only a | concern when runqueue values exceeds the number of CPs on the server.'';
select
server_name,
to_char(start_date,''YY/MM/DD HH24'') my_date,
avg(running_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
(user_cpu + system_cpu) > 70
and start_date > sysdate-&1
group by
server_name,
to_char(start_date,''YY/MM/DD HH24'')
ORDER BY
server_name,
to_char(start_date,''YY/MM/DD HH24'')
;
# vmstat_alert.ksh
#----------------------------------------
# 可以将此shell加入cron中，每天7点运行
#----------------------------------------
#!/bin/ksh
#----------------------------------------
# 首先设定环境变量，根据实际环境修改. . . .
# 接受一个参数输入，表示当前要报告的数据库SID
#----------------------------------------
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d'':''`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
SCRIPT_PATH=`echo ~oracle/vmstat`
export SCRIPT_PATH
sqlplus perfstat/perfstat<<!
spool /tmp/vmstat_$ORACLE_SID.lst
@$SCRIPT_PATH/vmstat_alert 7 4
spool off;
exit;
!
#----------------------------------------
# 检查vmstat_alert.sql的输出结果
# 正常情况应该只包含下面2行
# SQL> @/export/home/oracle/vmstat/vmstat_alert 7
# SQL> spool off;
# 如果超过3行则表示有异常值，那么直接邮件给DBA
#----------------------------------------
check_stat=`cat /tmp/vmstat_$ORACLE_SID.lst|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -gt 3 ]
then
cat /tmp/vmstat_$ORACLE_SID.lst|mailx -s "System vmstat alert" kamus@itpub.net some_other_dba@mail.address.net
fi
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'');
4) 长期趋势报告
# rpt_vmstat.sql
REM ----------------------------------------
REM 该SQL用于报告Oracle环境中每个服务器日平均的CPU使用率
REM 报告范围为已搜集的所有数据
REM ----------------------------------------
connect perfstat/perfstat;
set pages 9999;
set feedback off;
set verify off;
column server_name heading ''server'' format a10
column my_date heading ''date'' 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,
trunc(start_date) my_date,
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
group BY server_name, trunc(start_date)
order by server_name, trunc(start_date);
