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
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 :
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
