RE: Redo log switches and waits database freezes
- From: "Jack van Zanen" <jack@xxxxxxxxxxxx>
- To: "'Luis Fernando Cerri'" <lfcerri@xxxxxxxxx>
- Date: Tue, 21 Nov 2006 20:30:49 +0100
Hi All
Problem is really bad system performance that I'll have the system people
take a look at. When database does checkpoint the whole system (not just
oracle)stops responding during that checkpoint. Not good!!!
I also had forgotten to put the rows parameter in the SQL*Loader file so it
was committing way too often.
Point 2 is taken care of, now point 1 left for fixing :-)
Brgds Jack
-----Oorspronkelijk bericht-----
Van: Luis Fernando Cerri [mailto:lfcerri@xxxxxxxxx]
Verzonden: Tuesday, November 21, 2006 7:18 PM
Aan: jack@xxxxxxxxxxxx
Onderwerp: Re: Redo log switches and waits database freezes
Hello, Jack.
I've already faced in the past a situation like the one you are
experiencing now.
One thing that could help you immediatly is to set the corrisponding
parameter (LOG_CHECKPOINT_INTERVAL, MTTR_FAST_START, sorry but the
parameter names may be wrong) to force the database to perform a
checkpoint each time something like 20% of the redo log file is
filled. If you do that, the checkpoint event will not have to write at
once all the dirty blocks since the last switch of redo logs.
Points that you need to investigate to eliminate the root cause of this
problem:
- speed up your checkpoint event by reducing your database buffer size;
AND/OR
- distributing your datafiles on separate disks;
AND/OR
- check for I/O contention on physical disks;
AND/OR
- few db writer processes? need to increase them?
AND/OR
- is async I/O properly set?
AND/OR
- maybe disable some indexes during the load may make less blocks
dirty and consequently cause less redo logs switches.
I think this can give you a starting point to solve this problem.
Please let me know how things are doing and, if you solve it, don't
you forget to share with us through the mail list how it was solved.
Best regards,
Cerri
from the sunny Brazil
2006/11/20, Jack van Zanen <jack@xxxxxxxxxxxx>:
>
>
>
> Hi All,
>
>
>
>
>
> OS: windows 2003 server
>
> Database : 10.2.0.2 STANDARD EDITION
>
>
>
> I have a database that is running in noarchivelog mode and the log files
are
> not multiplexed. I have only 2 drives available for oracle so I have put
my
> redo log files separate from datafiles
>
>
>
> This database is for conversion purposes and needs to be as fast as the
> hardware allows me.
>
> I am using SQL*loader to load files into this database after which I can
do
> transformations followed by and extraction to the final system.
>
>
>
> I need enabled constraints (mainly check) so direct load is out of the
> question I guess.
>
>
>
> Now when I load my data After a while all 4 250MB logfiles have status
> active and checkpointing freezes the database.
>
> But even if it just loads 1 file, leaving just 1 redo log file active and
1
> current, and I manually checkpoint the database this takes a long time
> during which the database is not responding.
>
>
>
> I have searched metalink and google but have not found anything usefull
> relating to this.
>
>
>
> I have some of the wait events relating to the logfiles listed here and
the
> top wait events for this loader session was actually log file sync
>
>
>
> WAIT_CLASS#|EVENT|TIME_WAITED|AVERAGE_WAIT
>
> 9|log file sequential read|15|0.37
>
> 9|log file single write|68|1.67
>
> 9|log file parallel write|897575|0.75
>
> 2|log file switch completion|742|33.74
>
> 5|log file sync|896616|0.75
>
>
>
>
>
> If you need more information please let me know specific which
>
>
>
> Brgds Jack
--
//www.freelists.org/webpage/oracle-l
Other related posts:
