Re: separate tablespaces for large tables?

Hi David,

The organization of tablespaces is a mix of science, superstition, and personal preference. My personal preferences would lead me to have one tablespace for the small tables, one for the medium sized tables, and one for each of the large tables. I would next create another set of tablespaces for the indexes.

I would make all of the extents in the small table tablespace the same size and all of the extents in the medium table tablespace the same size the same applies to the indexes. The scientific basis for this is based on the idea of minimizing/eliminating fragmentation and "holes" in the middle of your tablespace.

Now, as to your RAID 5. In my opinion the best thing to do with a RAID 5 array is carry it out of your computer room to the nearest bridge and drop it over the side. The next best thing is use it for any RDBMS that you want to get rid of since performance will be so lousy that management won't even flinch when asked to write a check for tens (even hundreds) of thousands to switch to RAID 0+1 (or is it 1+0 -- I can never remember which it is that you really want). Okay, having been burned I am very prejudiced against RAID 5. You can get excellent performance out of RAID 5 given the following conditions:

1. Separate disk for your redo logs
2. Separate disk for your archive logs
3. Separate disk for your rollback segments
4. Separate disk for your temporary tablespace
5. Substantial cache in your controller(s)
6. Multiple disk strings and/or controllers for the RAID
7. A lot of spindles, ideally 9, in each string

The problem is that most RAID 5 configurations are designed to minimize cost and let performance fall where it will. I can attest to the fact that if your RAID 5 box is configured to cut costs performance will drop off the bottom end -- your performance can drop to the point where a bookkeeper with a green eye shade and a quill pen will be faster than a 4-way 64-bit system with a gig of RAM. The problem is not the write performance of RAID 5, which can range from abysmal to abominable, but to the contention for what equates to all of your disk activity being funneled through a single point.

regards

Jerry Gitomer

David Spaisman wrote in message <36EF3B85.E16EB063_at_intercall.com>...
>I have question as I have to size (and I would like to do so correctly)
>tables in a new database coming up for production soon.
>
>Of 96 tables, there are 18 tables to be update frequently, with 7 being
>tables with 1000's rows, 8 being 100,000s rows and 3 with 1,000,000
>rows. So I need to guestimate what pctfree to use in the table DDL.
>
>This is a purchased application so we are guessing on how thw tables
>will be used.
>
>But my question is should I use 1 tablespace for each of the three large
>tables? And should I have separate tablespace for each of the 8 midsize
>tables or use 1? How do you usually work with this?
>
>If these tablespaces are on RAID 5 drives, does this still apply?
>
>I am trying to minimize my subsequent maintenance woes. I will probably
>use a multiple of the multiblock read count parm in the init file based
>upon the avg rows size for determining my extent size(subsequent and
>initial)?
>
>What are thoughts about tablespaces?
>
>Thanks.
>
>David Spaisman
>
Received on Wed Mar 17 1999 - 00:49:17 CST