ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 监控磁盘IO的脚本

oracle 监控磁盘IO的脚本

原创 Linux操作系统 作者:david3389 时间:2009-03-17 17:57:16 0 删除 编辑

之前一直怀疑在数据库忙时会出现磁盘瓶颈,但是一直不能有效收集磁盘IO的历史数据以方便制作报表,所以今天粗略了制作了统计磁盘IO的脚本,将数据写入到scott下的表内,定期查询出数据在EXEL中制作报表(生成EXEL的脚本以后奉上)。

OS:solaris 8
DB:oracle 9206

创建表:
create table diskio
(
time date,
rs varchar2(20),
ws varchar2(20),
krs varchar2(20),
kws varchar2(20),
wait varchar2(20),
actv varchar2(20),
svct varchar2(20),
wp varchar2(20),
bp varchar2(20),
mountpoint varchar2(50)
 )
  
comment on column diskio.time   is  '统计时间戳';
comment on column diskio.rs   is  '每秒读取次数';
comment on column diskio.ws   is  '每秒写入次数';
comment on column diskio.krs  is  '每秒读取的千字节数';
comment on column diskio.kws  is  '每秒写入的千字节数';
comment on column diskio.wait is  '处于活动服务状态的平均事务数 ';
comment on column diskio.actv is  '等待服务的平均事务数(队列长度)';
comment on column diskio.svct is  '平均服务时间,以毫秒为单位';
comment on column diskio.wp   is  '队列不为空的时间百分比 ';
comment on column diskio.bp   is  '磁盘繁忙的时间百分比';
comment on column diskio.mountpoint   is  '磁盘挂接点';

IO.SH:
#!/bin/sh
ORACLE_HOME=/pglobal/cmxp/oracle/app/oracle/product/9.2.0
export ORACLE_HOME
ORACLE_SID=csms3
export ORACLE_SID
LOG=iostat.log
TIME=`date +%Y%m%d%H%M%S`
iostat -xtc  1 5  |grep "2/md[1-5]\>" > $LOG
####################################################
#COLLECT EVERY VOLUME R/S
####################################################
MD1RS=`grep "2/md1\>" $LOG | tail -1 |awk '{print $2}'`
MD2RS=`grep "2/md2\>" $LOG | tail -1 |awk '{print $2}'`
MD3RS=`grep "2/md3\>" $LOG | tail -1 |awk '{print $2}'`
MD4RS=`grep "2/md4\>" $LOG | tail -1 |awk '{print $2}'`
MD5RS=`grep "2/md5\>" $LOG | tail -1 |awk '{print $2}'`
####################################################
#COLLECT EVERY VOLUME W/S
####################################################
MD1WS=`grep "2/md1\>" $LOG | tail -1 |awk '{print $3}'`
MD2WS=`grep "2/md2\>" $LOG | tail -1 |awk '{print $3}'`
MD3WS=`grep "2/md3\>" $LOG | tail -1 |awk '{print $3}'`
MD4WS=`grep "2/md4\>" $LOG | tail -1 |awk '{print $3}'`
MD5WS=`grep "2/md5\>" $LOG | tail -1 |awk '{print $3}'`
####################################################
#COLLECT EVERY VOLUME KR/S
####################################################
MD1KRS=`grep "2/md1\>" $LOG | tail -1 |awk '{print $4}'`
MD2KRS=`grep "2/md2\>" $LOG | tail -1 |awk '{print $4}'`
MD3KRS=`grep "2/md3\>" $LOG | tail -1 |awk '{print $4}'`
MD4KRS=`grep "2/md4\>" $LOG | tail -1 |awk '{print $4}'`
MD5KRS=`grep "2/md5\>" $LOG | tail -1 |awk '{print $4}'`
####################################################
#COLLECT EVERY VOLUME KW/S
####################################################
MD1KWS=`grep "2/md1\>" $LOG | tail -1 |awk '{print $5}'`
MD2KWS=`grep "2/md2\>" $LOG | tail -1 |awk '{print $5}'`
MD3KWS=`grep "2/md3\>" $LOG | tail -1 |awk '{print $5}'`
MD4KWS=`grep "2/md4\>" $LOG | tail -1 |awk '{print $5}'`
MD5KWS=`grep "2/md5\>" $LOG | tail -1 |awk '{print $5}'`
####################################################
#COLLECT EVERY VOLUME WAIT
####################################################
MD1WAIT=`grep "2/md1\>" $LOG | tail -1 |awk '{print $6}'`
MD2WAIT=`grep "2/md2\>" $LOG | tail -1 |awk '{print $6}'`
MD3WAIT=`grep "2/md3\>" $LOG | tail -1 |awk '{print $6}'`
MD4WAIT=`grep "2/md4\>" $LOG | tail -1 |awk '{print $6}'`
MD5WAIT=`grep "2/md5\>" $LOG | tail -1 |awk '{print $6}'`
####################################################
#COLLECT EVERY VOLUME ACTV
####################################################
MD1ACTV=`grep "2/md1\>" $LOG | tail -1 |awk '{print $7}'`
MD2ACTV=`grep "2/md2\>" $LOG | tail -1 |awk '{print $7}'`
MD3ACTV=`grep "2/md3\>" $LOG | tail -1 |awk '{print $7}'`
MD4ACTV=`grep "2/md4\>" $LOG | tail -1 |awk '{print $7}'`
MD5ACTV=`grep "2/md5\>" $LOG | tail -1 |awk '{print $7}'`
####################################################
#COLLECT EVERY VOLUME SVCT
####################################################
MD1SVCT=`grep "2/md1\>" $LOG | tail -1 |awk '{print $8}'`
MD2SVCT=`grep "2/md2\>" $LOG | tail -1 |awk '{print $8}'`
MD3SVCT=`grep "2/md3\>" $LOG | tail -1 |awk '{print $8}'`
MD4SVCT=`grep "2/md4\>" $LOG | tail -1 |awk '{print $8}'`
MD5SVCT=`grep "2/md5\>" $LOG | tail -1 |awk '{print $8}'`
####################################################
#COLLECT EVERY VOLUME WP
####################################################
MD1WP=`grep "2/md1\>" $LOG | tail -1 |awk '{print $9}'`
MD2WP=`grep "2/md2\>" $LOG | tail -1 |awk '{print $9}'`
MD3WP=`grep "2/md3\>" $LOG | tail -1 |awk '{print $9}'`
MD4WP=`grep "2/md4\>" $LOG | tail -1 |awk '{print $9}'`
MD5WP=`grep "2/md5\>" $LOG | tail -1 |awk '{print $9}'`
####################################################
#COLLECT EVERY VOLUME BP
####################################################
MD1BP=`grep "2/md1\>" $LOG | tail -1 |awk '{print $10}'`
MD2BP=`grep "2/md2\>" $LOG | tail -1 |awk '{print $10}'`
MD3BP=`grep "2/md3\>" $LOG | tail -1 |awk '{print $10}'`
MD4BP=`grep "2/md4\>" $LOG | tail -1 |awk '{print $10}'`
MD5BP=`grep "2/md5\>" $LOG | tail -1 |awk '{print $10}'`

