FAQ
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

Search Discussions

  • Jack van Zanen at Nov 21, 2006 at 7:30 pm
    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
  • Alex Gorbachev at Nov 22, 2006 at 5:03 am
    Windows gurus might be more helpful but it looks like terrible IO
    performance. Is it single CPU machine?
    Anyway, what do I know about Windows? :)
    On 11/21/06, Jack van Zanen wrote:
    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!!!
    --
    Best regards,
    Alex Gorbachev

    The Pythian Group
    Sr. Oracle DBA

    http://www.pythian.com/blogs/author/alex/
    http://blog.oracloid.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Dennis Williams at Nov 21, 2006 at 9:38 pm
    Jack,

    The first thing I would consider is disabling the constraints and using the
    direct path loader. Then after each load, run SQL statements to create a
    report of constraint violations. That might help your log switch problem and
    maybe your overall project time.

    Dennis Williams

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 20, '06 at 9:52p
activeNov 22, '06 at 5:03a
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase