| Tablespace sizing [message #137326] |
Wed, 14 September 2005 12:17  |
ETL_DEV
Messages: 1
Registered: September 2005
|
Junior Member |
|
|
Allow me to apologize for asking a vague question
I need some information on sizing tablespaces. I remember having done some math when I was preparing for my OCP 3 years ago. I'm on the client side and the DWH is in bad shape with performance.
Client is running: Oracle 10 g on Sun Solaris machine
They are looking for a basic configuaration setup until they hire a full time DBA. This is more of a test environment. I have to give a presentation tomorrow. If anybody has any kind of a calculation spreadhsheet or resources please guide me.
Thanks in advance
ETL_DEV
|
|
|
|
| Re: Tablespace sizing [message #137960 is a reply to message #137326] |
Mon, 19 September 2005 02:41  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
Few Basic guideline for for tablespace.
Use multiple tablespaces
========================
Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:
[1] Separate user data from data dictionary data to reduce I/O contention.
[2] Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
[3] Store different the datafiles of different tablespaces on different disk drives to reduce I/O contention.
[4] Take individual tablespaces offline while others remain online, providing better overall availability.
[5] Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
[6] Back up individual tablespaces.
Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.
Specify tablespaces default Storage Parameter
=============================================
When you create a new dictionary-managed tablespace you can specify default storage parameter for object that will be created in the tablespace. storage parameter specified when an object is created override the default storage parameter of the tablespace containing object.
Note:- Size of tablespace is decided by the datafile attached with it.
Example:- Tablespace user01 can have
[1] 10 MB of datafile name aaa
[2] 100 MB of datafile name bbb
[3] 90 MB of datafile name ccc
so over all size of tablespace is 10+100+90=200MB
Make Minimum of 50MB datafile which should be autoextensible. But size of datafile should be decided on the type of data in it if you know that there will be fixed row or record in it & which is not going to increase then make datafile size by calculating the size fo record.
I hope above explanation will help you
Regards
Always Friend Sunilkumar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
