Diana
Actually, I have had some experience with locally managed tablespaces on
HPUX-64bit version of 8.1.6. The trouble with them mostly had to do with
temporary and rollback segments. I can't remember exactly what the problem was
with temporary, I can recall something about an ORA error I was getting, but
it's a bit fuzzy. The rollback segment problem had to do with an Oracle bug
when dropping them, it caused the server session to be killed in svrmgrl, gave
me a bad feeling. As far as using them for tables, indexes, etc .. I had them
running in a production environment for about 4 months with no other
significant problems.
Tom Tyson
- Diana Duncan <Diana_at_fileFRENZY.com> wrote:
> 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
Received on Mon Sep 18 2000 - 08:52:51 CDT