echo \|$MD1RS\|$MD1WS\|$MD1KRS\|$MD1KWS\|$MD1WAIT\|$MD1ACTV\|$MD1SVCT\|$MD1WP\|$MD1BP\|
echo \|$MD2RS\|$MD2WS\|$MD2KRS\|$MD2KWS\|$MD2WAIT\|$MD2ACTV\|$MD2SVCT\|$MD2WP\|$MD2BP\|
echo \|$MD3RS\|$MD3WS\|$MD3KRS\|$MD3KWS\|$MD3WAIT\|$MD3ACTV\|$MD3SVCT\|$MD3WP\|$MD3BP\|
echo \|$MD4RS\|$MD4WS\|$MD4KRS\|$MD4KWS\|$MD4WAIT\|$MD4ACTV\|$MD4SVCT\|$MD4WP\|$MD4BP\|
echo \|$MD5RS\|$MD5WS\|$MD5KRS\|$MD5KWS\|$MD5WAIT\|$MD5ACTV\|$MD5SVCT\|$MD5WP\|$MD5BP\|

$ORACLE_HOME/bin/sqlplus 'scott/tiger' <insert into diskio(time,rs,ws,krs,kws,wait,actv,svct,wp,bp,mountpoint) values(to_date('$TIME','yyyymmddhh24miss'),$MD1RS,$MD1WS,$MD1KRS,$MD1KWS,$MD1WAIT,$MD1ACTV,$MD1SVCT,$MD1WP,$MD1BP,'/global/cmbpvol01');
insert into diskio(time,rs,ws,krs,kws,wait,actv,svct,wp,bp,mountpoint) values(to_date('$TIME','yyyymmddhh24miss'),$MD2RS,$MD2WS,$MD2KRS,$MD2KWS,$MD2WAIT,$MD2ACTV,$MD2SVCT,$MD2WP,$MD2BP,'/global/cmbpvol02');
insert into diskio(time,rs,ws,krs,kws,wait,actv,svct,wp,bp,mountpoint) values(to_date('$TIME','yyyymmddhh24miss'),$MD3RS,$MD3WS,$MD3KRS,$MD3KWS,$MD3WAIT,$MD3ACTV,$MD3SVCT,$MD3WP,$MD3BP,'/global/cmbpvol03');
insert into diskio(time,rs,ws,krs,kws,wait,actv,svct,wp,bp,mountpoint) values(to_date('$TIME','yyyymmddhh24miss'),$MD4RS,$MD4WS,$MD4KRS,$MD4KWS,$MD4WAIT,$MD4ACTV,$MD4SVCT,$MD4WP,$MD4BP,'/global/cmbpvol04');
insert into diskio(time,rs,ws,krs,kws,wait,actv,svct,wp,bp,mountpoint) values(to_date('$TIME','yyyymmddhh24miss'),$MD5RS,$MD5WS,$MD5KRS,$MD5KWS,$MD5WAIT,$MD5ACTV,$MD5SVCT,$MD5WP,$MD5BP,'/global/cmbp');
commit;
EOF

 

crontab(oracle用户):
#collect diskio
0,5,10,15,20,25,30,35,40,45,50,55  * * * * /pglobal/cmxp/io.sh
 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16628454/viewspace-571248/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-17

  • 博文量
    19
  • 访问量
    120155