Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: determining extent size, etc.

Re: determining extent size, etc.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 13 Jun 2002 08:41:34 +0100
Message-ID: <1023954046.9619.1.nnrp-10.9e984b29@news.demon.co.uk>

This is one of those things where the benefit is more perceived than real. LMTs are 'much more efficient' than DMTs at space management - but in carefully designed database systems, space management is a very rare occurrence so

   time saved x frequency = negligible benefit.

On the other hand, if you perceive a need to reorganise some objects on a regular basis, and if you are enforcing tablespace quotas then there is more of a need to worry about limiting the number of extents in those objects.

And, of course, there is always the issue that the extent map for a segment should fit into the segment header if possible - which means 505 extents (last time I checked) for an 8K block size, and this may have dropped for Oracle 9. Even then, the most significant issue (in most cases) will be the impact on reports running against dba_extents - and why do you run those reports . . . to do predictive space management, which is
pretty redundant under LMTs anyway !

Your final observation is the most important one - the number of extents to have is the number of extents you plan to have. I like to set up the 3 to 5 different sizes of extents because I like to predict the size of objects, and to see objects growing at the rate I expect. If I target objects to be around 20 extents, then at one extreme I can see that my estimate was accurate to about 5%. At the other extreme (e.g. as in your 256K extents for 250M object) I could get a very precise estimate of accuracy, but you have to remember that space management still costs, and if you have LOTS of objects in the database and they ALL follow this 'fine-grain' strategy then you are likely to see side-effects that don't show up when testing on just 2 or 3 objects.

And finally, there is the argument of monitoring activity. Large objects tend to have different i/o characteristics from small objects - so it is a good idea to put them in different tablespaces; and if they are in different tablespaces, you might as well use different extent sizes.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Richard Foote wrote in message
<76SN8.11095$Hj3.35238_at_newsfeeds.bigpond.com>...

>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
>
Received on Thu Jun 13 2002 - 02:41:34 CDT

Original text of this message

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