利用Oracle数据库存储和分析Unix操作系统的性能

类别:数据库 点击:0 评论:0 推荐:

作者简介:

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

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

 

我的邮件地址:[email protected]

我的MSN: [email protected]

 

期望与各位朋友讨论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" [email protected] [email protected]

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);

本文地址:http://com.8s8s.com/it/it20068.htm