Re: Online redo log tuning

A copy of this was sent to "Dave Waterworth" <pscdaw_at_ihug.com.au> (if that email address didn't require changing) On Tue, 6 Jul 1999 23:19:44 +1000, you wrote:

>Hi,
>
>I'm currently tuning a database that is to be used as a emergency backup if
>a main server fails. It's running on a 256Meg server with only one disk.
>I've got a few question re the online redo logs and how there config affects
>tuning.
>
>One session periodically loads a large amount of data into the system
>(deleting 50000 records, then replacing with 50000 new records, over about
>10 tables). I need to make sure this transaction doesn't peg the system
>(there are only a couple of other sessions active but they may be querying
>the same tables that are being deleted.
>
>I've set up the SGA to be 100Meg with 30Meg Redo Buffer which gives me
>pretty acceptible performance but the disk is still very busy during the
>insert. Using diskmon I see that the online redo logs are the culprits, I do
>not care about recovert at all.
>
>1. I don't need to archive the redo logs, will increasing there size and
>hence reduce the frequency of log switches have much effect on performance
>or are log switches fairly cheap?
>

increasing their size will definitely help. each log switch fires a checkpoint which flushes your buffers. having larger logs will defer this. If you have logs big enough to get through the whole 50,000 record process without firing a checkpoint, we can do the checkpoint later when the system is less busy.

>2. With only 2 1M redo log files, is there a chance that the check pointing
>of log 1 is still in process when log is full, or is the check point process
>pretty quick? Does checkpointing block a log switch? Does it matter if a
>single transaction fills all of the online logs then starts to overwrite the
>initial file again?
>

absolutely there is a very good chance. checkpoints are done with lots of scattered writes to datafiles. they can take a while.

it does not matter if a single transaction fils all of the online logs and starts to overwrite them -- its designed to do that.

>3. The log_checkpoint_interval is set to 10000 which I thinks means that I'm
>checkpointing far more frequenctly than necessary. How do I disable this, do
>I delete the entry in init.ora, set it to zero or a larger value?
>

don't delete it, set it high.

log checkpoint interval

        The number of newly filled redo log file blocks needed to trigger a 
        checkpoint. Regardless of this value, a checkpoint always occurs when 
        switching from one online redo log file to another. If the value 
        exceeds the actual redo log file size, checkpoints occur only when 
        switching logs. 

Assume your redo log blocks (os blocks) are 512bytes. Then a setting of 10,000 is approx 4.8meg. Unless your logs are bigger then 4.8 meg, this setting is high enough.

>4. What exactly does the message checkpoint not complete mean and how do I
>eliminate it
>

it means we are attempting to reuse a logfile and the checkpoint needed to flush data protected by that redo log file has not completed. we must finish that checkpoint before we can reuse that log. the system basically 'stops' and finishes that checkpoint as fast as possible. it then starts again.

you can get rid of them by adding more log files or increasing the size of your log files.

In your case, I would start with at least 2-25meg log files, at least.

>5. I notice that the starter database has two redo log groups, I assume that
>both are being written too simultaneously which makes no sense on a single
>CPU single disk machine.
>

2 groups are needed (we need a minimum of 2 groups -- 1 active and 1 not active).

If you have more then 1 member per group, we multiplex the writes. On a single disk system, not a necessity (your taking a big risk as it is).

Multiplexing writes on a single cpu system makes great sense:

• unless you are using disks that use the system cpu (eg: IDE drives), the disks have their own 'cpus'
• multiplexing redo log files is the only way to protect against losing them -- but you need more then 1 disk.

>Thanks in advance
>
>Dave Waterworth
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 06 1999 - 08:40:06 CDT