记一次Oracle Sql优化经历--消耗过多CPU(原创)
同事打电话跟我说,数据库CPU过高、swap交换频繁,要我马上看看,这里记录下整个过程以供大家参考,也让大家提点意见
$topas
Topas Monitor for host:
fjlt_wb_db01
EVENTS/QUEUES
FILE/TTY
Mon Feb 13 10:10:09 2012
Interval:
2
Cswitch
13932
Readch
2718.1K
Syscall
344.8K
Writech
162.7K
CPU
User%
Kern%
Wait%
Idle%
Reads
1231
Rawin
0
ALL
89.1
10.9
0.0
0.0
Writes
861
Ttyout
725
Forks
6
Igets
0
Network
KBPS
I-Pack
O-Pack
KB-In
KB-Out
Execs
6
Namei
414
Total
97.6K
24.9K
51.5K
30.8K
66.9K Runqueue
43.0
Dirblk
0
Waitqueue
0.0
Disk
Busy%
KBPS
TPS KB-Read KB-Writ
MEMORY
Total
13.0
22.6K 2715.0
21.7K
932.5
PAGING
Real,MB
31744
Faults
4337
% Comp
85
FileSystem
KBPS
TPS KB-Read KB-Writ
Steals
0
% Noncomp
4
Total
638.7
398.5
637.4
1.3
PgspIn
0
% Client
4
PgspOut
0
Name
PID
CPU%
PgSp Owner
PageIn
0
PAGING SPACE
oracle
66847032
17.3
10.4 oracle
PageOut
1
Size,MB
32768
oracle
33751546
4.3
10.7 oracle
Sios
1
% Used
39
oracle
37093606
2.6
10.6 oracle
% Free
61
oracle
51577090
2.4
10.4 oracle
NFS (calls/sec)
oracle
60752382
2.4
10.2 oracle
SerV2
0
WPAR Activ
0
oracle
2425184
2.3
10.5 oracle
CliV2
0
WPAR Total
0
oracle
38535516
2.1
10.6 oracle
SerV3
0
Press: "h"-help
oracle
65404954
2.0
10.5 oracle
CliV3
0
"q"-quit
oracle
40239486
2.0
10.3 oracle
oracle
65208590
2.0
11.6 oracle
oracle
60555628
1.9
10.5 oracle
oracle
23658656
1.9
10.3 oracle
oracle
47841658
1.9
10.5 oracle
oracle
52363552
1.8
10.3 oracle
oracle
2359684
1.8
10.5 oracle
oracle
31916154
1.7
10.6 oracle
oracle
46530580
1.7
10.9 oracle
oracle
721382
1.6
11.7 oracle
oracle
2884060
1.6
10.5 oracle
oracle
5636414
1.6
10.5 oracle
发现
CPU
使用率为
100%
,
swap
分区交换频繁,其中进程号为
66847032
的
oracle
用户进程的
CPU
使用率为
17.3%
。
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
2 0 7506815 658832 0 0 0 0 0 0 6913 154614 20053 31 12 58 0
5 0 7506576 659071 0 0 0 0 0 0 5763 193844 14268 53 11 36 0
5 0 7