Re: Tuning, RAID5, and fragmentation

dana mn wrote:

> Presuming a DBA is forced to use RAID5, what elements of tuning become
> irrelevant? (in the sense that if you're stuck with RAID5, warts and
> all, then trying to tune X, Y, and Z would be a waste of time /
> ineffective).
>
> Load balancing files would be one thing.. no way to put indexes and
> tables on different disks (ditto redo log file members, etc) with one
> massive RAID5 volume.
>
> What about fragmentation and coalescing? Are these still a concern for
> tablespaces located on RAID5 volumes?
>
> Has anyone written an article about Oracle and "living with RAID5"? I'm
> finding that a customer has several Oracle databases on systems with
> nothing else but RAID5 storage for everything.
>
> Thanks very much.
>
> - Dana
>
> __________________________________________________
> Do You Yahoo!?
> Get email at your own domain with Yahoo! Mail.
> http://personal.mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: dana mn
> INET: danamn2000_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).

Personally, I like the fail safe that RAID5 provides, however, it is nice if asked, to have a mixed RAID, i.e., 5 and 10. 10 for indexs.

Fragmentation is generally more a function of segment extensions, that is, the more you have to extend the more likely you will fragment. If you can, plan your tablespaces so table with similar growth expectations are placed together. Ideally, the initial and next extensions should be identical. Avoid setting pctincrease to anything but zero to keep extents the same within a table space. Non contiguous extents will occur, however the hope is the extent space will be contiguous. Set the tablespace default settings appropriate for the tables they contain. Coalesce tablespaces manually rather than configuring SMON to do it, as that process requires pctincrease to be greater than zero.

Tablespace fragmentation may not be perceptable, however, segment fragmentation can be depending on the activity of the table. I personally like to set minextents equal to the number of datafiles I have, and I have note less scattering of free space blocks. I can only assume then that contiguous space is being allocated. 8x offering partioned indexes and tables helps as well, and depending on the availability requirements of the instance partitioned tables may be exported and imported to really clean up an area if there was a miss on the settings for the inital sizing.

I am assuming you have a number of RAID volumes and can apply archive and redo with tables that are not as active as others, which will help further distribute I/0 over the volumes.

I like RAID5 since it has saved my neck on more than on occasion, however, RAID 10 (1 & 0) is very nice if you need the speed for indexes and can afford to loose and reconstruct an index if necessary.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Dave Weber
  INET: dlw_at_lightstream.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).