教你如何解决CPU过度消耗的问题
<br> --使用vmstat,top等辅助解决Oracle数据库性能问题<br> <br> <b>问题描述:</b><br> <br> 开发人员报告系统运行缓慢，影响用户访问.<br> <br> <b>1.登陆数据库主机</b><br> <br> 使用vmstat检查，发现CPU资源已经耗尽，大量任务位于运行队列:<br> <br> bash-2.03$ vmstat 3<br> procs memory page disk faults cpu<br> r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id<br> 0 0 055042321464112 0 0 0 0 0 0 0 0 1 1 0 4294967196 0 0 -84 -5 -145<br> 131 0 0 5368072 1518360 56 691 0 2 2 0 0 0 1 0 0 3011 7918 2795 97 3 0<br> 131 0 0 5377328 1522464 81 719 0 2 2 0 0 0 1 0 0 2766 8019 2577 96 4 0<br> 130 0 0 5382400 1524776 67 682 0 0 0 0 0 0 0 0 0 3570 8534 3316 97 3 0<br> 134 0 0 5373616 1520512 127 1078 0 2 2 0 0 0 1 0 0 3838 9584 3623 96 4 0<br> 136 0 0 5369392 1518496 107 924 0 5 5 0 0 0 0 0 0 2920 8573 2639 97 3 0<br> 132 0 0 5364912 1516224 63 578 0 0 0 0 0 0 0 0 0 3358 7944 3119 97 3 0<br> 129 0 0 5358648 1511712 189 1236 0 0 0 0 0 0 0 0 0 3366 10365 3135 95 5 0<br> 129 0 0 5354528 1511304 120 1194 0 0 0 0 0 0 0 4 0 3235 8864 2911 96 4 0<br> 128 0 0 5346848 1507704 99 823 0 0 0 0 0 0 0 3 0 3189 9048 3074 96 4 0<br> 125 0 0 5341248 1504704 80 843 0 2 2 0 0 0 6 1 0 3563 9514 3314 95 5 0<br> 133 0 0 5332744 1501112 79 798 0 0 0 0 0 0 0 1 0 3218 8805 2902 97 3 0<br> 129 0 0 5325384 1497368 107 643 0 2 2 0 0 0 1 4 0 3184 8297 2879 96 4 0<br> 126 0 0 5363144 1514320 81 753 0 0 0 0 0 0 0 0 0 2533 7409 2164 97 3 0<br> 136 0 0 5355624 1510512 169 566 786 0 0 0 0 0 0 1 0 3002 8600 2810 96 4 0<br> 130 1 0 5351448 1502936 267 580 1821 0 0 0 0 0 0 0 0 3126 7812 2900 96 4 0<br> 129 0 0 5347256 1499568 155 913 2 2 2 0 0 0 0 1 0 2225 8076 1941 98 2 0<br> 116 0 0 5338192 1495400 177 1162 0 0 0 0 0 0 0 1 0 1947 7781 1639 97 3 0<br> <br> <b>2.使用Top命令</b><br> <br> 观察进程CPU耗用,发现没有明显过高CPU使用的进程<br> <br> $ top<br> <br> last pid: 28313; load averages: 99.90, 117.54, 125.71 23:28:38<br> 296 processes: 186 sleeping, 99 running, 2 zombie, 9 on cpu<br> CPU states: 0.0% idle,96.5% user, 3.5% kernel,0.0% iowait, 0.0%swap<br> Memory: 4096M real, 1404M free, 2185M swap in use, 5114M swap free<br> <br> PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND<br> 27082 oracle8i 1 33 0 1328M 1309M run 0:17 1.29% oracle<br> 26719 oracle8i 1 55 0 1327M 1306M sleep 0:29 1.11% oracle<br> 28103 oracle8i 1 35 0 1327M 1304M run 0:06 1.10% oracle<br> 28161 oracle8i 1 25 0 1327M 1305M run 0:04 1.10% oracle<br> 26199 oracle8i 1 45 0 1328M 1309M run 0:42 1.10% oracle<br> 26892 oracle8i 1 33 0 1328M 1310M run 0:24 1.09% oracle<br> 27805 oracle8i 1 45 0 1327M 1306M cpu/1 0:10 1.04% oracle<br> 23800 oracle8i 1 23 0 1327M 1306M run 1:28 1.03% oracle<br> 25197 oracle8i 1 34 0 1328M 1309M run 0:57 1.03% oracle<br> 21593 oracle8i 1 33 0 1327M 1306M run 2:12 1.01% oracle<br> 27616 oracle8i 1 45 0 1329M 1311M run 0:14 1.01% oracle<br> 27821 oracle8i 1 43 0 1327M 1306M run 0:10 1.00% oracle<br> 26517 oracle8i 1 33 0 1328M 1309M run 0:33 0.97% oracle<br> 25785 oracle8i 1 44 0 1328M 1309M run 0:46 0.96% oracle<br> 26241 oracle8i 1 45 0 1327M 1306M run 0:42 0.96% oracle<br> <br> <b>3.检查进程数量</b><br> <br> bash-2.03$ ps -ef|grep ora|wc -l<br> 258<br> bash-2.03$ ps -ef|grep ora|wc -l<br> 275<br> bash-2.03$ ps -ef|grep ora|wc -l<br> 274<br> bash-2.03$ ps -ef|grep ora|wc -l<br> 278<br> bash-2.03$ ps -ef|grep ora|wc -l<br> 277<br> bash-2.03$ ps -ef|grep ora|wc -l<br> 366<br> <br> 发现系统存在大量Oracle进程,大约在300左右，而正常情况下Oracle连接数应该在100左右.<br> <br> <b>4.检查数据库</b><br> <br> 查询v$session_wait获取各进程等待事件<br> <br> SQL> select sid,event,p1,p1text from v$session_wait;<br> <br> SID EVENT P1 P1TEXT<br> ---------- ------------------------------ ---------- -------- 124 latch free 1.6144E 10 address<br> 1 pmon timer 300 duration<br> 2 rdbms ipc message 300 timeout<br> 3 rdbms ipc message 300 timeout<br> 11 rdbms ipc message 30000 timeout<br> 6 rdbms ipc message 180000 timeout<br> 4 rdbms ipc message 300 timeout<br> 134 rdbms ipc message 6000 timeout<br> 147 rdbms ipc message 6000 timeout<br> 275 rdbms ipc message 17995 timeout<br> 274 rdbms ipc message 6000 timeout<br> <br> SID EVENT P1 P1TEXT<br> ---------- ------------------------------ ---------- --------<br> 118 rdbms ipc message 6000 timeout<br> 7 buffer busy waits 17 file#<br> 56 buffer busy waits 17 file#<br> 161 buffer busy waits 17 file#<br> 195 buffer busy waits 17 file#<br> 311 buffer busy waits 17 file#<br> 314 buffer busy waits 17 file#<br> 205 buffer busy waits 17 file#<br> 269 buffer busy waits 17 file#<br> 200 buffer busy waits 17 file#<br> 164 buffer busy waits 17 file#<br> <br> SID EVENT P1 P1TEXT<br> ---------- ------------------------------ ---------- -------<br> 140 buffer busy waits 17 file#<br> 66 buffer busy waits 17 file#<br> 10 db file sequential read 17 file#<br> 18 db file sequential read 17 file#<br> 54 db file sequential read 17 file#<br> 49 db file sequential read 17 file#<br> 48 db file sequential read 17 file#<br> 46 db file sequential read 17 file#<br> 45 db file sequential read 17 file#<br> 35 db file sequential read 17 file#<br> 30 db file sequential read 17 file#<br> <br> SID EVENT P1 P1TEXT<br> ---------- ------------------------------ ---------- -------<br> 29 db file sequential read 17 file#<br> 22 db file sequential read 17 file#<br> 178 db file sequential read 17 file#<br> 175 db file sequential read 17 file#<br> 171 db file sequential read 17 file#<br> 123 db file sequential read 17 file#<br> 121 db file sequential read 17 file#<br> 120 db file sequential read 17 file#<br> 117 db file sequential read 17 file#<br> 114 db file sequential read 17 file#<br> 113 db file sequential read 17 file#<br> <br> SID EVENT P1 P1TEXT<br> ---------- ------------------------------ ---------- ------<br> 111 db file sequential read 17 file#<br> 107 db file sequential read 17 file#<br> 80 db file sequential read 17 file#<br> 222 db file sequential read 17 file#<br> 218 db file sequential read 17 file#<br> 216 db file sequential read 17 file#<br> 213 db file sequential read 17 file#<br> 199 db file sequential read 17 file#<br> 198 db file sequential read <
页:
[1]