一，OS空间相关
1，检查硬盘
[root@dev ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 117G 5.5G 105G 5% /
/dev/sda2 807G 745G 21G 98% /ebs10
/dev/sdb2 510G 117G 367G 25% /ebs20
tmpfs 16G 3.1M 16G 1% /dev/shm
2，查找最大文件
首先定位文件夹，假设在/EBS10
root@dev ~]# cd /ebs10/
[root@dev ebs10]# find . -ls|sort -nrk7|head -10
[root@dev ebs10]# find . -ls|sort -nrk7|head -10
20316164 92682352 -rwxrwxrwx 1 root root 94814035107 May 10 12:46 ./oracle/prod/db2013.tar.gz
66879687 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 10:01 ./oracle/prod/db/apps_st/data/undo07.dbf
66879685 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 10:03 ./oracle/prod/db/apps_st/data/a_txn_data08.dbf
66879681 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 10:03 ./oracle/prod/db/apps_st/data/a_txn_data10.dbf
66879678 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 06:47 ./oracle/prod/db/apps_st/data/system19.dbf
66879677 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 10:03 ./oracle/prod/db/apps_st/data/undo06.dbf
66879670 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 10:03 ./oracle/prod/db/apps_st/data/a_txn_ind10.dbf
66879656 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 10:04 ./oracle/prod/db/apps_st/data/undo05.dbf
66879640 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 10:03 ./oracle/prod/db/apps_st/data/a_txn_data07.dbf
66879635 20992032 -rw-r----- 1 oradev dba 21474844672 May 14 10:04 ./oracle/prod/db/apps_st/data/a_txn_ind11.dbf
如果挂载满了可以考虑，删除追踪日志，网络日志，归档日志，旧的安装文件等
查询给出目录下占用空间最大的文件夹
[root@dev ebs10]# du -s|sort -nr|head -10
671799376 .
检查脚本
[root@dev oradev]# cat disk.sh
#!/bin/bash
mntlist="/ebs10 / /ebs20 /dev/shm"
for ml in $mnlist
do
echo $ml
usedspc=$(df -h $ml|awk '{print $5}'|grep -v capacity|cut -d "%" -f1 -)
BOX=$(uname -a|awk '{print $2}')
#
case $usedspc in
[0-9])
arcstat='relax,lots of disk space:$usedspc"
;;
[1-7][0-9])
arcstat='disk spce okay:$usedspc"
;;
[8][0-9])
arcstat="space getting low:$usedspc"
;;
[9][0-9])
arcstat="waring,runing out of space:$usedspc"
echo $arcstat $ml|mailx -s "space on:$BOX" weias@***.com.cn
;;
[1][0][0])
arcstat="uodate resume, no space left:$usedspc"
echo $arcstat $ml|mailx -s "space on:$BOX" weias@****.com.cn
;;
* )
arcstat="huh?:$usedspc"
;;
esac
#
BOX=$(uanme-a|awk '{print $2}')
echo $arcstat
#
done
#
2，确定服务器性能是否与CPU,I/O 内存或者网络相关
vmstat 1 10 每一秒执行一次 执行10次
3，确定消耗资源最多的进程
[root@dev prod]# top //确定进程
[root@dev prod]# ps 31159
PID TTY STAT TIME COMMAND
31159 ? Ss 1:54 oracledev (LOCAL=NO)
最个常用的按钮 < > 排序 d 改变刷新时间 空格键 立刻刷新 F/0 选择一个排序 z 改变颜色
6.5 确定cpu 和内存的瓶颈
最耗CPU的进程
[root@test4 ~]# ps -e -o pcpu,pid,user,tty,args|sort -n -k 1 -r|head
95.4 19360 appldev ? frmweb webfile=5,8,test4_9095,/ebs10/oracle/prod/inst/apps/TEST4_test4/logs/ora/10.1.2/forms/socket.log
82.2 15911 appldev ? frmweb webfile=5,24,test4_9095,/ebs10/oracle/prod/inst/apps/TEST4_test4/logs/ora/10.1.2/forms/socket.log
23.0 28378 oradev ? oracleTEST4 (LOCAL=NO)
20.0 28380 oradev ? oracleTEST4 (LOCAL=NO)
17.0 28382 oradev ? oracleTEST4 (LOCAL=NO)
14.0 28384 oradev ? oracleTEST4 (LOCAL=NO)
2.6 28280 oradev ? oracleTEST4 (LOCAL=NO)
1.6 18861 oradev ? oracleTEST4 (LOCAL=NO)
1.2 15783 appldev ? /ebs10/oracle/prod/apps/tech_st/10.1.3/Apache/Apache/bin/httpd -d /ebs10/oracle/prod/apps/tech_st/10.1.3/Apache/Apache -DSSL -f /ebs10/oracle/prod/inst/apps/TEST4_test4/ora/10.1.3/Apache/Apache/conf/httpd.conf
0.9 4467 oradev ? /ebs10/oracle/prod/db/tech_st/11.1.0/bin/tnslsnr TEST4 -inherit
4，最耗内存的进程
[root@test4 ~]# ps -e -o pmem,pid,user,tty,args|sort -n -k 1 -r|head
9.3 19911 oradev ? oracleTEST4 (LOCAL=NO)
9.1 4401 oradev ? ora_smon_TEST4
8.7 4615 oradev ? ora_cjq0_TEST4
8.5 4435 oradev ? ora_q001_TEST4
8.4 21971 oradev ? oracleTEST4 (LOCAL=NO)
8.3 21959 oradev ? oracleTEST4 (LOCAL=NO)
8.0 21962 oradev ? oracleTEST4 (LOCAL=NO)
8.0 21946 oradev ? oracleTEST4 (LOCAL=NO)
7.8 4395 oradev ? ora_dbw0_TEST4
7.8 20066 oradev ? oracleTEST4 (LOCAL=NO)
取一个表名
[root@test4 ~]# alias topc='ps -e -o pcpu,pid,user,tty,args|sort -n -k 1 -r|head'
6,[oraprod@ebsdb ~]$ iostat -xd (X扩展 d设备) 10
检查使用过多的IO
select *
from (select a.PARSING_SCHEMA_NAME,
a.DIRECT_WRITES,
a.DISK_READS,
substr(a.SQL_TEXT, 1, 75)
from v$sql a
order by a.DISK_READS desc)
where rownum < 20
检查当前正在等待的I/O
select * from v$session where event like 'db file%read
二 分析是否存在网络瓶颈
[root@backup 1]# netstat -ptc
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
getnameinfo failed
tcp 0 148 backup.bitland.com.cn:ssh [UNKNOWN]:4414 ESTABLISHED 26961/0
如果Send-Q值高说明网络过载
检查数据的连接性
ping db/ip
tnsping ip
三 将一个资源密集性的进程映射到一个数据库进程
1 ，找出最耗cpu 的资源
[oraprod@ebsdb ~]$ ps -e -o pcpu,pid,user,tty,args|sort -n -k 1 -r|head
88.8 28799 oraprod ? ora_j000_prod
74.6 7103 oraprod ? oracleprod (LOCAL=NO)
35.8 7506 oraprod ? oracleprod (LOCAL=NO)
23.0 7307 oraprod ? oracleprod (LOCAL=NO)
19.2 29360 oraprod ? oracleprod (LOCAL=NO)
18.2 23699 oraprod ? oracleprod (LOCAL=NO)
12.9 19182 oraprod ? oracleprod (LOCAL=NO)
11.7 3824 oraprod ? oracleprod (LOCAL=NO)
10.0 19271 oraprod ? oracleprod (LOCAL=NO)
2,查询数据字典 28799
SELECT SQL_TEXT,a.SQL_ID
FROM V$SQLAREA A, V$SESSION B
WHERE A.SQL_ID = B.PREV_SQL_ID
AND B.SID = (select s.sid
from v$session s,v$process p
where p.spid= 29360 and s.PADDR=p.ADDR );
执行计划
select * from table(dbms_xplan.display_cursor(('av6u1g3pbjfs0')))
通过 ordebug 确认
SQL> oradebug setospid 29360;
Oracle pid: 183, Unix process pid: 29360, image: oracle@ebsdb.***.com.cn
SQL> oradebug current_sql;
select * from APPS.OKL_LA_RELEASE_ASSETS_UV
SQL> oradebug tracefile_name;
/ebs10/oracle/prod/db/tech_st/11.1.0/admin/prod_ebsdb/diag/rdbms/prod/prod/trace/prod_ora_29360.trc
SQL> oradebug help 帮助
3，终止一个密集的进程
alter system kill session 'sid,serial#');
kill -i pid
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15187685/viewspace-761149/ ，如需转载，请注明出处，否则将追究法律责任。
