Oracle 表对象HKW监控shell

背景:

最近研究了下表HKW的性能影响,需要监某些性能问题的表,因此创建了该脚本

#/bin/bash
#echo "********Checking the Location's Existence!"
#if [ -d /home/oracle/script/dbmon' ];
#   then echo "********Dirctory is existed!"
#else
#   mkdir -p /home/oracle/script/dbmon
#fi
#
#create table HWK
#(
#  TIME     DATE default sysdate,
#  OWNER    VARCHAR2(20),
#  T_NAME   VARCHAR2(20),
#  NUM_ROWS NUMBER,
#  HWK      NUMBER
#)
#tablespace DBMON
#
#grant all on hwk to dbmon;
LOC='/home/oracle/script/dbmon'
sqlplus / as sysdba<set head off;
set echo off;
spool '$LOC/t_cnt.sql';
select 'select count(*) as t_cnt from '||owner||'.'||table_name||;' from all_tables where wner='APP' or wner='CARD_SECD';
spool off;
EOF
more $LOC/t_cnt.sql |sed '/SQL/d' |sed '/^$/d'|sed '$d'>$LOC/t_cnt.sql
if [ $? -eq 0 ];
   then
     echo "********Create t_cnt.sql Done!"
else echo "********Create t_cnt.sql Failed!"
fi

sqlplus / as sysdba<set echo off;
set head off;
set pagesize 10000;
spool '$LOC/t_cnt';
@$LOC/t_cnt.sql;
spool off;
EOF

rm -rf $LOC/t_cnt.sql
echo "********Now create t_cnt Data"
more t_cnt.lst |sed '/SQL/d'|sed '/T_CNT/d'|sed '/--/d'|sed '/^$/d'>$LOC/t_cnt.dat
if [ $? -ne 0 ];
 then echo "********Create t_cnt Data failed!"
 else echo "********Create t_cnt Data Done!"
fi

if [ -f $LOC/t_cnt.dat ];
  then echo "*********Create t_cnt data succeed!"
else
   echo "********Create t_cnt data failed,Please try it again!"
   echo "............................"
   echo "********Now Recreate t_cnt data!"
   more t_cnt.lst |sed '/SQL/d'|sed '/T_CNT/d'|sed '/--/d'|sed '/^$/d'>$LOC/t_cnt.dat
fi
rm -rf $LOC/t_cnt.lst
#######################################Create tab Data###########################################
echo "********Now create tab Data!"

sqlplus / as sysdba<set head off;
set echo off;
set pagesize 10000;
spool '$LOC/t_tab.dat';
select at.owner,at.table_name,at.num_rows from all_tables at where wner='APP' or wner='CARD_SECD';
spool off;
EOF
more $LOC/t_tab.dat |sed '/SQL/d' |sed '/^$/d'|sed '$d'>$LOC/t_tab.dat
if [ $? -eq 0 ];
   then echo "********Create t_tab.dat Done!"
else
   echo "********Create t_tab.dat Failed!,Please Check it!"
fi

rm -rf $LOC/t_tab.lst

if [ -f '$LOC/t_tab.dat' ];
   then echo "*********Create t_tab data succeed!"
else
   echo "********Create t_tab data failed,Please try it again!"
   echo "********Now Recreate t_tab data!"
   more t_tab.dat |sed '/SQL/d'|sed '/T_CNT/d'|sed '/--/d'|sed '/^$/d'>$LOC/t_tab.dat
fi

 

echo "********Source data created,Now to Create final Data"
paste $LOC/t_tab.dat $LOC/t_cnt.dat>$LOC/dat.dat
if [ $? -eq 0 ];
   then echo "********Final Data ready!"
   rm -rf $LOC/t_cnt.dat
   rm -rf $LOc/t_tab.dat
else echo "********Final Data failed,Please retry it!"
fi
  
echo "*********Now Create controlfile!"

if [ -f '$LOC/t_dat.ctl' ];
   then echo "********Ctl is Ready!"
else
   echo "********Control file  is not Ready,Now to Create it!"
   touch $LOC/t_dat.ctl
fi

echo "********Loading ccontrol file!"
cat >$LOC/t_dat.ctl <OPTIONS ( DIRECT=TRUE)
LOAD DATA
INFILE '$LOC/dat.dat'

INTO TABLE sys.hwk
APPEND
FIELDS TERMINATED BY WHITESPACE
(OWNER,T_NAME,NUM_ROWS,HWK)

EOF

echo "********Now loading Data into sys.hwk!Please Wait!"

if [ -f '$LOC/load_dat.log' ];
   then rm -rf $LOC/load_dat.log
fi
sqlldr dbmon/dbmon control=$LOC/t_dat.ctl log=$LOC/load_dat.log

if [ $? -eq 0 ];
  then echo "********Load data into sys.hwk Done!"
  rm -rf $LOC/dat.dat
else echo "********Load data into sys.hwk Failed,Please Try it again!"
fi

echo "********Load Data Done,Now to clear temp files!....... "
rm -rf $LOC/t_dat.ctl
rm -rf %LOC/t_dat.dat

echo "********Clear temp files OK,Updating Time for hwk!"
sqlplus / as sysdba<update hwk set time=(select sysdate from dual);
commit;
EOF

echo "******Update for systime Done!"

#conn dbmon/dbmon
#create or replace view hwk as
#select to_char(h.time,'yyyy-mm-dd hh24:mi:ss') time,h.owner,h.t_name,h.num_rows,h.hwk,h.num_rows/h.hwk as hit_ratio from sys.hwk h
#where h.num_rows<>h.hwk;

结论:

其结果可以监控那些表对象有问题的的表,其实在Oracle的监控中平时也应该对Index进行监控,集合index internal可以进行下一个探索

优秀是一种习惯,习惯也是一种优秀

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

转载于:http://blog.itpub.net/15072844/viewspace-620919/