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

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Fri, 15 Sep 2000 18:05:56 -0400
Message-Id: <10620.117183@fatcity.com>

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-----
From: Steve McClure [mailto:steve_at_pactr.com] Sent: Friday, September 15, 2000 5:56 PM To: Multiple recipients of list ORACLE-L Subject: RE: having multiple datafiles per tablespace vs. one datafile 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

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

Received on Fri Sep 15 2000 - 17:05:56 CDT