Home » RDBMS Server » Performance Tuning » Number of Datafiles in a Tablespace
Number of Datafiles in a Tablespace [message #193275] Fri, 15 September 2006 09:38 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous message
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.
Previous Topic: Estimate_Percent for Analyze Schema
Next Topic: problems with updating big table by commit at end
Goto Forum:
  


Current Time: Fri Dec 02 18:55:07 CST 2016

Total time taken to generate the page: 0.17948 seconds