利用Oracle数据库存储和分析Unix操作系统的性能-数据库专栏,ORACLE

作者简介：

张乐奕，通常使用的网名为kamus，也曾用过seraphim，现在任职于北京某大型软件公司，oracle数据库dba，主要负责证券行业的核心交易系统数据库管理及维护工作。

热切关注oracle技术和相关操作系统技术，出没于各大数据库技术论坛，目前是中国最大的oracle技术论坛www.itpub.net的数据库管理版版主。

我的邮件地址：kamus@itpub.net

我的msn: kamusis@hotmail.com

期望与各位朋友讨论oracle相关技术。

本人所有文章，不经许可，不得转载。

目录

一、       前言… 4

二、       思路… 4

三、       vmstat脚本及步骤… 4

1.       安装statspack. 4

2.       创建stats$vmstat表… 4

3.       创建vmstat目录… 6

4.       创建get_vmstat.ksh脚本… 6

5.       创建run_vmstat.ksh脚本… 8

6.       创建crontab作业，定时执行run_vmstat.ksh脚本… 9

7.       分析数据… 9

1)    异常报告… 9

2)    每小时趋势报告… 13

3)    周趋势报告… 14

4)    长期趋势报告… 14

四、       使用excel生成趋势图… 15

五、       参考资料… 15

 
一、      前言
作为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);