Re: Tablespaces sizing!

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 17 May 2008 19:55:04 -0700 (PDT)
Message-ID: <18e28b85-d382-49af-9439-ae438f90fc7f@j22g2000hsf.googlegroups.com>


On May 17, 5:28 pm, Pat <pat.ca..._at_service-now.com> wrote:
> On May 17, 2:02 pm, scorpio1..._at_yahoo.com wrote:
>
>
>
>
>
> > Hello all.
> > I have a question regarding the tablespace size. Assume that I create
> > the new database and now I wanted to create the Application for some
> > company that has a feature like this:
>
> > Company: WCA Wireless Net
> > Numbers of orders per year: 44 million
> > Average number of equipment sales per phone: 2.5
> > Number of phone plans: 13
> > Number of phones: 100
>
> > And I must create tables like:
>
> > Data Model:
> > phone table: phone_id (PK), phone_name, model_no, phone_price
> > plan table: plan_id (PK), plan_name, plan_price
> > plan_state_table: plan_id (FK), state_id (FK)
> > state table: state_id (PK), state_description
> > equipment table:  equip_id (PK), equip_name, phone_id (FK),
> > equip_price
> > order table: order_id (PK), plan_id (FK), phone_id (FK), equip_total,
> > order_total
> > order equip table: order_id, equip_id
>
> > and also I can say that:
>
> > Data sample for phone table and example for analyzing row size/extent
> > size:
> > Insert into phone values (phone_id_seq.nextval, 'Motorola
> > LX','M4569BL',99.89);
> > analyze table phone compute statistics;
> > select table_name, avg_row_len from user_tables where table_name =
> > 'PHONE';
> > avg_row_len = 29 bytes
> > so I can understand that This application expects to store approx. 100
> > different phones at a time: 29 bytes * 100: ~3K worth of data for the
> > phone table. And we can say that extent sizes could be 64K (very
> > small) to store the data in this table.
>
> > I wanted to create 2 separate tablespaces for tables and for indexes.
>
> > The question is what is the best option for the sizes of these
> > tablespaces? And why?
> > How can understand that if I wanted to create the different
> > application or managing the size of  the tablespaces for the different
> > accept.
>
> >  For example for the application above I create the tablespaces with
> > these options:
>
> >   SQL> create tablespace tables
> >   2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORCL2\tables01.dbf'
> >   3  size 100m autoextend on maxsize 200m
> >   4  extent management local uniform size 100k;
>
> > Tablespace created.
>
> > SQL> create tablespace indexes
> >   2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORCL2\indexes01.dbf'
> >   3  size 100m autoextend on maxsize 200m
> >   4  extent management local uniform size 100k;
>
> > Tablespace created.
>
> > I know this is the just practice; I created as a part of my personal
> > server at home but how about the real word? What is going to be the
> > best options and why?
>
> > Thank you all
>
> Generally, the main advantage to putting indexes and data in different
> tablespaces is so that you can put them on different sets of spindles.
> If you have, for example, a box with two drives, you might put the
> indexes on one drive and the data on another (physical) drive, thus
> spreading out your workload across multiple spindles and allowing you
> to do more IOs.
>
> If you've just got one big drive array though (say a raid 5 or 10
> array mounted), then having separate tablespaces doesn't give you a
> whole lot of performance benefits since you're still spinning the same
> disks. Same thing probably follows if you're on a SAN, but that
> depends a lot on how the back end is configured so I'd hate to offer a
> generalization.
>
> Same thing with sizing; unless you can take advatage of multiple
> physical devices, there's not (that I'm aware of) much of a benefit to
> using, say, 3 small tablespaces as opposed to one big one.- Hide quoted text -
>
> - Show quoted text -

The only reason to separate tables and indexes into different tablespaces is for ease of managment by the DBA. There is no performance benefit from putting the indexes for tables into separate tablespaces that is not equally available to just separating one set of tables and indexes onto separate disk devices from another set of tables and indexes. If you entire disk farm is one 5 physical disk array organized as RAID-5 then there is no performance benefit from separating the objects into different files because logically all the files are on the same disk.

About the only place you might actually be able to place files on specific physical disks is in the PC server world where only a couple of disks are hooked to the PC. Where I work the LAN Administrators have made the PC disks (other than the C drive) from stripes cut from a network storage unit.

Make sure you understand your disk layout before spending time separting objects into different files for performance purposes.

HTH -- Mark D Powell -- Received on Sat May 17 2008 - 21:55:04 CDT

Original text of this message