Re: Frequent log switches during sqlldr batch jobs

Ralph wrote:
> >
> > Some places are SAME, others are pretty variable. Some places are
> > cheap enough to require RAID-5, but will grudgingly give RAID1/0 to
> the
> > redos - remember, the redo's will be a fixed size, but the archived
> > redo's will be quite variable based on transaction rate and restore
> > requirements.
>
> Excuse me, doesnt Oracle archive the online logs after they get full?
> (unless 'ALTER SYSTEM SWITCH LOGFILE' which is not a common thing in
> day to day life of a database). If that is the case the archived
logs,
> which are a copy of redo logs, have to be the size of redo logs,
which
> in turn of a standard size. What would cause the archived logs to
vary
> in size based on transaction rate, restore requirements?

Think of it this way:

If you have 800M on a 1G device, how long would it take to blow up? With redo logs, never, since they are reused. With archived logs, cycling through the redo logs once... OK, I originally should have said, the total disk space used by archived redos is variable, and is much larger than redo. The restore requirements means, how long do you need to keep archived logs around for quick access.

As far as switch logfile, I use it all the time, and I'm pretty sure many places on 9 use the init parameter that does the same thing. (I only prefer the cron version because it allows me to avoid congesting the network when other things are likely to be.)

>
> >Also, I've seen some places give internal drives to
> > redo, just because they are too small to be used by anything else.
>
> While on the topic, I have read in books that it is best to give redo
> logs their own disk to reduce contention, especially in high
acitivity
> databases. Is it justified to buy a small disk, of say, few GBs
> exclusively for each of the redo logs? (We may need a few of them
> depending on the number of log files and members)

The books you have may be a bit old. The important thing is to understand that redo logs tend to be written serially, archiving is also a bursty serial writing, Oracle is smart enough to take advantage of redo mirroring that it controls when archiving, and undo may be the most used of the normal data files (my view is skewed towards online databases). So, look up the SAME and baarf papers (and discussions here and on oracle-l) if you have raid, understand controller contention may be more important than disk contention when push comes to failover, etc. etc.

It may well be justified if you are stuck with RAID-5 everywhere else. The write-buffer argument on those beasties varies with hardware and salesmanship.

(There is also some argument that unix file system performance will degrade over time as blocks are used and released, as in creating and deleting archived logs. But I haven't seen any convincing evidence that it will make any difference for archiving.)

jg

--
@home.com is bogus.
http://www.newratings.com/companies/headlines.asp?isin=US68389X1054