 | |
Tablespaces sizing!
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Tablespaces sizing! -
05-17-2008
, 04:13 PM
#2
| |||
| |||
|
|
I wanted to create 2 separate tablespaces for tables and for indexes. |
Quote:
|
What is going to be the best options and why? |
#3
| |||
| |||
|
|
I wanted to create 2 separate tablespaces for tables and for indexes. |
Quote:
|
What is going to be the best options and why? |
#4
| |||
| |||
|
|
I wanted to create 2 separate tablespaces for tables and for indexes. |
Quote:
|
What is going to be the best options and why? |
#5
| |||
| |||
|
|
I wanted to create 2 separate tablespaces for tables and for indexes. |
Quote:
|
What is going to be the best options and why? |
#6
| |||
| |||
|
|
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 |
Table and Index costing procedures can be used to determine the size by
segment.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#7
| |||
| |||
|
|
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 |
Table and Index costing procedures can be used to determine the size by
segment.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#8
| |||
| |||
|
|
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 |
Table and Index costing procedures can be used to determine the size by
segment.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#9
| |||
| |||
|
|
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 |
Table and Index costing procedures can be used to determine the size by
segment.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.