我的数据库CPU总是满负荷运载,load average也一直很高,我查了一下机器数据,但是对于怎么优化数据库不知道从何入手,还请大家指点一下;
[root@ORACLE root]# top
2:58pm up 40 days, 3:11, 4 users, load average: 42.15, 41.75, 41.69
463 processes: 457 sleeping, 6 running, 0 zombie, 0 stopped
CPU0 states: 91.4% user, 8.5% system, 0.0% nice, 0.0% idle
CPU1 states: 99.6% user, 0.4% system, 0.0% nice, 0.0% idle
CPU2 states: 26.5% user, 73.4% system, 0.0% nice, 0.0% idle
CPU3 states: 71.3% user, 28.0% system, 0.0% nice, 0.6% idle
Mem: 4003200K av, 3971732K used, 31468K free, 0K shrd, 14504K buff
Swap: 4192924K av, 240012K used, 3952912K free 3194640K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
4269 oracle 25 0 315M 283M 282M R 93.9 7.2 24:23 oracle
2408 oracle 25 0 1180 1132 656 R 61.4 0.0 53566m top
2767 oracle 25 0 299M 267M 266M R 57.5 6.8 35:31 oracle
13505 oracle 25 0 250M 219M 218M S 43.3 5.6 10:50 oracle
2759 oracle 25 0 344M 312M 311M R 42.8 7.9 35:44 oracle
25386 root 25 0 1364 1364 836 R 26.8 0.0 7:02 top
4511 oracle 24 0 291M 259M 258M R 18.4 6.6 33:55 oracle
26991 oracle 16 0 196M 165M 164M S 7.7 4.2 4:34 oracle
28066 oracle 15 0 160M 128M 127M S 1.0 3.2 2:33 oracle
20668 oracle 15 0 982M 950M 950M S 0.9 24.3 201:31 oracle
27001 oracle 15 0 155M 123M 122M S 0.9 3.1 2:06 oracle
20617 oracle 15 0 980M 948M 948M S 0.8 24.2 201:13 oracle
3030 oracle 16 0 465M 433M 432M S 0.8 11.0 4:50 oracle
24648 oracle 15 0 71592 38M 38012 S 0.8 0.9 0:05 oracle
18839 oracle 16 0 183M 151M 150M S 0.7 3.8 0:10 oracle
3399 szjlt 15 0 10252 5880 3348 S 0.6 0.1 131:38 java
1481 oracle 16 0 3560 3068 2620 S 0.2 0.0 47:48 tnslsnr
2836 oracle 15 0 886M 854M 853M S 0.2 21.8 4:42 oracle
1490 oracle 15 0 42144 8528 8272 S 0.1 0.2 50:42 oracle
1498 oracle 15 0 117M 85M 87496 S 0.1 2.1 17:02 oracle
19575 oracle 25 0 308M 276M 275M S 0.1 7.0 25:27 oracle
1 root 15 0 456 420 404 S 0.0 0.0 3:58 init
2 root 0K 0 0 0 0 SW 0.0 0.0 0:00 migration_CPU0
3 root 0K 0 0 0 0 SW 0.0 0.0 0:00 migration_CPU1
4 root 0K 0 0 0 0 SW 0.0 0.0 0:00 migration_CPU2
5 root 0K 0 0 0 0 SW 0.0 0.0 0:00 migration_CPU3
[root@ORACLE root]# vmstat 5 20
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
5 36 0 240016 12532 5940 3225148 0 0 1 1 1 1 0 1 0
8 36 0 240016 12532 5956 3225180 0 0 6 84 766 463 80 20 0
8 36 0 240016 12524 5972 3225208 0 0 2 68 732 425 87 13 0
7 36 0 240016 12224 5988 3225248 0 0 6 78 824 520 86 14 0
4 36 0 240016 12404 6008 3225280 0 0 8 59 907 615 73 27 1
7 36 0 240016 11120 6032 3221384 0 0 14 59 809 508 65 27 8
6 36 0 240016 11128 6048 3221404 0 0 2 55 737 454 79 21 0
5 36 0 240016 11264 6064 3221440 0 0 6 73 1165 889 75 25 0
6 36 0 240016 11112 6144 3220700 0 0 538 69 784 579 83 17 0
6 36 0 240016 11108 6160 3220696 0 0 2 62 765 459 84 16 0
4 36 1 240016 11204 6184 3220716 0 0 4 80 950 661 74 24 2
7 36 2 240016 10892 6208 3220688 0 0 2 56 907 608 74 26 0
3 36 0 240016 13424 6224 3218688 0 0 5 68 755 481 75 22 2
3 36 0 240016 12836 6316 3218692 0 0 26 71 976 686 54 28 18
5 36 0 240016 12780 6340 3218748 0 0 14 110 871 596 71 27 2
4 36 0 240016 13160 6364 3218824 0 0 14 53 891 605 74 25 1
5 36 0 240016 12520 6380 3218856 0 0 4 53 802 500 74 26 0
4 36 0 240016 11908 6396 3217488 0 0 2 50 742 432 73 27 0
6 36 0 240016 12800 6416 3217532 0 0 5 56 683 377 81 19 1
5 36 0 240016 12800 6432 3217560 0 0 4 66 874 572 82 17 1
[root@ORACLE root]# ps aux | grep oracle | wc -l
287
#占用cpu资源很高的进程都是这个sql
SQL> select sql_text,spid,v$session.program,process from
2 v$sqlarea,v$session,v$process
3 where v$sqlarea.address=v$session.sql_address
4 and v$sqlarea.hash_value=v$session.sql_hash_value
5 and v$session.paddr=v$process.addr
6 and v$process.spid in (17501);
SQL_TEXT
--------------------------------------------------------------------------------
SPID PROGRAM PROCESS
------------ ------------------------------------------------ ------------
update moonuser set totalscore=totalscore+(:1),monthscore=monthscore+(:2) where
userid=:3
17501 JDBC Thin Client
SQL> select sid,event,p1,p1text from v$session_wait;
SID EVENT
---------- ----------------------------------------------------------------
P1 P1TEXT
---------- ----------------------------------------------------------------
37 null event
1952673792
121 null event
1952673792
188 null event
1952673792
SID EVENT
---------- ----------------------------------------------------------------
P1 P1TEXT
---------- ----------------------------------------------------------------
194 null event
1413697536
1 pmon timer
300 duration
2 rdbms ipc message
300 timeout
SID EVENT
---------- ----------------------------------------------------------------
P1 P1TEXT
---------- ----------------------------------------------------------------
3 rdbms ipc message
125 timeout
6 rdbms ipc message
180000 timeout
7 rdbms ipc message
500 timeout
SID EVENT
---------- ----------------------------------------------------------------
P1 P1TEXT
---------- ----------------------------------------------------------------
10 rdbms ipc message
6000 timeout
13 rdbms ipc message
300 timeout
9 rdbms ipc message
30000 timeout
SID EVENT
---------- ----------------------------------------------------------------
P1 P1TEXT
---------- ----------------------------------------------------------------
4 rdbms ipc message
300 timeout
5 smon timer
300 sleep time
11 SQL*Net message from client
1952673792 driver id
SID EVENT
---------- ----------------------------------------------------------------
P1 P1TEXT
---------- ----------------------------------------------------------------
17 SQL*Net message from client
1952673792 driver id
19 SQL*Net message from client
1952673792 driver id
21 SQL*Net message from client
1952673792 driver id
#...全部是 SQL*Net message from client...#
SID EVENT
---------- ----------------------------------------------------------------
P1 P1TEXT
---------- ----------------------------------------------------------------
20 SQL*Net message from client
1952673792 driver id
18 SQL*Net message from client
1952673792 driver id
16 SQL*Net message from client
1952673792 driver id
SID EVENT
---------- ----------------------------------------------------------------
P1 P1TEXT
---------- ----------------------------------------------------------------
8 wakeup time manager
0
259 rows selected.
请问我的数据库哪里存在问题,该怎么优化,谢谢.