Re: determining extent size, etc.

I guess that's a question I have.

If the number of extents in a locally managed tablespace is not the issue, then why bother with these 3 levels of extent sizes. Is this not applying good dictionary managed tablespace practices to locally managed tablespaces ?

If we "incorrectly" place a 250M object in a locally managed tablespace with a uniform size of 256K, what are the actual performance and management issues of this 1000 extent object ? It sounds like a lot of extents and it doesn't kind of feel right but is it an issue ? Other than some data dictionary queries (eg. dba_extents) taking longer to process as Oracle needs to do a bit more work to derive these values, will the general performance of the database suffer ? What if we have 1000 objects with a 1000 extents, what then ?

When asked how many extents should an object have, I like the answer "as many extents as you PLAN it to have". Is there a better (and less corny) one.

Love some feedback on this.

Richard

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D0629F4.C8EC8524_at_exesolutions.com...
> Ed Stevens wrote:
>
> > Platform Win2K, Ora 8.1.7.3
> >
> > We are getting ready to upgrade all of our db's from 8.0.5 to 8.1.7
(finally!)
> > and in the process are doing some rethinking on extent sizes,
distribution, etc.
> >
> > In the past we have generally had 1 tablespace for user tables. With
the
> > upgrade we want to go to LMT and have 3 tablspaces for user tables --
one for
> > 'small' tables (and a 'small' extent size), one 'medium' and one
'large', with
> > uniform extent sizing in each TS roughly matched to the tables placed
there.
> >
> > What I'm looking for is some rules of thumb (here we go again!) on
determining
> > the extent size for each TS and determining which tables to go in each.
I
> > figure the first step to be a query like
> >
> > SELECT NUM_ROWS * AVG_ROW_LEN tsize
> > FROM DBA_TABLES
> > WHERE OWNER = 'scott'
> > ORDER BY tsize;
> >
> > So, given that info and assuming that the result does NOT yeild any
clear break
> > points in table sizes, where would one go from there?
> >
> > Or am I completely off base?
> > --
> > Ed Stevens
> > (Opinions expressed do not necessarily represent those of my employer.)
>
> If it helps I generally begin the thought process using orders of
magnitude. So, for
> example:
>
> 256K
> 2.5M
> 25M
>
> Then adjust from there.
>
> Though based on some of the mythology thumping we've had around here
lately I'm not
> at all sure some people wouldn't advocate going 256K (or some other value)
for
> everything and just letting the number of extents expand indefinitely.
>
> Daniel Morgan
>
Received on Wed Jun 12 2002 - 20:16:24 CDT