Re: Oracle performance & bottle necks (redo,rollback and datafiles)

I don't have a specific problem so I can't give any specific details. It's just that when I've recommended configurations I'm always unsure of the best place to put redo logs or rollback segments (for performance not security). A project I worked on a while ago had a batch job that needed to load data in half an hour. To cut a long story short the DBA had only stripped the datafiles over 3 disks. We upped it to 10 and the job went twice as fast. This started me thinking that although the datafiles can be scaled up (i.e. parallelism using Oracles parallelism, or just running multiple batch jobs in conjunction with disk stripping) this scaling could not be applied to the redo logs if they became the bottle neck ( or can it?).

This may not be a problem for general workloads because a lot of the IO would be read and so the redo logs would have a much lower activity than the datafiles. The worse case scenario I can think of is a bulk load with little database checking (i.e. reads). In this case I believe the amount of IO written to the redo logs may actually exceed the IO written to the datafiles mainly due to the fact that if rollback segment information is written to redo logs this doubles the amount of throughput. In a quick test when inserting a 2K row to a table the redo log grew by 6K.

It may be helpful if there are any guidelines regarding the ration between writes to datafiles:redo for different types of workloads. For instance if the ratio is 6 to 1 for a given workload then the datafiles can be scaled six time before we need to think about the redo logs.

The main problem with setting up a configuration is that your often working in the dark. The information you have is often inadequate /useless or just a finger in the air job. This is not necessary the fault of the analyst/ programmers/designers but the fact the base information we need to work with is not known or just guesses. Once live it's much easier but it maybe too late.

I'm therefore trying to come up with a simple scalable configuration that allows managers to sleep easily in there beds. I just have this uneasy feeling about the redo logs. I don't know at what point they will become a problem and if they do I don't know how to scale them. The rollbacks segments may also give me a problem. i.e. Should they go on the same stripped devices as the datafiles or should they have there own device. Should this device be stripped to match the datafiles? Is a stripped rollback segment any use?

"Michael E. Austin" wrote:

> Can you be more specific about the paltform you are using.
> What type/how many disk controllers and number of spindles.
> How about looking at the parameters that are specific to REDO log files,
> what kind of stats are you seeing that points you to a problem with
> REDO?
>
> Michael Austin
> Michael Austin, Inc.
> An Independent Database Consultant.
> austin_at_inmind.com
>
> Mike Burden wrote:
> >
> > If one is trying to get oracle to perform at its best where should redo
> > logs, rollback segments and datafiles be allocated to.
> >
> > For example assume it is critical that a mass load must completed within
> > a given time frame.
> >
> > The box has multiple processors (to take advantage of parallelism) and
> > uses stripping to get flat out performance for the datafiles. But my
> > question is: won't this just shift the bottle next to the redo logs
> > (probably not the rollback segments). If the redo logs have twice the
> > amount of data being written to them (i.e. datafiles and rollback
> > segments) then on a sequential load this will surely be the bottle neck.
> > Writing to the redo logs can't be done in parallel, can it? Putting the
> > redo log files on to a stripped disk may help assuming archiving is
> > switch on (hmmm...I'll think about that one).
> >
> > Could be really radical and backup the database first then assign the
> > redo logs (and rollback segments) to a null device. If oracle reads from
> > these files then a RAM drive is an alternative.
> >
> > The same applies to random updates but in this case the datafile
> > processing is slowed down somewhat because of reads, index access etc.
> >
> > Are there recommendations which state how fast redo logs disks or
> > rollback disk should perform when compared to the datafile disks.
Received on Tue Feb 02 1999 - 03:52:25 CST