Re: Checkpoint not complete error
Date: Fri, 24 Apr 2009 16:37:34 -0400
Message-ID: <76b3d4e30904241337v169c2e0ft235f21fa4ad90e1d_at_mail.gmail.com>
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_WAITSTIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- -----------
----------- ------------
log file sync 55 10 .18 log file sync 149 31 .21 log file sync 102 30 .29 EVENT TOTAL_WAITSTIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- -----------
----------- ------------
log file sync 120 26 .22 log file sync 19 7 .38 log file sync 253 30 .12 EVENT TOTAL_WAITSTIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- -----------
----------- ------------
log file sync 26 8 .3 log file sync 133 16 .12 log file sync 26 3 .1 EVENT TOTAL_WAITSTIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- -----------
----------- ------------
log file sync 77 12 .15 log file sync 9875 901 .09 log file sync 7 1 .12 EVENT TOTAL_WAITSTIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- -----------
----------- ------------
log file sync 2 0 .13 log file sync 282 23 .08 log file sync 2 2 1.09 EVENT TOTAL_WAITSTIME_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_WAITSTIME_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_WAITSTIME_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?
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 24 2009 - 15:37:34 CDT