Re: Tablespaces sizing!

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. Received on Sat May 17 2008 - 16:28:48 CDT