Hi,
Recently, I found that my Oracle database (8.1.7.4) on Windows NT service
pack 6
response time has increased significantly. I did a check and found the
buffer chache hit ratio
was down to less than 0.20. However, the ratio does not improve even when I
increase the
db_buffer from 17MB to 38MB. The ratio still drop to 0.20 after 8 hrs of
operation.
A check on the system events and I can't find anything unusual.
Below is the system event statistics:
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT STARTUP_T
------------------------------ ------------ -------------- ------------ ----
-------- ---------
library cache pin 2 0 0
0 19-APR-06
process startup 2 0 1
1 19-APR-06
refresh controlfile command 1 0 1
1 19-APR-06
instance state change 1 0 1
1 19-APR-06
log file single write 124 0 2
0 19-APR-06
SQL*Net more data to client 608 0 11
0 19-APR-06
rdbms ipc reply 6 0 22
4 19-APR-06
direct path write 2,200 0 47
0 19-APR-06
db file parallel write 5,872 0 65
0 19-APR-06
file identify 321 0 121
0 19-APR-06
latch free 406 404 132
0 19-APR-06
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT STARTUP_T
------------------------------ ------------ -------------- ------------ ----
-------- ---------
control file parallel write 12,451 0 196
0 19-APR-06
reliable message 1 0 219
219 19-APR-06
log file switch completion 61 0 266
4 19-APR-06
log file sequential read 1,084 0 329
0 19-APR-06
SQL*Net message to client 437,807 0 346
0 19-APR-06
direct path read 3,598 0 392
0 19-APR-06
Null event 1 1 411
411 19-APR-06
control file sequential read 2,001 0 475
0 19-APR-06
log file parallel write 24,950 0 521
0 19-APR-06
file open 3,695 0 590
0 19-APR-06
buffer busy waits 2,522 0 1,051
0 19-APR-06
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT STARTUP_T
------------------------------ ------------ -------------- ------------ ----
-------- ---------
log file sync 49,504 0 4,396
0 19-APR-06
db file sequential read 113,492 0 19,134
0 19-APR-06
db file scattered read 3,980,849 0 1,017,991
0 19-APR-06
virtual circuit status 564,611 73 3,305,664
6 19-APR-06
smon timer 121 115 3,559,391
29,416 19-APR-06
pmon timer 11,889 11,602 3,559,911
299 19-APR-06
SQL*Net message from client 437,231 0 3,613,881
8 19-APR-06
dispatcher timer 1,299,503 608,021 7,078,791
5 19-APR-06
rdbms ipc message 84,707 34,943 17,830,490
210 19-APR-06
Any help is appreciated. Thank you.
Received on Wed Apr 19 2006 - 11:07:57 CDT
