作者簡介：張樂奕，通常使用的網(wǎng)名為kamus，也曾用過seraphim，現(xiàn)在任職于北京某大型軟件公司，Oracle數(shù)據(jù)庫DBA，主要負(fù)責(zé)證券行業(yè)的核心交易系統(tǒng)數(shù)據(jù)庫治理及維護工作。 熱切關(guān)注Oracle技術(shù)和相關(guān)操作系統(tǒng)技術(shù)，出沒于各大數(shù)據(jù)庫技術(shù)論壇，目前是中國最大的Oracle技術(shù)論壇www.itpub.net的數(shù)據(jù)庫治理版版主。我的郵件地址：kamus@itpub.net我的MSN: kamusis@hotmail.com期望與各位朋友討論Oracle相關(guān)技術(shù)。本人所有文章，不經(jīng)許可，不得轉(zhuǎn)載。目錄一、;;;;前言... 4二、;;;;思路... 4三、;;;;vmstat腳本及步驟... 41.;;;;安裝statspack. 42.;;;;創(chuàng)建stats$vmstat表... 43.;;;;創(chuàng)建vmstat目錄... 64.;;;;創(chuàng)建get_vmstat.ksh腳本... 65.;;;;創(chuàng)建run_vmstat.ksh腳本... 86.;;;;創(chuàng)建crontab作業(yè)，定時執(zhí)行run_vmstat.ksh腳本... 97.;;;;分析數(shù)據(jù)... 91);異常報告... 92);每小時趨勢報告... 133);周趨勢報告... 144);長期趨勢報告... 14四、;;;;使用Excel生成趨勢圖... 15五、;;;;參考資料... 15一、;;;前言作為UNIX系統(tǒng)中的Oracle數(shù)據(jù)庫治理員，時刻監(jiān)控操作系統(tǒng)的性能無疑是非常重要的，幸運的是UNIX系統(tǒng)提供了大量的監(jiān)控命令，比如vmstat, iostat, sar, top等等，這些監(jiān)控命令均以字符界面輸出結(jié)果，再加上UNIX系統(tǒng)中shell的強大分析功能，這樣我們只需要編寫一些腳本就可以實現(xiàn)自動的后臺監(jiān)控，當(dāng)有問題的時候再自動發(fā)送郵件給DBA。其實相類似的一些監(jiān)控腳本可能已經(jīng)隨處可見了，但是本文提到的一個新的思路，就是利用Oracle數(shù)據(jù)庫的statspack空間來存儲監(jiān)控的結(jié)果，再利用數(shù)據(jù)庫天生的檢索優(yōu)勢，這樣比以往靠shell分析甚或是人工分析生成的監(jiān)控結(jié)果文件要更加輕松，智能，同時也能實現(xiàn)更持久和更廣泛的監(jiān)控。本文大部分素材來源自Donald K. Burleson的Oracle9i UNIX Administration Handbook，但是對于腳本中的一些錯誤和不合理的地方作了修改，并且添加了一些功能，本文的測試環(huán)境全部基于Sun Solaris 8 Sparc 64bit + Oracle9.2.0.5。本文第一版主要以監(jiān)控內(nèi)存消耗和CPU等待的vmstat命令為例，將陸續(xù)添加iostat等其它命令的監(jiān)控腳本。 二、;;;思路首先安裝statspack，然后手工創(chuàng)建一個用于存儲vmstat命令輸出結(jié)果的表，再作一個shell定時執(zhí)行vmstat，將結(jié)果全部插入數(shù)據(jù)庫表中，最后就是通過SQL定時分析表中的數(shù)據(jù)，超過預(yù)先指定的門限值就告警。同時根據(jù)表中的數(shù)據(jù)，我們還能作出系統(tǒng)性能的趨勢報告。三、;;;vmstat腳本及步驟1.;安裝statspacksqlplus “/ as sysdba”SQL> @?/rdbms/admin/spcreate.sql按照提示設(shè)定perfstat用戶的密碼之后，由于是10g，我選擇了SYSAUX表空間作為perfstat用戶的默認(rèn)表空間，而沒有另行創(chuàng)建表空間。2.;創(chuàng)建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 '監(jiān)控時間';comment on column stats$vmstat.duration is '監(jiān)控時長';comment on column stats$vmstat.server_name is '服務(wù)器名稱';comment on column stats$vmstat.running_queue is '執(zhí)行隊列';comment on column stats$vmstat.waiting_queue is '等待隊列';comment on column stats$vmstat.swap_in is '每秒平均交換進LWPS數(shù)';comment on column stats$vmstat.swap_out is '交換出進程數(shù)';comment on column stats$vmstat.kbytes_page_in is '換頁進字節(jié)(K)';comment on column stats$vmstat.kbytes_page_out is '換頁出字節(jié)(K)';comment on column stats$vmstat.page_scan is '換頁守護進程掃描頁數(shù)';comment on column stats$vmstat.user_cpu is '用戶操作占用的CPU';comment on column stats$vmstat.system_cpu is '系統(tǒng)操作占用的CPU';comment on column stats$vmstat.idle_cpu is 'CPU空閑率';comment on column stats$vmstat.wait_cpu is 'CPU等待率(AIX獨有)';3.;創(chuàng)建vmstat目錄在oracle用戶主目錄下創(chuàng)建用于存放所有相關(guān)腳本的vmstat目錄。 $ cd$ mkdir vmstat4.;創(chuàng)建get_vmstat.ksh腳本改腳本用于定時執(zhí)行vmstat命令并且將結(jié)果存入數(shù)據(jù)庫。# get_vmstat.ksh#!/bin/ksh#----------------------------------------# 首先設(shè)定環(huán)境變量，根據(jù)實際環(huán)境修改. . . .#----------------------------------------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秒)，可以根據(jù)實際情況修改 . . . .#----------------------------------------SAMPLE_TIME=300#----------------------------------------# 腳本一旦運行將不會停止，除非關(guān)閉操作系統(tǒng) . . . .# -S參數(shù)表示監(jiān)控swap空間的情況，報告si，so列# msg$$中的$$表示一個任意2位數(shù)字，系統(tǒng)自動生成#----------------------------------------while truedo vmstat –S ${SAMPLE_TIME} 2 > /tmp/msg$$#----------------------------------------# Solaris系統(tǒng)的vmstat沒有wait CPU統(tǒng)計，所以我們在那一列中存入0# $1, $2, $6, $7等數(shù)字分別表示vmstat輸出中的第幾列，# 每個Unix系統(tǒng)中的vmstat輸出可能都不一樣，# 所以修改這些列號，就可以應(yīng)對不同的操作系統(tǒng)。#----------------------------------------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.;創(chuàng)建run_vmstat.ksh腳本該腳本放在crontab中，用來定時檢查get_vmstat.ksh腳本有沒有正常運行，假如在運行，那么不作任何動作，假如不在運行，那么就運行g(shù)et_vmstat.ksh腳本。 這個腳本的意義在于防止重新啟動操作系統(tǒng)之后忘記運行g(shù)et_vmstat.ksh腳本。# run_vmstat.ksh#!/bin/ksh#----------------------------------------# 首先設(shè)定環(huán)境變量，根據(jù)實際環(huán)境修改. . . .#----------------------------------------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#----------------------------------------# 作更進一步地控制，在系統(tǒng)沒有操作的時間內(nèi)停止監(jiān)控# 晚上8點到第二天凌晨8點之間停止監(jiān)控# 其它時間假如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.;創(chuàng)建crontab作業(yè)，定時執(zhí)行run_vmstat.ksh腳本 該作業(yè)每半小時運行一次。$ 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.;分析數(shù)據(jù)至此已經(jīng)完成了定時運行vmstat和在數(shù)據(jù)庫中存儲vmstat結(jié)果的步驟。自然，僅僅是搜集了統(tǒng)計信息是遠(yuǎn)遠(yuǎn)不夠的，下面我們要分析搜集來的信息，產(chǎn)生操作系統(tǒng)的性能報告。將報告分為以下幾類，分別用腳本實現(xiàn)。異常報告：顯示超過了門限值的時間段（vmstat_alert.ksh + vmstat_alert.sql）每小時趨勢報告：顯示一天內(nèi)每小時的系統(tǒng)平均利用情況（rpt_vmstat_hr.sql）周趨勢報告：顯示天天的系統(tǒng)平均利用情況（rpt_vmstat_dy.sql）長期趨勢報告：顯示系統(tǒng)性能的一個長期趨勢線（rpt_vmstat.sql））1);;;;;異常報告# vmstat_alert.sqlREM ----------------------------------------REM 該SQL用于報告Oracle環(huán)境中每個服務(wù)器的異常情況。REM 根據(jù)get_vmstat.ksh腳本得到的信息，報告每個小時的平均值。REM DBA發(fā)現(xiàn)異常時間段，則可以深入檢查每5分鐘的具體數(shù)據(jù)。REM 在這個腳本中接受一個參數(shù)，表示需要報告的時間跨度。REM 對于runing queue的門限值應(yīng)該設(shè)置為CPU數(shù)，REM 表示假如出現(xiàn)大量等待執(zhí)行的任務(wù)就報警，這通常表示CPU負(fù)載過重。REM 對于page scan(sr)的門限值設(shè)置為1，REM 表示只要出現(xiàn)page daemon掃描頁就報警，這通常表示內(nèi)存不足。REM 對于CPU利用率，設(shè)置為70，表示超過70%以上的利用率才報警。REM 比如運行vmstat_alert 7REM 表示輸出當(dāng)前日期之前7天之內(nèi)的執(zhí)行隊列大于4，REM sr大于1，CPU利用率超過70%的按照小時統(tǒng)計的報告。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#----------------------------------------# 首先設(shè)定環(huán)境變量，根據(jù)實際環(huán)境修改. . . .# 接受一個參數(shù)輸入，表示當(dāng)前要報告的數(shù)據(jù)庫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的輸出結(jié)果# 正常情況應(yīng)該只包含下面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． 創(chuàng)建crontab作業(yè)，天天7點定時執(zhí)行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環(huán)境中每個服務(wù)器一天內(nèi)小時平均的CPU使用率REM 接受一個參數(shù)，用于指定需要報告的日期，格式為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用于報告本周內(nèi)Oracle環(huán)境中每個服務(wù)器的日平均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環(huán)境中每個服務(wù)器日平均的CPU使用率REM 報告范圍為已搜集的所有數(shù)據(jù)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);