数据库cpu使用率正常时是80%左右，现在只有变成20%，CPU使用率一直上不去，速度变得很慢，下面是STATSPACK收集的信息，帮忙看看瓶颈大概在哪里？如何解决？谢谢STATSPACK report forDatabase DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1210487487 orcl 1 03-Jun-09 17:09 10.2.0.4.0 NO
Host Name: P570A Num CPUs: 16 Phys Memory (MB): 31,104
~~~~Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 15-Jun-09 21:50:58 164 11.9
End Snap: 2 15-Jun-09 21:54:35 164 12.0
Elapsed: 3.62 (mins)Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 10,240M Std Block Size: 8K
Shared Pool Size: 512M Log Buffer: 13,893KLoad Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 63,814.01 446,698.06
Logical reads: 96,967.45 678,772.13
Block changes: 298.27 2,087.90
Physical reads: 12,641.78 88,492.48
Physical writes: 22.36 156.55
User calls: 2.88 20.19
Parses: 23.87 167.06
Hard parses: 0.20 1.42
Sorts: 2.88 20.13
Logons: 0.01 0.10
Executes: 1,080.60 7,564.23
Transactions: 0.14 % Blocks changed per Read: 0.31 Recursive Call %: 99.77
Rollback per transaction %: 6.45 Rows per Sort: 243.75Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 91.18 Redo NoWait %: 99.99
Buffer Hit %: 86.96 In-memory Sort %: 100.00
Library Hit %: 99.87 Soft Parse %: 99.15
Execute to Parse %: 97.79 Latch Hit %: 86.59
Parse CPU to Parse Elapsd %: 69.23 % Non-Parse CPU: 99.97 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 91.16 91.10
% SQL with executions>1: 87.04 88.09
% Memory for SQL w/exec>1: 86.40 87.61Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
read by other session 1,857,391 2,650 1 78.7
CPU time 353 10.5
db file scattered read 226,694 318 1 9.4
db file sequential read 38,443 34 1 1.0
latch: cache buffers chains 53,165 6 0 .2
-------------------------------------------------------------Host CPU (CPUs: 16)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.01 0.01 10.90 6.57 82.53 11.13 18.15Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 10.83
% of busy CPU for Instance: 61.97
%DB time waiting for CPU - Resource Mgr:Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 31,104.0 31,104.0
SGA use (MB): 10,992.0 10,992.0
PGA use (MB): 354.2 353.9
% Host Mem used for SGA+PGA: 36.5 36.5
-------------------------------------------------------------
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1210487487 orcl 1 03-Jun-09 17:09 10.2.0.4.0 NO
Host Name: P570A Num CPUs: 16 Phys Memory (MB): 31,104
~~~~Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 15-Jun-09 21:50:58 164 11.9
End Snap: 2 15-Jun-09 21:54:35 164 12.0
Elapsed: 3.62 (mins)Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 10,240M Std Block Size: 8K
Shared Pool Size: 512M Log Buffer: 13,893KLoad Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 63,814.01 446,698.06
Logical reads: 96,967.45 678,772.13
Block changes: 298.27 2,087.90
Physical reads: 12,641.78 88,492.48
Physical writes: 22.36 156.55
User calls: 2.88 20.19
Parses: 23.87 167.06
Hard parses: 0.20 1.42
Sorts: 2.88 20.13
Logons: 0.01 0.10
Executes: 1,080.60 7,564.23
Transactions: 0.14 % Blocks changed per Read: 0.31 Recursive Call %: 99.77
Rollback per transaction %: 6.45 Rows per Sort: 243.75Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 91.18 Redo NoWait %: 99.99
Buffer Hit %: 86.96 In-memory Sort %: 100.00
Library Hit %: 99.87 Soft Parse %: 99.15
Execute to Parse %: 97.79 Latch Hit %: 86.59
Parse CPU to Parse Elapsd %: 69.23 % Non-Parse CPU: 99.97 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 91.16 91.10
% SQL with executions>1: 87.04 88.09
% Memory for SQL w/exec>1: 86.40 87.61Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
read by other session 1,857,391 2,650 1 78.7
CPU time 353 10.5
db file scattered read 226,694 318 1 9.4
db file sequential read 38,443 34 1 1.0
latch: cache buffers chains 53,165 6 0 .2
-------------------------------------------------------------Host CPU (CPUs: 16)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.01 0.01 10.90 6.57 82.53 11.13 18.15Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 10.83
% of busy CPU for Instance: 61.97
%DB time waiting for CPU - Resource Mgr:Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 31,104.0 31,104.0
SGA use (MB): 10,992.0 10,992.0
PGA use (MB): 354.2 353.9
% Host Mem used for SGA+PGA: 36.5 36.5
-------------------------------------------------------------
-> Ordered by % of DB time desc, Statistic nameStatistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 3,383.9 100.0
DB CPU 374.0 11.1
PL/SQL execution elapsed time 3.2 .1
parse time elapsed 0.4 .0
sequence load elapsed time 0.4 .0
hard parse elapsed time 0.3 .0
repeated bind elapsed time 0.1 .0
PL/SQL compilation elapsed time 0.0 .0
DB time 3,384.2
background elapsed time 5.1
background cpu time 0.6
-------------------------------------------------------------Wait Events DB/Inst: ORCL/orcl Snaps: 1-2
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last) Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
read by other session 1,857,391 0 2,650 1 ########
db file scattered read 226,694 0 318 1 7,312.7
db file sequential read 38,443 0 34 1 1,240.1
latch: cache buffers chains 53,165 0 6 0 1,715.0
db file parallel write 74 0 3 36 2.4
log file parallel write 68 0 1 20 2.2
control file parallel write 109 0 1 5 3.5
control file sequential read 4,354 0 0 0 140.5
wait list latch free 27 0 0 10 0.9
log file sync 18 0 0 14 0.6
log file switch completion 3 0 0 38 0.1
cursor: pin S wait on X 9 56 0 5 0.3
enq: TX - index contention 25 0 0 1 0.8
latch: session allocation 24 0 0 0 0.8
log file single write 2 0 0 5 0.1
buffer busy waits 172 0 0 0 5.5
row cache lock 31 0 0 0 1.0
latch: shared pool 10 0 0 1 0.3
log file sequential read 2 0 0 3 0.1
SQL*Net break/reset to client 18 0 0 0 0.6
SQL*Net message from client 597 0 24,355 40796 19.3
virtual circuit status 8 100 234 29297 0.3
wait for unread message on broadc 216 100 211 977 7.0
jobq slave wait 72 96 211 2929 2.3
Streams AQ: waiting for messages 43 100 210 4883 1.4
Streams AQ: qmn slave idle wait 7 0 193 27623 0.2
Streams AQ: qmn coordinator idle 15 53 193 12891 0.5
SQL*Net message to client 597 0 0 0 19.3
-------------------------------------------------------------Background Wait Events DB/Inst: ORCL/orcl Snaps: 1-2
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last) Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file parallel write 74 0 3 36 2.4
log file parallel write 68 0 1 20 2.2
control file parallel write 109 0 1 5 3.5
control file sequential read 135 0 0 2 4.4
log file single write 2 0 0 5 0.1
log file sequential read 2 0 0 3 0.1
rdbms ipc message 806 95 1,894 2349 26.0
pmon timer 72 100 211 2930 2.3
Streams AQ: qmn slave idle wait 7 0 193 27623 0.2
Streams AQ: qmn coordinator idle 15 53 193 12891 0.5
-------------------------------------------------------------
Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 1-2
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
SQL*Net break/reset to cli 18 100.0
buffer busy waits 172 98.8 .6 .6
buffer deadlock 32 100.0
control file parallel writ 109 44.0 21.1 13.8 16.5 3.7 .9
control file sequential re 4354 99.2 .1 .0 .4 .3 .1
cursor: pin S 27 100.0
cursor: pin S wait on X 9 44.4 55.6
db file parallel write 74 4.1 5.4 5.4 2.7 17.6 24.3 40.5
db file scattered read 226K 70.9 10.4 10.1 5.5 2.4 .6 .1
db file sequential read 38K 85.5 5.7 3.6 2.9 1.7 .6 .0
enq: HW - contention 1 100.0
enq: TX - index contention 25 80.0 8.0 8.0 4.0
latch free 4 100.0
latch: cache buffers chain 50K 99.0 .6 .3 .0 .1
latch: cache buffers lru c 1 100.0
latch: session allocation 20 95.0 5.0
latch: shared pool 9 77.8 11.1 11.1
log file parallel write 68 11.8 7.4 7.4 17.6 23.5 13.2 19.1
log file sequential read 2 50.0 50.0
log file single write 2 50.0 50.0
log file switch completion 3 100.0
log file sync 18 16.7 11.1 11.1 22.2 16.7 11.1 11.1
read by other session 1809K 70.1 12.2 9.5 5.1 2.3 .7 .1
row cache lock 31 93.5 6.5
wait list latch free 28 100.0
SQL*Net message from clien 597 57.5 .7 .2 41.7
SQL*Net message to client 597 100.0
Streams AQ: qmn coordinato 15 46.7 53.3
2、
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
read by other session 1,857,391 2,650 1 78.7
CPU time 353 10.5
db file scattered read 226,694 318 1 9.4
db file sequential read 38,443 34 1 1.0
latch: cache buffers chains 53,165 6 0 .2
从这些等待上看，
1）read by other session 存在竞争
2）CPU time 这是一个BUG，需要打PATCH
3）db file scattered read 磁盘I/O比较高，可能是不恰当索引
4）latch: cache buffers chains 存在竞争建议你先从竞争上查，然后再看索引是否正常。
lz问题出在read by other session等待事件，该事件是buffer busy wait的子类。
时间白白耗在等待上了，而不是解析和执行。
所以cpu使用率也上不去，就是因为出现大量该等待。这个等待多由不良sql引起。lz贴出你的top sql，或者找个地方把完整的statspack上传。
SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'read by other session'; 先看看实际的原因码吧．另外，８楼的，你为什么觉得cpu time是bug?