Re: Checkpoint not complete error
- From: Saad Khan <saad4u@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 24 Apr 2009 16:37:34 -0400
Ok gurus, I've resized the redologs to 500MB but the users are now
complaining that the app is dragging. In the alert logs I dont see any other
message neither I see logswitches occuring fastly.
When I ran the following sql, I get these results:
select
2 event,
3 total_waits,
4 time_waited,
5 average_wait
6 from
7 sys.v_$session_event
8 where
9 event = 'log file sync'
/
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file sync 55
10 .18
log file sync 149
31 .21
log file sync 102
30 .29
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file sync 120
26 .22
log file sync 19
7 .38
log file sync 253
30 .12
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file sync 26
8 .3
log file sync 133
16 .12
log file sync 26
3 .1
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file sync 77
12 .15
log file sync 9875
901 .09
log file sync 7
1 .12
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file sync 2
0 .13
log file sync 282
23 .08
log file sync 2
2 1.09
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file sync 65
33 .5
log file sync 17
4 .24
log file sync 6
2 .32
18 rows selected.
Similarly, following are the other results:
SQL> select
event,
total_waits,
time_waited,
average_wait
from
sys.v_$system_event
where
event like 'db file %' or
event = 'free buffer waits' or
event = 'write complete waits'
order by
time_waited desc
/
2 3 4 5 6 7 8 9 10 11 12 13 14
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
db file sequential read 236822
23909 .1
db file scattered read 61664
8335 .14
db file single write 391
21 .05
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
db file parallel read 16
6 .38
I went through a system check report and found the following comments in it:
Data Blocks - DBWR too slow
Segment header - Full tbl scan w/data load - reschedule
Undo Header - Not enough RBS - add
Undo Block - Increase pctfree of tables being modified
I'm not sure where to move from here. Can someone please help?
Other related posts:
