Oracle-L: RE: Locally Managed Tablespaces

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Wed, 16 Apr 2003 21:25:39 -0800
Message-ID: <F001.0058398C.20030416212539@fatcity.com>

I suppose where I'm coming from is that for the vast majority of databases out there, you could quite happily get away starting with 1 tablespace with 1m extents, then when it gets to (say) 4G or 8G in size, you simply add another tablespace with 1m extents, and so forth ad infinitum, with maybe just a slightly more disciplined approach if you want to transport them.

Certainly not optimal, but (assuming a reasonable underlying I/O infrastructure) probably a lot closer than one would imagine. Should you need any I/O balancing etc, then virtually every tablespace or even the segments within it, is interchangeable with another. Sort of like turning all of your tablespaces into an extent-mapped file system like Veritas.

In terms of large segments, whilst there are certainly a lot of "objects" (tables, indexes, clusters, et al) out there more than 4G, they are often composed of several segments each less than 4G, simply for administrative convenience.

Cheers
Connor

PS - Of course, I do have to concede that with all my meanderings on this subject, all the databases I've put together from scratch always have lots of (uniform) tablespaces each with different extent sizes and the like. And I'm still old school where I like to control which disks/volumes things are being placed onto. Nothing like being a hypocrite :-)

Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> wrote:
> I will admit that I don't know at what point Steve
> Adam's recommendation
> would actually have an effect on performance. Here's
> another way to look at
> it:
> Is there any situation where having a large number
> of extents is beneficial?
> For example is there any case that 50000 extents
> would be better than 5000?
> (Assuming each extent is large enough that any disk
> read will have to
> retrieve at least that amount of data.) As far as I
> know there is no
> benefit. So if you can predict the growth of your
> table, how does it hurt to
> use an extent size that will keep the number of
> extents in the thousands?
>
> Which of course is your assumption with a ceiling of
> 4G on segment size, but
> I would think that segment sizes > 4G are not
> uncommon.
>
> > -----Original Message-----
> > From: Connor McDonald [mailto:hamcdc_at_yahoo.co.uk]
> >
> > Whilst I agree with Steve's assessment as it
> applies
> > to squeezing the n'th degree of efficiency from
> > Oracle, I'd be keen on hearing from anyone that
> > suffered any detectable / noticeable performance
> > degradation from having an excess of extent map
> blocks
> > in their system.
> >
> > Don't get me wrong, I've no doubt that such a
> > degradation must exist, but I'd be very surprised
> if
> > there are many databases out there that are that
> well
> > optimized so that this becomes a relevant issue.
> >
> > If that's the case, then I'm back to my argument
> that
> > for almost all databases, you can quite happily
> get
> > away with a 1m extent size for *every* segment in
> the
> > database (assuming a 4G ceiling on segment size)
>

Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo http://search.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Thu Apr 17 2003 - 00:25:39 CDT

RE: Locally Managed Tablespaces - Questions.