#----------------------------------------
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/nullfielseif [ $vmstat_num -le 0 ]then nohup $SCRipT_PATH/get_vmstat.ksh > /dev/null 2>&1 &fifi
6. 創(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.cron
7. 分析數(shù)據(jù)
至此已經(jīng)完成了定時運行vmstat和在數(shù)據(jù)庫中存儲vmstat結(jié)果的步驟。自然，僅僅是搜集了統(tǒng)計信息是遠遠不夠的，下面我們要分析搜集來的信息，產(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)境中每個服務器的異常情況。REM 根據(jù)get_vmstat.ksh腳本得到的信息，報告每個小時的平均值。REM DBA發(fā)現(xiàn)異常時間段，則可以深入檢查每5分鐘的具體數(shù)據(jù)。REM 在這個腳本中接受一個參數(shù)，表示需要報告的時間跨度。REM 對于runing queue的門限值應該設置為CPU數(shù)，REM 表示假如出現(xiàn)大量等待執(zhí)行的任務就報警，這通常表示CPU負載過重。REM 對于page scan(sr)的門限值設置為1，REM 表示只要出現(xiàn)page daemon掃描頁就報警，這通常表示內(nèi)存不足。REM 對于CPU利用率，設置為70，表示超過70%以上的利用率才報警。REM 比如運行vmstat_alert 7REM 表示輸出當前日期之前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 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 9999ttitle 'run queue > CPUsMay indicate an overloaded CPUWhen 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 > 1May indicate overloaded memoryWhenever 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#----------------------------------------# 首先設定環(huán)境變量，根據(jù)實際環(huán)境修改. . . .# 接受一個參數(shù)輸入，表示當前要報告的數(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_PATH
sqlplus perfstat/perfstat<<!spool /tmp/vmstat_$ORACLE_SID.lst@$SCRIPT_PATH/vmstat_alert 7 4spool off;exit;!
#----------------------------------------# 檢查vmstat_alert.sql的輸出結(jié)果# 正常情況應該只包含下面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 ]thencat /tmp/vmstat_$ORACLE_SID.lstmailx -s "System vmstat alert" kamus@itpub.net some_other_dba@mail.address.netfi
1． 創(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.cron
2) 每小時趨勢報告
# rpt_vmstat_hr.sqlREM ----------------------------------------REM 該SQL用于報告Oracle環(huán)境中每個服務器一天內(nèi)小時平均的CPU使用率REM 接受一個參數(shù)，用于指定需要報告的日期，格式為YYYYMMDDREM ----------------------------------------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 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)境中每個服務器的日平均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 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$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.sqlREM ----------------------------------------REM 該SQL用于報告Oracle環(huán)境中每個服務器日平均的CPU使用率REM 報告范圍為已搜集的所有數(shù)據(jù)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);
