Re: Reality check for filesystem/disk layout

Hi,

    Since it seems that your data can be loaded again easily via night batch load, why not consider noarchivelog mode?     SAME is better than your disk partition policy ,I think. You have limited number of disk, seperate your limited number of disks for dedicated redo/archive maybe is not a good idear.     And using outer part of the disk maybe become much more complicated when raid is in use.Do you know the underlying raid policy?     Another possible solution I will consider is put redo on mirrored disk(2 disks) and everything else on raid5. This max the daily read performance and does not affect the nightly loading.Archive log is not relavant to performance of read and loading,unless archive process is unable to catch the speed of redo generation.I won't put valuable disk resource to archivelog.     

Zhu Chao.     

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, September 27, 2003 7:19 AM

> Sure.
>
> Our only real problem on this box comes from our large batch loads,
> especially at the end of the month when we get huge amounts of redo (it
> usually takes about a week to finish loading the month's data - the only
> time batch processing doesn't finish overnight). To make matters worse as
> soon as some tables are loaded reports start being generated off them while
> other tables are still being loaded.
>
> Redo activity is pretty constant at that time with frequent log switches.
>
> Since as soon a redo log fills up
> a) the previous redo log is read
> b) an archive log is written
> separating out the archive logs and ever other redo log seems like the best
> way to minimize io contention for redo. Of course ideally they'd be on
> their own disks but that's not feasible.
>
> I'm playing around a little more by putting the temp filesystem separate
> from the redo logs just because I know the large reports are a sore point
> with our production department that runs the data loads and I think this
> will reduce the delays for end of month loads/reports.
>
> Since the outer part of the disk is fastest I put the stuff that's acessed
> most often there (a trick I learned from a consultant SA we had a few years
> ago who was the most database/oracle knowledgeable unix SA I've ever met - I
> really regretted it when the company went through a cost savings period and
> cancelled his contract).
>
>
>
> Jay Miller
> Sr. Oracle DBA
> x68355
>
>
> -----Original Message-----
> Sent: Friday, September 26, 2003 5:20 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Jay,
>
> I'd like to see (for my enlightenment) a brief rationale for your decisions,
> if you have time. Thanks!
>
> --- JayMiller_at_tdwaterhouse.com wrote:
> > We have the luxury of moving a 300G database to a new box that's being
> > built and choosing the specifications, disk layout, striping, etc.
> > After spending
> > the morning poring over Cary Millsap's wonderful VLDB paper this is
> > what
> > we're thinking of but I'd appreciate any comments.
> >
> > One of my main goals going in was separating redo logs into 2 sets of
> > disks and archive logs on a third.
> >
> > We have 16 disks to play with and seem to be winning the 1+0 battle
> > against some SAs who don't understand why we wouldn't want to use
> > RAID5.
> >
> > The database has minimal write activity during the day (other than
> > sorts to the temp tablespace) but huge batch write activity at night
> > and especially
> > at the end of the month (the data load time is enough of a problem
> > that the
> > few partitioned tables we can easily reload are doing unrecoverable
> > loads).
> > There is a lot of read activity during the day, both single row
> > queries from
> > front ends that are rolled out to several thousand people and reports
> > that
> > can do some large sort/merge joins.
> >
> > Here's what we were thinking:
> >
> > 1st Disk Set - 4 72M disks RAID 1+0
> >
> > 1st and 3rd redo log on outside
> > Misc. Datafiles in middle
> > Misc scripts and files used by other departments in center
> >
> > 2nd Disk Set - 6 72M disks RAID 1+0
> > Archive logs on outside
> > Temp tablespace and misc. datafiles in middle
> > Text files used for loading in center
> >
> > 3rd Disk Set - 6 72M disks RAID 1+0
> > 2nd and 4th redo logs on outside
> > Rollback tablespace and misc datafiles in middle
> > /oracle (executables and some scripts) in center
> >
> >
> > I was debating if there was any advantage in varying stripe sizes
> > across the different disk sets (since I know Cary says redo logs like
> > fine grained
> > stripe sizes) but given the mix of uses for each that doesn't seem
> > viable.
> >
> >
> > Comments, suggestions or even productive questioning of my sanity
> > would be appreciated.
> >
> >
> > Thanks,
> > Jay Miller
> >
> >
>
>
> =====
> Paul Baumgartel
> Transcentive, Inc.
> www.transcentive.com
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Shopping - with improved product search
> http://shopping.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Paul Baumgartel
> INET: treegarden_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
> message BODY, include a line containing: UNSUB ORACLE-L (or the name of
> mailing list you want to be removed from). You may also send the HELP
> command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: JayMiller_at_tdwaterhouse.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: zhu chao
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).