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: Steve Adams <steve.adams_at_ixora.com.au>
Date: Mon, 18 Sep 2000 12:13:59 +1000
Message-Id: <10622.117217@fatcity.com>


Hi Diana,

With regard to locally managed tablespaces, it sounds like you are thinking of using the AUTOALLOCATE policy. If so, let me encourage you to use the UNIFORM policy instead. For reasons, please see the tip on "Planning Extents" at http://www.ixora.com.au/tips/creation/extents.htm.

The problem to which Waleed referred with dropping and creating lots of tablespaces, is that "dropped" tablespaces are never removed from the data dictionary - they are just marked as INVALID. So the C_TS# cluster will continue to grow if you do not reuse tablespace names. This however only impacts the performance of administrative queries that do full table scans against the cluster. It does not affect the performance of recursive SQL. Nevertheless, I would encourage you to consider a scheme whereby tablespace names can be reused.

Otherwise, in my opinion, there is nothing wrong with your 2000 schema 4000 tablespace proposal, and given the requirement for point in time recovery of the metadata for an individual account, it makes good sense.

@ 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: Diana Duncan [mailto:Diana_at_fileFRENZY.com] Sent: Monday, 18 September 2000 4:50
To: Multiple recipients of list ORACLE-L Subject: FW: having multiple datafiles per tablespace vs. one datafile per

Whoops, meant to send this to the list.

-----Original Message-----
Sent: Saturday, September 16, 2000 6:50 PM To: 'Khedr, Waleed '
per

Wow! This is certainly receiving some impassioned responses. I really appreciate the input. ;-)

Waleed, I can't really go into the reasons on the list, but I'll send you a detailed explanation on Monday if you are still interested. In the meantime, could you explain the problems with the cluster? I wouldn't normally worry about one of my Oracle tables having 4000+ rows in it, but I haven't had to create clustered tables before. On the other hand, my experience with the many tablespaces in the Oracle Applications certainly indicated that the recursive calls to the data dictionary slowed down that app considerably. How would you feel about having a seperate schema for each of 2000 accounts? (2000 accounts * 20 tables, with approx. 3 indexes per table) This was an alternate solution proposed by another architect here. Frankly, that one scared me more.

Also, I wouldn't expect too many deletions of accounts (at least I hope). And these are accounts, not individual users, exactly.

Funny, I was just researching the locally managed tablespaces, and had decided I had better use them. I'm looking forward to throwing my storage calculations out the window. ;-) Anyone had any bad experiences with them? Space management is important to us, so I hope Oracle is better at allocating the storage than I am!

Thanks again for the input. I'm finding the list invaluable. If enough people react with shock and astonishment at this idea, it will give me some ammunition to challenge the stated requirements that have made it necessary. Why is it that CTO's are always so technologically clueless? ;-)

Diana

-----Original Message-----
To: Multiple recipients of list ORACLE-L Sent: 9/16/00 12:55 AM

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-----
To: Khedr, Waleed; Multiple recipients of list ORACLE-L Sent: 9/15/00 11:28 PM

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-----
Sent: Saturday, 16 September 2000 11:29
To: Multiple recipients of list ORACLE-L 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
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
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
Received on Sun Sep 17 2000 - 21:13:59 CDT

Original text of this message

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