Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Redo log switches and waits database freezes

RE: Redo log switches and waits database freezes

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Tue, 21 Nov 2006 20:30:49 +0100
Message-Id: <20061121193054.YHGW2441.amsfep13-int.chello.nl@marvin>


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_at_gmail.com] Verzonden: Tuesday, November 21, 2006 7:18 PM Aan: jack_at_vanzanen.com
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_at_vanzanen.com>:
>
>
>
> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 21 2006 - 13:30:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US