[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference eps::oracle

Title:Oracle
Notice:For product status see topics: UNIX 1008, OpenVMS 1009, NT 1010
Moderator:EPS::VANDENHEUVEL
Created:Fri Aug 10 1990
Last Modified:Fri Jun 06 1997
Last Successful Update:Fri Jun 06 1997
Number of topics:1574
Total number of notes:4428

1569.0. "Why is there frequent REDO LOG reads?" by TBC001::WONG () Wed May 28 1997 15:40

    Is there any reason why Oracle will read the redo logs very frequently?
    
    I have the redo logs put on a separate LSM volume and monitored the I/O
    statistics with volstat.  For every two writes, there is one read on
    the volume.  Is this normal?
    
    Joe
T.RTitleUserPersonal
Name
DateLines
1569.1Thats the way it isNNTPD::"srinath@qcav01.enet.dec.com"qcav01::srinathThu May 29 1997 06:2617
Joe,

The Oracle Redo logs are used by Oracle for database integrity.
So since there is lots of writes (i presume that they are updates),
there is a change in the state of the datafiles.
This is logged into the Redo logs for say,possible future database recovery
and for its own consistency checks.

So the access to the redo is expected.Perhaps you should try putting it
on a seperate spindle (DO NOT DISTRIBUTE ACROSS SPINDLES), and tune your
log_buffer and other log_ values in your init.ora.

Hope this justifies...

73's
/srinath
[Posted by WWW Notes gateway]
1569.2What are the many READ's for?TBC001::WONGThu May 29 1997 13:544
    ref .1
    
    I expected lots of writes and some reads.  What I didn't expect was one
    read for every two writes.  Any suggestions?
1569.3More inputs NNTPD::"srinath@qca.dec.com"qcav01::srinathFri May 30 1997 05:4511
>I expected lots of writes and some reads.  What I didn't expect was one
>read for every two writes.  Any suggestions?

Perhaps what you see as unexplained is bcos of your application ?.
Can you provide more info on the activities on the system (database
perspective)

Anybody else to throw light on this ?.

73's
[Posted by WWW Notes gateway]
1569.4Application was ManManXTBC001::WONGFri May 30 1997 13:218
    ref .3
    
    The application did heavy updates.  So I expected lots of write by
    Oracle to the log files.  I don't know why Oracle will read the log
    files so frequently.  It was consistently doing one read for every two
    writes.  The reads were physical reads as reported by VOLSTAT.  I was
    running Oracle 7.2.3 on Digital UNIX 3.2G.  Application used was
    ManManX.
1569.5rollback entries get loggedAXPBIZ::RANJANFri May 30 1997 18:194
If I remember right, even rollback segments are written in redo logs (as
they are a part of the database). If there is heavy update activity with
some amount of rolling back, there is a chance of reads from redo logs. 
But, as you mentioned, 1 read versus 2 writes looks a little high.
1569.6couple of commentsALFAM7::GOSEJACOBMon Jun 02 1997 15:3534
    .0 came close to having my Oracle view of the world being shattered
    into fine grain pieces. I don't have a clue on what's happening on your
    box but redo logs are sequentially written and only read upon recovery
    of the database (after a chrash of the Oracle kernel that is). So on a
    database in 'normal' operational mode you should not see any read
    activity on the current redo log file.
    
    Having said this ...
    
    There is a chance that during the reboot of the Oracle kernel after a
    database crash the database will become available after all
    rollforward transactions have been applied by the recovery process and
    some rollback information is still to be read from the redo logs. But
    this should happen for a very limited time after the Oracle reboot.
    
    Now the real question: are you running the database in "archive log
    mode" (init.ora parameter log_archive_start = true)? Whenever a log
    switch happens in this case the archive process will start reading the
    filled up redo log while in parallel the log writer process will
    write to the next redo log in sequence.
    
    Apart from this I can't think of any part of the Oracle kernel reading
    from redo logs.
    
    re .5
    Rollback segments are used by Oracle to provide read consistency. They
    store the before image of datablocks currently changed by not yet
    commited transactions. Like datablocks from ordinary tablespaces they
    may be flushed to disk by the database writer. The blocks are flushed
    to the tablespace the rollback segments have been created in. I may be
    missing something but as far as I know rollback I/O activity will not
    cause any writes to the redo logs.
    
    	Martin 
1569.7Re: .6AMCUCS::KKDASLet's Train The TrainersMon Jun 02 1997 19:0820
>    There is a chance that during the reboot of the Oracle kernel after a
>    database crash the database will become available after all
>    rollforward transactions have been applied by the recovery process and
>    some rollback information is still to be read from the redo logs. But
>    this should happen for a very limited time after the Oracle reboot.

I doubt that an instance recovery requires redo log data. It should ideally be
rolling back all uncommitted transactions and should not be doing any roll
forward activities under any circumstances.

A redo log is only read by ARCH process and database recovery process. That too
the ARCH process archives only the inactive redolog files and thedatabase 
recovery is a stand alone activity. That leaves us with no reading of active
redo log at all ! Unless somebody enlighten us with other specifics.

Therefore, .0 claim is still unexplained. Are the LSM volumes mirrored ? Was
there any ongoing synchronization? Is there any other part of database on those
volumes? etc. etc.

/KK/
1569.8\EPS::VANDENHEUVELHeinTue Jun 03 1997 03:5713
    Since this is on an LSM device anyway, the author can perhaps humor
    us and post (a pointer to) an IO trace (voltrace) to show the block
    numbers for reads & write and theIO sizes used. That might give us
    a clue. Consistent reads would sound like the most important reason.
    
    Hmm, would Oracle try to fill up redo log pages and with too few DB
    buffers have to re-read partially used pages to be able to add in
    fresh data? Is there an administration page it has to go back to 
    all the time?
    
    	Hein.