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

作者简介：张乐奕，通常使用的网名为kamus，也曾用过seraphim，现在任职于北京某大型软件公司，Oracle数据库DBA，主要负责证券行业的核心交易系统数据库治理及维护工作。 热切关注Oracle技术和相关操作系统技术，出没于各大数据库技术论坛，目前是中国最大的Oracle技术论坛www.itpub.net的数据库治理版版主。我的邮件地址：kamus@itpub.net我的MSN: kamusis@hotmail.com期望与各位朋友讨论Oracle相关技术。本人所有文章，不经许可，不得转载。目录一、;;;;前言... 4二、;;;;思路... 4三、;;;;vmstat脚本及步骤... 41.;;;;安装statspack. 42.;;;;创建stats$vmstat表... 43.;;;;创建vmstat目录... 64.;;;;创建get_vmstat.ksh脚本... 65.;;;;创建run_vmstat.ksh脚本... 86.;;;;创建crontab作业，定时执行run_vmstat.ksh脚本... 97.;;;;分析数据... 91);异常报告... 92);每小时趋势报告... 133);周趋势报告... 144);长期趋势报告... 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.;安装statspacksqlplus “/ as sysdba”SQL> @?/rdbms/admin/spcreate.sql按照提示设定perfstat用户的密码之后，由于是10g，我选择了SYSAUX表空间作为perfstat用户的默认表空间，而没有另行创建表空间。2.;创建stats$vmstat表SQL> connect perfstat/perfstatSQL> @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 sysauxstorage (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 vmstat4.;创建get_vmstat.ksh脚本改脚本用于定时执行vmstat命令并且将结果存入数据库。# get_vmstat.ksh#!/bin/ksh#----------------------------------------# 首先设定环境变量，根据实际环境修改. . . .#----------------------------------------ORACLE_SID=kamusdbeXPort ORACLE_SIDORACLE_HOME=`cat /var/opt/oracle/oratabgrep ^$ORACLE_SID:cut -f2 -d':'`export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATHexport PATHSERVER_NAME=`uname -aawk '{print $2}'`typeset -u SERVER_NAMEexport SERVER_NAME#----------------------------------------# 每5分钟运行一次vmstat(300秒)，可以根据实际情况修改 . . . .#----------------------------------------SAMPLE_TIME=300#----------------------------------------# 脚本一旦运行将不会停止，除非关闭操作系统 . . . .# -S参数表示监控swap空间的情况，报告si，so列# msg$$中的$$表示一个任意2位数字，系统自动生成#----------------------------------------while truedo 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; EXITEOF donedone#----------------------------------------#删除临时文件#----------------------------------------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_PATHORACLE_SID=kamusdbexport ORACLE_SIDORACLE_HOME=`cat /var/opt/oracle/oratabgrep $ORACLE_SID:cut -f2 -d':'`export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATHexport PATH#----------------------------------------# 作更进一步地控制，在系统没有操作的时间内停止监控# 晚上8点到第二天凌晨8点之间停止监控# 其它时间假如get_vmstat不在运行，就运行它#----------------------------------------HOUR=`date +'%H'`check_stat=`ps -efgrep get_vmstatgrep -v grepwc -l`vmstat_num=`expr $check_stat`if [ $HOUR -gt 19 ] [ $HOUR -lt 8 ]then if [ $vmstat_num -gt 0 ] ;;;then kill -9 `ps -efgrep get_vmstatgrep -v grepawk '{print $2 }'` > /dev/null fielseif [ $vmstat_num -le 0 ] then nohup $SCRIPT_PATH/get_vmstat.ksh > /dev/null 2>&1 &fifi6.;创建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.cron7.;分析数据至此已经完成了定时运行vmstat和在数据库中存储vmstat结果的步骤。自然，仅仅是搜集了统计信息是远远不够的，下面我们要分析搜集来的信息，产生操作系统的性能报告。将报告分为以下几类，分别用脚本实现。异常报告：显示超过了门限值的时间段（vmstat_alert.ksh + vmstat_alert.sql）每小时趋势报告：显示一天内每小时的系统平均利用情况（rpt_vmstat_hr.sql）周趋势报告：显示天天的系统平均利用情况（rpt_vmstat_dy.sql）长期趋势报告：显示系统性能的一个长期趋势线（rpt_vmstat.sql））1);;;;;异常报告# vmstat_alert.sqlREM ----------------------------------------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 7REM 表示输出当前日期之前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 a20column c2;;;heading waitqformat 9999column c3;;;heading pg_in; format 9999column c4;;;heading pg_ot; format 9999column c5;;;heading usr;format 9999column c6;;;heading sys;format 9999column c7;;;heading idl;format 9999column c8;;;heading wt;;format 9999ttitle 'run queue > CPUsMay indicate an overloaded CPUWhen runqueue exceeds the number of CPUs on the server, tasks are waiting for service.';selectserver_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)c7fromperfstat.stats$vmstatWHERErunning_queue > 4and start_date > sysdate-&1group byserver_name,to_char(start_date,'YY/MM/DD;HH24')ORDER BYserver_name,to_char(start_date,'YY/MM/DD;HH24')ttitle 'page_scan > 1May indicate overloaded memoryWhenever Unix performs a page-in, the RAM memory on the server has been exhausted and swap pages are being used.';selectserver_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)c7fromperfstat.stats$vmstatWHEREpage_scan > 1and start_date > sysdate-&1group byserver_name,to_char(start_date,'YY/MM/DD;HH24')ORDER BYserver_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.';selectserver_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)c7fromperfstat.stats$vmstatWHERE(user_cpu + system_cpu) > 70and start_date > sysdate-&1group byserver_name,to_char(start_date,'YY/MM/DD;HH24')ORDER BYserver_name,to_char(start_date,'YY/MM/DD;HH24')# vmstat_alert.ksh#----------------------------------------# 可以将此shell加入cron中，天天7点运行#----------------------------------------#! /bin/ksh#----------------------------------------# 首先设定环境变量，根据实际环境修改. . . .# 接受一个参数输入，表示当前要报告的数据库SID#----------------------------------------ORACLE_SID=$1export ORACLE_SIDORACLE_HOME=`cat /var/opt/oracle/oratabgrep $ORACLE_SID:cut -f2 -d':'`export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATHexport PATHSCRIPT_PATH=`echo ~oracle/vmstat`export SCRIPT_PATHsqlplus perfstat/perfstat<<!spool /tmp/vmstat_$ORACLE_SID.lst@$SCRIPT_PATH/vmstat_alert 7 4spool 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.lstwc -l`;oracle_num=`expr $check_stat`if [ $oracle_num -gt 3 ]then cat /tmp/vmstat_$ORACLE_SID.lstmailx -s 'System vmstat alert' kamus@itpub.net some_other_dba@mail.address.netfi1． 创建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.cron2);;;;;每小时趋势报告# rpt_vmstat_hr.sqlREM ----------------------------------------REM 该SQL用于报告Oracle环境中每个服务器一天内小时平均的CPU使用率REM 接受一个参数，用于指定需要报告的日期，格式为YYYYMMDDREM ----------------------------------------connect perfstat/perfstat;set pages 9999;set feedback off;set verify off;column server_name heading 'server' format a10column my_hour heading 'hour' format a10column c2;;;heading runqformat 9999column c3;;;heading pg_in; format 9999column c4 ;;;;;heading pg_ot; format 9999column c5;;;heading usr;format 9999column c6;;;heading sys;format 9999column c7;;;heading idl;format 9999column c8;;;heading wt;;format 9999select 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.sqlREM ----------------------------------------REM 该SQL用于报告本周内Oracle环境中每个服务器的日平均CPU使用率REM ----------------------------------------connect perfstat/perfstat;set pages 9999;set feedback off;set verify off;column server_name heading 'server' format a10column my_day heading 'day' format a20column c2;;;heading runqformat 9999column c3;;;heading pg_in; format 9999column c4;;;heading pg_ot; format 9999column c5;;;heading usr;format 9999column c6;;;heading sys;format 9999column c7;;;heading idl;format 9999column c8;;;heading wt;;format 9999select 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$vmstatwhere 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.sqlREM ----------------------------------------REM 该SQL用于报告Oracle环境中每个服务器日平均的CPU使用率REM 报告范围为已搜集的所有数据REM ----------------------------------------connect perfstat/perfstat; set pages 9999;set feedback off;set verify off;column server_name heading 'server' format a10column my_date heading 'date' format a20column c2;;;heading runqformat 9999column c3;;;heading pg_in; format 9999column c4;;;heading pg_ot; format 9999column c5;;;heading usr;format 9999column c6;;;heading sys;format 9999column c7;;;heading idl;format 9999column c8;;;heading wt;;format 9999select 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$vmstatgroup BY server_name, trunc(start_date)order by server_name, trunc(start_date);