| Number of Datafiles in a Tablespace [message #193275] |
Fri, 15 September 2006 09:38  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
Is there recomended number of number of datafiles to be present in a tablespace,
I already have 9 datafiles in a single tables holding indexes, each datafile is of 10000 (mb) and it's a growing APP so need to add few more datafiles just wanted to know is there any recomended numbers to follow from Performance point of View,
Tablespace is LMT, Oracle version 9i(rel 2 ).
We do regular deletes weekly and insertions are daily basically it is an OLTP,
Another thing is i noticed for Index tablespace for the column Fragmented Index for some datafiles it shows value as 0 and for 1 shows as 75.7 and for 1 it shows 100. what is the significance of it, do we need to take care of some thing, last month we rebuild 5 index after we changed some structure of table.
Thanks
|
|
|
|
| Re: Number of Datafiles in a Tablespace [message #193280 is a reply to message #193275] |
Fri, 15 September 2006 09:44  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
It depends on your adminstrative need. No hard limit.
many datafiles striped across multiple physical devices may help with i/o.
In extreme cases, make sure you are not running out of maxdatafiles parameter set.
>>Another thing is i noticed for Index tablespace for the column Fragmented Index for .....
I cannot understand.
Where are you seeing these??
Are you talking about fragmented indexes?
Make sure you are using LMT with uniform extent size. That should fix it (well, almost. not exactly)
[Updated on: Fri, 15 September 2006 13:55] Report message to a moderator |
|
|
|
| Re: Number of Datafiles in a Tablespace [message #193286 is a reply to message #193280] |
Fri, 15 September 2006 10:04 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Mahesh
I saw that column in Toad , when using tablespaces Tab,
Yes we have LMT with uniform Extent Size.
Another doubt is Monitoring Index is an extra overhead on system from performance point of View.
Another doubt , When we import data in a existing table, which has zero rows because we truncated, and deleted all indexes, it is getting the index from import, at this time does Oracle creates a new index or rebuilds ,since once upon the same indexes were part of this table, Do we need to rebuild them if it creates as a new index, just wanted to know if they are any side effects from performance point of view dropping the existing indexes (we need to because changes in structure )
and creating new ones on huge tables.
Thanks Again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
