Re: Database Parameters and Redo Log Contention

In a way these sizings don't really matter. Redo log operations and recovery are (mostly) limited by I/O (how fast can I read and write to my disks). So placement of files is very important.

The parameter settings in Oracle control how often you do checkpoints and how much I/O is done per checkpoint.

In your situation:
- log_buffer is big enough (even too large, just wasting memory) - you are checkpointing every 10000 redo blocks written (a block is 512 bytes). So in your case you will have 2 to 3 checkpoints per redo log file. Plus there will be a checkpoint every time you swith log files (4-6 times in your case)
- during a checkpoint we need to write the buffers to disk as part of the normal write batch. Oracle will only write one write batch at the time (it has to wait for all buffers to be written in order to start the next write batch).
- db_block_checkpoint_batch will default to 8 and the write batch will default to (db_files * db_file_simultaneous_writes)/2. So if the write batch is large and the checkpoint batch small, checkpoints will last a long time
- if DBWR is not keeping up with the normal workload or writing batches that are too big, checkpoints will suffer as during checkpoints more blocks/buffers will have to be written.

There is more ofcourse, if you are interested drop me an email akolk_at_gelrevision.nl

Anjo.
Gary Corless <gary.corless_at_gen.friendsfirst.ie> wrote in message news:7cqre0$ls5$1_at_news1.news.iol.ie...
> I work in a small IT dept and picked Oracle as the database for our new
> system. The problem is it's hard to know what sizes things should be (the
> default sizes seem very small). I've got 53 Redo Log Contentions within
the
> first 2 weeks of the pilot project. The application is not yet critical
so
> db recovery currently set for 3 days.
>
> Question 1 : Are the SGA sizes to big/small?
>
> Question 2 : Would increasing or decreasing the redo log sizes ,
> log_checkpoint_interval or log_checkpoint_timeout reduce the Log
Contention?
>
> Spec :
> Oracle 7.3.3 (Migrating to 8 at the end of the year),
> NT(sp3),
> Dual P2 450,
> 512 MB Ram,
> 8G Data
> have ~5000 transactions creating logs at 50M-70M/day (idle for 12 hours)
> db_block_size = 4096
> redo log size = 12,880K (8 groups)
> log_checkpoint_interval = 10000
> log_checkpoint_timeout = 0
>
> -----------------------SGA-----------------------
> Total System Global Area 385,147K
> Fixed Size 35K
> Variable Size 162,698K
> Database Buffers 216,268K
> Redo Buffers 6,144K
>
> Thanks for the Help,
>
> Gary
>
>
Received on Mon Mar 22 1999 - 10:45:21 CST