一、 前言
作为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 <
1<eof #="" #!="" #----------------------------------------="" #删除临时文件="" $2="" $check_stat`="" $hour="" $idle_cpu,="" $oracle_sid:|cut="" $page_in,="" $page_out,="" $page_scan,="" $runque,="" $sample_time,="" $swapin,="" $swapout,="" $system_cpu,="" $user_cpu,="" $vmstat_num="" $waitque,="" '$server_name',="" '{print="" (="" );="" +"%h"`="" -9="" -d':'`="" -ef|grep="" -f2="" -gt="" -l`="" -lt="" -v="" .="" 0="" 19="" 5\.="" 8="" [="" ]="" `ps="" bin="" check_stat="`ps" commit;="" done="" eof="" exit="" export="" get_vmstat|grep="" grep|awk="" grep|wc="" hour="`date" if="" insert="" into="" kill="" ksh="" msg$$="" opt="" oracle="" oracle_home="" oracle_sid="" oratab|grep="" path="" perfstat.stats\$vmstat="" rm="" run_vmstat.ksh="" script_path="" sysdate,="" then="" tmp="" values="" var="" vmstat_num="`expr" vmstat`="" ||="" }'`="" ~oracle="" 作更进一步地控制，在系统没有操作的时间内停止监控="" 其它时间如果get_vmstat不在运行，就运行它="" 创建run_vmstat.ksh脚本="" 晚上8点到第二天凌晨8点之间停止监控="" 该脚本放在crontab中，用来定时检查get_vmstat.ksh脚本有没有正常运行，如果在运行，那么不作任何动作，如果不在运行，那么就运行get_vmstat.ksh脚本。这个脚本的意义在于防止重新启动操作系统之后忘记运行get_vmstat.ksh脚本。="" 首先设定环境变量，根据实际环境修改.=""> /dev/null
2
3fi
4
5else
6
7if [ $vmstat_num -le 0 ]
8
9then nohup $SCRIPT_PATH/get_vmstat.ksh > /dev/null 2>&1 &
10
11fi
12
13fi
14
156\. 创建crontab作业，定时执行run_vmstat.ksh脚本
16该作业每半小时运行一次。
17
18$ crontab –l > oracle.cron
19
20$ echo ’00,30 * * * * /export/home/oracle/vmstat/run_vmstat.ksh >> /export/home/oracle/vmstat/run.lst 2>&1’ >> oracle.cron
21
22$ crontab oracle.cron
23
247\. 分析数据
25至此已经完成了定时运行vmstat和在数据库中存储vmstat结果的步骤。自然，仅仅是搜集了统计信息是远远不够的，下面我们要分析搜集来的信息，产生操作系统的性能报告。
26
27将报告分为以下几类，分别用脚本实现。
28
29异常报告：显示超过了门限值的时间段（vmstat_alert.ksh + vmstat_alert.sql）
30
31每小时趋势报告：显示一天内每小时的系统平均利用情况（rpt_vmstat_hr.sql）
32
33周趋势报告：显示每天的系统平均利用情况（rpt_vmstat_dy.sql）
34
35长期趋势报告：显示系统性能的一个长期趋势线（rpt_vmstat.sql））
36
371) 异常报告
38# vmstat_alert.sql
39
40REM ----------------------------------------
41
42REM 该SQL用于报告Oracle环境中每个服务器的异常情况。
43
44REM 根据get_vmstat.ksh脚本得到的信息，报告每个小时的平均值。
45
46REM DBA发现异常时间段，则可以深入检查每5分钟的详细数据。
47
48REM 在这个脚本中接受一个参数，表示需要报告的时间跨度。
49
50REM 对于runing queue的门限值应该设置为CPU数，
51
52REM 表示如果出现大量等待执行的任务就报警，这通常表示CPU负载过重。
53
54REM 对于page scan(sr)的门限值设置为1，
55
56REM 表示只要出现page daemon扫描页就报警，这通常表示内存不足。
57
58REM 对于CPU利用率，设置为70，表示超过70%以上的利用率才报警。
59
60REM 比如运行vmstat_alert 7
61
62REM 表示输出当前日期之前7天之内的执行队列大于4，
63
64REM sr大于1，CPU利用率超过70%的按照小时统计的报告。
65
66REM ----------------------------------------
67
68set lines 80;
69set pages 999;
70set feedback off;
71set verify off;
72
73column my_date heading 'date hour' format a20
74column c2 heading waitq format 9999
75column c3 heading pg_in format 9999
76column c4 heading pg_ot format 9999
77column c5 heading usr format 9999
78column c6 heading sys format 9999
79column c7 heading idl format 9999
80column c8 heading wt format 9999
81
82ttitle 'run queue > CPUs|May indicate an overloaded CPU|When runqueue exceeds the number of CPUs| on the server, tasks are waiting for service.';
83
84select
85server_name,
86to_char(start_date,'YY/MM/DD HH24') my_date,
87avg(running_queue) c2,
88avg(kbytes_page_in) c3,
89avg(kbytes_page_out) c4,
90avg(user_cpu) c5,
91avg(system_cpu) c6,
92avg(idle_cpu) c7
93from
94perfstat.stats$vmstat
95WHERE
96running_queue > 4
97and start_date > sysdate-&1
98group by
99server_name,
100to_char(start_date,'YY/MM/DD HH24')
101ORDER BY
102server_name,
103to_char(start_date,'YY/MM/DD HH24')
104;
105
106ttitle '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.';
107
108select
109server_name,
110to_char(start_date,'YY/MM/DD HH24') my_date,
111avg(running_queue) c2,
112avg(kbytes_page_in) c3,
113avg(kbytes_page_out) c4,
114avg(user_cpu) c5,
115avg(system_cpu) c6,
116avg(idle_cpu) c7
117from
118perfstat.stats$vmstat
119WHERE
120page_scan > 1
121and start_date > sysdate-&1
122group by
123server_name,
124to_char(start_date,'YY/MM/DD HH24')
125ORDER BY
126server_name,
127to_char(start_date,'YY/MM/DD HH24')
128;
129
130ttitle '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.';
131
132select
133server_name,
134to_char(start_date,'YY/MM/DD HH24') my_date,
135avg(running_queue) c2,
136avg(kbytes_page_in) c3,
137avg(kbytes_page_out) c4,
138avg(user_cpu) c5,
139avg(system_cpu) c6,
140avg(idle_cpu) c7
141from
142perfstat.stats$vmstat
143WHERE
144(user_cpu + system_cpu) > 70
145and start_date > sysdate-&1
146group by
147server_name,
148to_char(start_date,'YY/MM/DD HH24')
149ORDER BY
150server_name,
151to_char(start_date,'YY/MM/DD HH24')
152;
153
154# vmstat_alert.ksh
155
156#----------------------------------------
157
158# 可以将此shell加入cron中，每天7点运行
159
160#----------------------------------------
161
162#!/bin/ksh
163
164#----------------------------------------
165
166# 首先设定环境变量，根据实际环境修改. . . .
167
168# 接受一个参数输入，表示当前要报告的数据库SID
169
170#----------------------------------------
171
172ORACLE_SID=$1
173
174export ORACLE_SID
175
176ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
177
178export ORACLE_HOME
179
180PATH=$ORACLE_HOME/bin:$PATH
181
182export PATH
183
184SCRIPT_PATH=`echo ~oracle/vmstat`
185
186export SCRIPT_PATH
187
188sqlplus perfstat/perfstat<<!--
189
190spool /tmp/vmstat_$ORACLE_SID.lst
191
192@$SCRIPT_PATH/vmstat_alert 7 4
193
194spool off;
195
196exit;
197
198!
199
200#----------------------------------------
201
202# 检查vmstat_alert.sql的输出结果
203
204# 正常情况应该只包含下面2行
205
206# SQL--> @/export/home/oracle/vmstat/vmstat_alert 7
207
208# SQL> spool off;
209
210# 如果超过3行则表示有异常值，那么直接邮件给DBA
211
212#----------------------------------------
213
214check_stat=`cat /tmp/vmstat_$ORACLE_SID.lst|wc -l`;
215
216oracle_num=`expr $check_stat`
217
218if [ $oracle_num -gt 3 ]
219
220then
221
222cat /tmp/vmstat_$ORACLE_SID.lst|mailx -s "System vmstat alert" [email protected] [email protected]
223
224fi
225
2261． 创建crontab作业，每天7点定时执行vmstat_alert.ksh脚本
227
228$ crontab –l > oracle.cron
229
230$ echo ’00 7 * * * /export/home/oracle/vmstat/vmstat_alert.ksh kamusdb >> /export/home/oracle/vmstat/runalert.lst 2>&1’ >> oracle.cron
231
232$ crontab oracle.cron
233
2342) 每小时趋势报告
235# rpt_vmstat_hr.sql
236
237REM ----------------------------------------
238
239REM 该SQL用于报告Oracle环境中每个服务器一天内小时平均的CPU使用率
240
241REM 接受一个参数，用于指定需要报告的日期，格式为YYYYMMDD
242
243REM ----------------------------------------
244
245connect perfstat/perfstat;
246set pages 9999;
247
248set feedback off;
249set verify off;
250
251column server_name heading 'server' format a10
252column my_hour heading 'hour' format a10
253column c2 heading runq format 9999
254column c3 heading pg_in format 9999
255column c4 heading pg_ot format 9999
256column c5 heading usr format 9999
257column c6 heading sys format 9999
258column c7 heading idl format 9999
259column c8 heading wt format 9999
260
261select server_name,
262to_char(start_date, 'HH24') my_hour,
263avg(runing_queue) c2,
264avg(kbytes_page_in) c3,
265avg(kbytes_page_out) c4,
266avg(user_cpu + system_cpu) c5,
267avg(system_cpu) c6,
268avg(idle_cpu) c7
269from stats$vmstat
270where trunc(start_date) = to_date(&1,'yyyymmdd')
271group BY server_name,to_char(start_date, 'HH24')
272order by server_name,to_char(start_date, 'HH24');
273
2743) 周趋势报告
275# rpt_vmstat_dy.sql
276
277REM ----------------------------------------
278
279REM 该SQL用于报告本周内Oracle环境中每个服务器的日平均CPU使用率
280
281REM ----------------------------------------
282
283connect perfstat/perfstat;
284set pages 9999;
285
286set feedback off;
287set verify off;
288
289column server_name heading 'server' format a10
290column my_day heading 'day' format a20
291column c2 heading runq format 9999
292column c3 heading pg_in format 9999
293column c4 heading pg_ot format 9999
294column c5 heading usr format 9999
295column c6 heading sys format 9999
296column c7 heading idl format 9999
297column c8 heading wt format 9999
298
299select server_name,
300to_char(start_date, 'day') my_day,
301avg(runing_queue) c2,
302avg(kbytes_page_in) c3,
303avg(kbytes_page_out) c4,
304avg(user_cpu + system_cpu) c5,
305avg(idle_cpu) c7
306from stats$vmstat
307where trunc(start_date) >= trunc(next_day(sysdate, 'MONDAY')) - 7
308and trunc(start_date) < trunc(next_day(sysdate, 'MONDAY'))
309group BY server_name, to_char(start_date, 'day')
310order by server_name, to_char(start_date, 'day');
311
3124) 长期趋势报告
313# rpt_vmstat.sql
314
315REM ----------------------------------------
316
317REM 该SQL用于报告Oracle环境中每个服务器日平均的CPU使用率
318
319REM 报告范围为已搜集的所有数据
320
321REM ----------------------------------------
322
323connect perfstat/perfstat;
324set pages 9999;
325
326set feedback off;
327set verify off;
328
329column server_name heading 'server' format a10
330column my_date heading 'date' format a20
331column c2 heading runq format 9999
332column c3 heading pg_in format 9999
333column c4 heading pg_ot format 9999
334column c5 heading usr format 9999
335column c6 heading sys format 9999
336column c7 heading idl format 9999
337column c8 heading wt format 9999
338
339select server_name,
340trunc(start_date) my_date,
341avg(runing_queue) c2,
342avg(kbytes_page_in) c3,
343avg(kbytes_page_out) c4,
344avg(user_cpu + system_cpu) c5,
345avg(idle_cpu) c7
346from stats$vmstat
347group BY server_name, trunc(start_date)
348order by server_name, trunc(start_date);</eof>