Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: having multiple datafiles per tablespace vs. one datafile per

RE: having multiple datafiles per tablespace vs. one datafile per

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Fri, 15 Sep 2000 23:50:36 -0400
Message-Id: <10620.117194@fatcity.com> 





Hi Steve,



I agree that using LMT may not cause problems. But on the other hand you
should try having 4000 TS & data files (dictionary based). Any storage
management query (free space, used space, extents, etc.) takes too long. I
tried to analyse the sys schema and ended up getting ora-600.



Also the plan is to have a TS for each user so probably there will be a lot
of deletion and creation of TS for old and new users.
You know the effect of this on the cluster.



Anyway I wish I know the reason for having a TS for each user.



Regards,



Waleed



-----Original Message-----


From: Steve Adams


To: Khedr, Waleed; Multiple recipients of list ORACLE-L
Sent: 9/15/00 11:28 PM


Subject: RE: having multiple datafiles per tablespace vs. one datafile per



Hi Waleed,



I beg to differ. Why should the performance of the C_TS# cluster be
significantly affected by the number of tablespaces? All routine access
is index


based, and Diana is likely to be using locally managed tablespaces
anyway.



@   Regards,

@   Steve Adams

@   http://www.ixora.com.au/

@   http://www.christianity.net.au/

@

@   Going to OpenWorld?

@   Catch the Ixora performance tuning seminar too!

@   See http://www.ixora.com.au/seminars/ for details.





-----Original Message-----


From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
Sent: Saturday, 16 September 2000 11:29


To: Multiple recipients of list ORACLE-L
Subject: RE: having multiple datafiles per tablespace vs. one datafile
per




The only missing thing here is each file on a dedicated disk!



The way the data dictionary is structured (clusters) will make
performance


night mare when dealing with 4000 tablespaces.






-----Original Message-----


Sent: Friday, September 15, 2000 5:56 PM
To: Multiple recipients of list ORACLE-L
per




Diana,


Can you share any of the reasons for separating each account into it's
own


tablespace?  This to me just seems to be a maintenance nightmare, not to
mention scalability(not sure that is a word actually).



Steve McClure



-----Original Message-----


From:	root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of Diana
Duncan

Sent:	Friday, September 15, 2000 1:11 PM
To:	Multiple recipients of list ORACLE-L
Subject:	RE: having multiple datafiles per tablespace vs. one


datafile per



Steve (and others),



Is there a recommendation for the maximum number of datafiles that make
sense for a database?  We are expecting over 4000 (each account gets
their


own data and index tablespaces, each with one datafile apiece -- believe
me,


there are reasons for this), and that number may go higher.  I'm
starting to


get concerned about the number, but I don't know if my concern is
justified,


or if I'm just paranoid.



Thanks for any input,


Diana



-----Original Message-----


Sent: Friday, September 15, 2000 10:31 AM
To: Multiple recipients of list ORACLE-L
per tablespace




Hi Babu,



The potential drawbacks of having multiple datafiles are trivial
compared to


the


potential benefits.



On most platforms when you issue a CREATE TABLESPACE command with
multiple


datafiles, those datafiles will be "zeroed" in parallel by slave
threads.


Even


on platforms that do not support this, you can do multiple ALTER
TABLESPACE



ADD



DATAFILE commands in parallel. Therefore multiple datafiles can make
tablespace


creation much faster.



For file system based databases, having multiple datafiles may reduce
the


risk


of contention for the operating system's datafile read/write locks
(inode


locks).



Having multiple datafiles per tablespace facilitates the adoption of a
uniform


datafile size policy, which in turn facilitates disk load rebalancing.



Finally, I answered a related question on Ixora Answers a few weeks ago,
and


you


may find that answer relevant. Look for the answer headed "Is AUTOEXTEND
OK?" at


http://www.ixora.com.au/q+a/0008/30150113.htm.



@   Regards,

@   Steve Adams

@   http://www.ixora.com.au/

@   http://www.christianity.net.au/

@

@   Going to OpenWorld?

@   Catch the Ixora performance tuning seminar too!

@   See http://www.ixora.com.au/seminars/ for details.





-----Original Message-----


[mailto:babu.nagarajan_at_iflexsolutions.com]
Sent: Friday, 15 September 2000 22:20


To: ORACLE-L_at_fatcity.com; oracledba_at_lazydba.com
tablespace




Hi



Could any one point out the advantages/disadvantages of having multiple
datafiles for a tablespace as against having one datafile per
tablespace?


--
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Author: Diana Duncan
  INET: Diana_at_fileFRENZY.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Author: Steve McClure
  INET: steve_at_pactr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in


Received on Fri Sep 15 2000 - 22:50:36 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US