背景：公司业务侧DB服务器经常卡顿，编写简单的脚本去定时查找占用CPU或内存高的进程，并通过Top10的占用CPU进程找到可能对应的sql语句。
1、切换到oracle用户下，并新建一个放脚本的目录
</u2/oracle/product/12.2.0/dbhome_1> ll scripts/
-rw-r--r-- 1 oracle oinstall 903 Mar 4 19:46 T100_Check_CMIO.sh
-rw-r--r-- 1 oracle oinstall 548 Mar 4 17:57 T100_DB_CKSQL.sh
</u2/oracle/product/12.2.0/dbhome_1/scripts> cat T100_Check_CMIO.sh
#!/bin/bash
Date=`date`
echo "当前检查时间为 $Date" >>`date +%Y-%m-%d_%H`.log
echo '=========占用CPU最多的十个进程=========' >> `date +%Y-%m-%d_%H`.log
ps aux|head -1 >> `date +%Y-%m-%d_%H`.log &&ps aux|grep -v PID|sort -rn -k +3|head >> `date +%Y-%m-%d_%H`.log
for i in `ps aux|grep -v PID|sort -rn -k +3|head |awk {'print $2'}`
do
echo '占用CPU前十进程对应SQL语句'>>`date +%Y-%m-%d_%H`.log
sh /u2/oracle/product/12.2.0/dbhome_1/scripts/T100_DB_CKSQL.sh $i >> `date +%Y-%m-%d_%H`.log
done
echo '==============分割线==========================' >> `date +%Y-%m-%d_%H`.log
echo '==============分割线==========================' >> `date +%Y-%m-%d_%H`.log
echo '=========占用内存最多的五个进程=========' >> `date +%Y-%m-%d_%H`.log
ps aux|head -1 >> `date +%Y-%m-%d_%H`.log && ps aux|grep -v PID|sort -rn -k +4|head -5>> `date +%Y-%m-%d_%H`.log
</u2/oracle/product/12.2.0/dbhome_1/scripts> cat T100_DB_CKSQL.sh
#!/bin/bash
source /u1/usr/oracle/.profile
sqlplus -S / as sysdba <<EOF
spool /u2/oracle/product/12.2.0/dbhome_1/scripts/sql.log
select a.sql_text,a.sql_id
From v\$sqltext a
where a.hash_value||a.address=(SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value)||DECODE(sql_hash_value,0,prev_sql_addr,sql_address)
FROM v\$session b
WHERE b.paddr = (SELECT addr FROM v\$process c WHERE c.spid = $1)
)
order by piece ;
spool off
EOF
2、创建定时任务，每28分钟执行一次脚本
</u2/oracle/product/12.2.0/dbhome_1/scripts> crontab -l
*/28 * * * * sh /u2/oracle/product/12.2.0/dbhome_1/scripts/T100_Check_CMIO.sh & > /dev/null 2>&13、查看输出结果
</u2/oracle/product/12.2.0/dbhome_1/scripts> cd /u1/usr/oracle/
</u1/usr/oracle> ll
-rw-r--r-- 1 oracle oinstall 6775 Mar 4 19:56 2025-03-04_19.log
-rw-r--r-- 1 oracle oinstall 19796 Mar 4 20:56 2025-03-04_20.log
</u1/usr/oracle> cat 2025-03-04_19.log
当前检查时间为 Tue Mar 4 19:56:02 CST 2025
=========占用CPU最多的十个进程=========
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
oracle 94014 154 0.0 6732168 19912 ? Rs 19:56 0:01 oracletoptst (LOCAL=NO)
oracle 90481 99.3 0.0 225893548 39624 ? Rs 19:50 5:37 oracletopprd (LOCAL=NO)
oracle 94004 95.5 0.0 225886380 32536 ? Rs 19:55 0:01 oracletopprd (LOCAL=NO)
oracle 93586 87.9 0.0 226243932 367400 ? Rs 19:55 0:32 oracletopprd (LOCAL=NO)
oracle 91101 80.7 0.0 225952308 46336 ? Ss 19:51 3:42 oracletopprd (LOCAL=NO)
oracle 91111 79.3 0.0 225952460 45512 ? Rs 19:51 3:38 oracletopprd (LOCAL=NO)
root 92562 70.9 0.0 128432 3280 ? S 03:08 715:01 exp owner=dsdata file=dsdata.dmp log=exp_dsdata.log
oracle 81273 69.2 0.0 225909400 44188 ? Rs 19:38 11:47 oracletopprd (LOCAL=NO)
oracle 76661 68.7 0.0 225889548 38272 ? Ss 19:31 16:38 oracletopprd (LOCAL=NO)
oracle 92724 57.2 0.0 226129124 257092 ? Rs 19:54 0:40 oracletopprd (LOCAL=NO)
占用CPU前十进程对应SQL语句
no rows selected
占用CPU前十进程对应SQL语句
no rows selected
占用CPU前十进程对应SQL语句
no rows selected
占用CPU前十进程对应SQL语句
SQL_TEXT SQL_ID
---------------------------------------------------------------- -------------
select dzba004, dzba010, dzba005 from dzba_t where dzba001 = : 4dpv3g3rs0k7r
p1 AND dzba002 = :p2 AND dzba003 = :p3 AND dzba010 = 's' 4dpv3g3rs0k7r
简略显示