数据库最近运行很慢，数据库服务器是soalris 8,16G 内存，其中SGA 11.2g,PGA 1.5g.AWR收集的信息如下：
WORKLOAD REPOSITORY report for
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 9,248M 9,824M Std Block Size: 8K
Shared Pool Size: 1,904M 1,328M Log Buffer: 14,432K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 658,657.70 8,027.39
Logical reads: 69,366.97 845.41
Block changes: 4,145.86 50.53
Physical reads: 192.80 2.35
Physical writes: 510.59 6.22
User calls: 380.16 4.63
Parses: 84.16 1.03
Hard parses: 25.21 0.31
Sorts: 10.07 0.12
Logons: 0.38 0.00
Executes: 192.54 2.35
Transactions: 82.05
% Blocks changed per Read: 5.98 Recursive Call %: 54.27
Rollback per transaction %: 2.02 Rows per Sort: 326.14
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 99.64
Buffer Hit %: 99.73 In-memory Sort %: 100.00
Library Hit %: 88.47 Soft Parse %: 70.05
Execute to Parse %: 56.29 Latch Hit %: 99.82
Parse CPU to Parse Elapsd %: 45.17 % Non-Parse CPU: 71.34
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.51 93.83
% SQL with executions>1: 42.94 51.39
% Memory for SQL w/exec>1: 31.06 50.83
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file switch (checkpoint in 533,845 390,087 731 29.3 Configurat
db file sequential read 6,926,502 222,306 32 16.7 User I/O
CPU time 183,882 13.8
free buffer waits 8,401,027 156,264 19 11.7 Configurat
log file sync 7,053,456 89,745 13 6.7 Commit
-------------------------------------------------------------
Time Model Statistics DB/Inst: SXGSMDB/sxgsmdb Snaps: 3115-3139
-> Total time in database user-calls (DB Time): 1332543s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 1,099,052.7 82.5
DB CPU 183,882.2 13.8
parse time elapsed 115,295.2 8.7
hard parse elapsed time 35,461.0 2.7
connection management call elapsed time 19,526.6 1.5
sequence load elapsed time 17,642.0 1.3
PL/SQL execution elapsed time 4,019.0 .3
failed parse elapsed time 2,889.0 .2
hard parse (sharing criteria) elapsed time 688.6 .1
PL/SQL compilation elapsed time 595.9 .0
hard parse (bind mismatch) elapsed time 48.8 .0
repeated bind elapsed time 5.1 .0
DB time 1,332,543.0 N/A
background elapsed time 124,127.5 N/A
background cpu time 22,216.1 N/A
-------------------------------------------------------------
Wait Class DB/Inst: SXGSMDB/sxgsmdb Snaps: 3115-3139
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc
Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Configuration 16,230,197 96.5 598,363 37 2.3
User I/O 8,912,662 .0 237,347 27 1.3
Concurrency 5,189,196 1.2 158,636 31 0.7
Commit 7,053,456 .1 89,745 13 1.0
System I/O 11,269,231 .0 81,520 7 1.6
Other 1,038,157 3.3 40,526 39 0.1
Application 1,525,605 .9 39,509 26 0.2
Network 32,850,512 .0 115 0 4.6
Administrative 9 .0 1 122 0.0
-------------------------------------------------------------
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
log file switch (checkpoint 533,845 73.1 390,087 731 0.1
db file sequential read 6,926,502 .0 222,306 32 1.0
free buffer waits 8,401,027 95.5 156,264 19 1.2
log file sync 7,053,456 .1 89,745 13 1.0
buffer busy waits 397,207 15.2 64,711 163 0.1
db file parallel write 1,309,440 .0 58,352 45 0.2
latch: shared pool 1,831,859 .0 48,522 26 0.3
latch: library cache 2,189,310 .0 39,560 18 0.3
enq: TX - row lock contentio 12,667 98.6 36,724 2899 0.0
enq: HW - contention 55,446 17.7 32,207 581 0.0
enq: US - contention 214,596 3.1 24,659 115 0.0
log file parallel write 7,545,685 .0 22,306 3 1.1
enq: SQ - contention 4,919 93.9 14,105 2867 0.0
db file scattered read 1,361,706 .0 9,195 7 0.2
buffer exterminate 9,652 64.0 6,941 719 0.0
write complete waits 5,440 90.2 5,047 928 0.0
read by other session 89,322 3.5 4,216 47 0.0
rdbms ipc reply 168,888 .9 4,105 24 0.0
enq: TX - index contention 7,073 13.8 3,608 510 0.0
latch free 55,322 .0 3,261 59 0.0
enq: RO - fast object reuse 654 81.2 1,661 2539 0.0
SQL*Net break/reset to clien 1,512,243 .0 1,012 1 0.2
local write wait 1,077 75.0 875 812 0.0
row cache lock 16,739 .2 701 42 0.0
SGA: allocation forcing comp 19,712 93.9 618 31 0.0
library cache lock 230 84.3 604 2627 0.0
control file parallel write 61,800 .0 567 9 0.0
enq: JD - contention 173 96.5 501 2895 0.0
os thread startup 921 3.8 471 512 0.0
log file switch completion 2,091 11.9 406 194 0.0
library cache pin 963 9.8 356 370 0.0
control file sequential read 2,350,536 .0 272 0 0.3
direct path read 409,139 .0 217 1 0.1
direct path write 84,372 .0 193 2 0.0
direct path write temp 12,458 .0 143 11 0.0
enq: FB - contention 80 48.8 127 1583 0.0
log buffer space 311 3.5 126 404 0.0
enq: TM - contention 41 92.7 112 2732 0.0
Log file init write 15,290 .0 102 7 0.0
db file parallel read 840 .0 94 112 0.0
enq: CF - contention 536 .0 83 154 0.0
enq: CI - contention 41 43.9 78 1903 0.0
undo segment tx slot 79 100.0 78 981 0.0
enq: TX - contention 239 8.8 75 314 0.0
SQL*Net message to client 32,544,794 .0 71 0 4.6
library cache load lock 192 3.6 58 303 0.0
undo segment extension 7,226,898 100.0 43 0 1.0
latch: cache buffers lru cha 77,855 .0 42 1 0.0
latch: row cache objects 3,222 .0 30 9 0.0
SQL*Net more data to client 259,125 .0 24 0 0.0
SQL*Net more data from clien 46,593 .0 20 0 0.0
LGWR wait for redo copy 485,129 .0 19 0 0.1
log file single write 890 .0 19 21 0.0
latch: cache buffers chains 26,623 .0 11 0 0.0
kksfbc child completion 124 96.8 6 52 0.0
reliable message 1,300 .0 5 4 0.0
Data file init write 442 .0 5 11 0.0
log file sequential read 880 .0 5 5 0.0
latch: enqueue hash chains 1,360 .0 2 1 0.0
latch: session allocation 572 .0 2 3 0.0
Wait Events DB/Inst: SXGSMDB/sxgsmdb Snaps: 3115-3139
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
cursor: mutex X 695,762 .0 2 0 0.1
latch: undo global data 966 .0 1 1 0.0
switch logfile command 9 .0 1 122 0.0
direct path read temp 11,497 .0 0 0 0.0
latch: library cache pin 218 .0 0 2 0.0
latch: object queue header o 275 .0 0 1 0.0
latch: redo allocation 239 .0 0 1 0.0
enq: PE - contention 1 .0 0 175 0.0
log file switch (private str 7 .0 0 20 0.0
latch: library cache lock 35 .0 0 4 0.0
wait list latch free 7 .0 0 18 0.0
latch: In memory undo latch 230 .0 0 0 0.0
enq: TX - allocate ITL entry 6 .0 0 16 0.0
db file single write 17 .0 0 5 0.0
latch: redo writing 128 .0 0 1 0.0
cursor: mutex S 18,612 .0 0 0 0.0
latch: messages 56 .0 0 1 0.0
enq: JS - queue lock 1 .0 0 21 0.0
buffer deadlock 993 100.0 0 0 0.0
latch: checkpoint queue latc 40 .0 0 0 0.0
SQL*Net message from client 32,544,833 .0 5,662,377 174 4.6
Streams AQ: qmn slave idle w 3,088 .0 84,336 27311 0.0
Streams AQ: qmn coordinator 6,236 50.6 84,335 13524 0.0
wait for unread message on b 86,776 98.0 84,142 970 0.0
Streams AQ: waiting for mess 17,224 97.7 83,022 4820 0.0
Streams AQ: waiting for time 1,482 46.3 76,269 51464 0.0
jobq slave wait 22,249 98.6 64,981 2921 0.0
SGA: MMAN sleep for componen 333,884 97.1 5,008 15 0.0
pipe get 2,522 16.3 1,962 778 0.0
class slave wait 60 100.0 293 4888 0.0
-------------------------------------------------------------
哪位高手给指点一下问题所在，是数据库的问题呢，还是机器硬件本身。详见附件。谢谢
WORKLOAD REPOSITORY report for
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 9,248M 9,824M Std Block Size: 8K
Shared Pool Size: 1,904M 1,328M Log Buffer: 14,432K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 658,657.70 8,027.39
Logical reads: 69,366.97 845.41
Block changes: 4,145.86 50.53
Physical reads: 192.80 2.35
Physical writes: 510.59 6.22
User calls: 380.16 4.63
Parses: 84.16 1.03
Hard parses: 25.21 0.31
Sorts: 10.07 0.12
Logons: 0.38 0.00
Executes: 192.54 2.35
Transactions: 82.05
% Blocks changed per Read: 5.98 Recursive Call %: 54.27
Rollback per transaction %: 2.02 Rows per Sort: 326.14
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 99.64
Buffer Hit %: 99.73 In-memory Sort %: 100.00
Library Hit %: 88.47 Soft Parse %: 70.05
Execute to Parse %: 56.29 Latch Hit %: 99.82
Parse CPU to Parse Elapsd %: 45.17 % Non-Parse CPU: 71.34
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.51 93.83
% SQL with executions>1: 42.94 51.39
% Memory for SQL w/exec>1: 31.06 50.83
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file switch (checkpoint in 533,845 390,087 731 29.3 Configurat
db file sequential read 6,926,502 222,306 32 16.7 User I/O
CPU time 183,882 13.8
free buffer waits 8,401,027 156,264 19 11.7 Configurat
log file sync 7,053,456 89,745 13 6.7 Commit
-------------------------------------------------------------
Time Model Statistics DB/Inst: SXGSMDB/sxgsmdb Snaps: 3115-3139
-> Total time in database user-calls (DB Time): 1332543s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 1,099,052.7 82.5
DB CPU 183,882.2 13.8
parse time elapsed 115,295.2 8.7
hard parse elapsed time 35,461.0 2.7
connection management call elapsed time 19,526.6 1.5
sequence load elapsed time 17,642.0 1.3
PL/SQL execution elapsed time 4,019.0 .3
failed parse elapsed time 2,889.0 .2
hard parse (sharing criteria) elapsed time 688.6 .1
PL/SQL compilation elapsed time 595.9 .0
hard parse (bind mismatch) elapsed time 48.8 .0
repeated bind elapsed time 5.1 .0
DB time 1,332,543.0 N/A
background elapsed time 124,127.5 N/A
background cpu time 22,216.1 N/A
-------------------------------------------------------------
Wait Class DB/Inst: SXGSMDB/sxgsmdb Snaps: 3115-3139
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc
Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Configuration 16,230,197 96.5 598,363 37 2.3
User I/O 8,912,662 .0 237,347 27 1.3
Concurrency 5,189,196 1.2 158,636 31 0.7
Commit 7,053,456 .1 89,745 13 1.0
System I/O 11,269,231 .0 81,520 7 1.6
Other 1,038,157 3.3 40,526 39 0.1
Application 1,525,605 .9 39,509 26 0.2
Network 32,850,512 .0 115 0 4.6
Administrative 9 .0 1 122 0.0
-------------------------------------------------------------
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
log file switch (checkpoint 533,845 73.1 390,087 731 0.1
db file sequential read 6,926,502 .0 222,306 32 1.0
free buffer waits 8,401,027 95.5 156,264 19 1.2
log file sync 7,053,456 .1 89,745 13 1.0
buffer busy waits 397,207 15.2 64,711 163 0.1
db file parallel write 1,309,440 .0 58,352 45 0.2
latch: shared pool 1,831,859 .0 48,522 26 0.3
latch: library cache 2,189,310 .0 39,560 18 0.3
enq: TX - row lock contentio 12,667 98.6 36,724 2899 0.0
enq: HW - contention 55,446 17.7 32,207 581 0.0
enq: US - contention 214,596 3.1 24,659 115 0.0
log file parallel write 7,545,685 .0 22,306 3 1.1
enq: SQ - contention 4,919 93.9 14,105 2867 0.0
db file scattered read 1,361,706 .0 9,195 7 0.2
buffer exterminate 9,652 64.0 6,941 719 0.0
write complete waits 5,440 90.2 5,047 928 0.0
read by other session 89,322 3.5 4,216 47 0.0
rdbms ipc reply 168,888 .9 4,105 24 0.0
enq: TX - index contention 7,073 13.8 3,608 510 0.0
latch free 55,322 .0 3,261 59 0.0
enq: RO - fast object reuse 654 81.2 1,661 2539 0.0
SQL*Net break/reset to clien 1,512,243 .0 1,012 1 0.2
local write wait 1,077 75.0 875 812 0.0
row cache lock 16,739 .2 701 42 0.0
SGA: allocation forcing comp 19,712 93.9 618 31 0.0
library cache lock 230 84.3 604 2627 0.0
control file parallel write 61,800 .0 567 9 0.0
enq: JD - contention 173 96.5 501 2895 0.0
os thread startup 921 3.8 471 512 0.0
log file switch completion 2,091 11.9 406 194 0.0
library cache pin 963 9.8 356 370 0.0
control file sequential read 2,350,536 .0 272 0 0.3
direct path read 409,139 .0 217 1 0.1
direct path write 84,372 .0 193 2 0.0
direct path write temp 12,458 .0 143 11 0.0
enq: FB - contention 80 48.8 127 1583 0.0
log buffer space 311 3.5 126 404 0.0
enq: TM - contention 41 92.7 112 2732 0.0
Log file init write 15,290 .0 102 7 0.0
db file parallel read 840 .0 94 112 0.0
enq: CF - contention 536 .0 83 154 0.0
enq: CI - contention 41 43.9 78 1903 0.0
undo segment tx slot 79 100.0 78 981 0.0
enq: TX - contention 239 8.8 75 314 0.0
SQL*Net message to client 32,544,794 .0 71 0 4.6
library cache load lock 192 3.6 58 303 0.0
undo segment extension 7,226,898 100.0 43 0 1.0
latch: cache buffers lru cha 77,855 .0 42 1 0.0
latch: row cache objects 3,222 .0 30 9 0.0
SQL*Net more data to client 259,125 .0 24 0 0.0
SQL*Net more data from clien 46,593 .0 20 0 0.0
LGWR wait for redo copy 485,129 .0 19 0 0.1
log file single write 890 .0 19 21 0.0
latch: cache buffers chains 26,623 .0 11 0 0.0
kksfbc child completion 124 96.8 6 52 0.0
reliable message 1,300 .0 5 4 0.0
Data file init write 442 .0 5 11 0.0
log file sequential read 880 .0 5 5 0.0
latch: enqueue hash chains 1,360 .0 2 1 0.0
latch: session allocation 572 .0 2 3 0.0
Wait Events DB/Inst: SXGSMDB/sxgsmdb Snaps: 3115-3139
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
cursor: mutex X 695,762 .0 2 0 0.1
latch: undo global data 966 .0 1 1 0.0
switch logfile command 9 .0 1 122 0.0
direct path read temp 11,497 .0 0 0 0.0
latch: library cache pin 218 .0 0 2 0.0
latch: object queue header o 275 .0 0 1 0.0
latch: redo allocation 239 .0 0 1 0.0
enq: PE - contention 1 .0 0 175 0.0
log file switch (private str 7 .0 0 20 0.0
latch: library cache lock 35 .0 0 4 0.0
wait list latch free 7 .0 0 18 0.0
latch: In memory undo latch 230 .0 0 0 0.0
enq: TX - allocate ITL entry 6 .0 0 16 0.0
db file single write 17 .0 0 5 0.0
latch: redo writing 128 .0 0 1 0.0
cursor: mutex S 18,612 .0 0 0 0.0
latch: messages 56 .0 0 1 0.0
enq: JS - queue lock 1 .0 0 21 0.0
buffer deadlock 993 100.0 0 0 0.0
latch: checkpoint queue latc 40 .0 0 0 0.0
SQL*Net message from client 32,544,833 .0 5,662,377 174 4.6
Streams AQ: qmn slave idle w 3,088 .0 84,336 27311 0.0
Streams AQ: qmn coordinator 6,236 50.6 84,335 13524 0.0
wait for unread message on b 86,776 98.0 84,142 970 0.0
Streams AQ: waiting for mess 17,224 97.7 83,022 4820 0.0
Streams AQ: waiting for time 1,482 46.3 76,269 51464 0.0
jobq slave wait 22,249 98.6 64,981 2921 0.0
SGA: MMAN sleep for componen 333,884 97.1 5,008 15 0.0
pipe get 2,522 16.3 1,962 778 0.0
class slave wait 60 100.0 293 4888 0.0
-------------------------------------------------------------
哪位高手给指点一下问题所在，是数据库的问题呢，还是机器硬件本身。详见附件。谢谢
awrrpt_1_3115_3139.txt
(2009-10-30 23:33:35, Size: 215 KB, Downloads: 12)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22529473/viewspace-660028/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/22529473/viewspace-660028/
1304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
