Re: determining extent size, etc.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1023954046.9619.1.nnrp-10.9e984b29_at_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.

Agreed. Therefore this suggests spending the time deciding which tablespace an object should reside in purely on a preferred extent size is unnecessary.
>
> 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.

The first point regarding the regular reorganising of objects suggests that they should be seperated from non-reorganised objects (something I advocate although if we are reorging because of too many/few extents, then I've a big question). However, that's sizing extents of objects based on reorg characteristics, not overall size as initally suggested. Additonally, has anyone run tests on how long it typically takes to drop objects in a LMT with large extents (10,000+). Although I'm not attempting to prove anything scientifically, it took just 1.5 secs to drop a 10005 extent table on my mickey mouse 9.0 database.

I don't quite follow the second point regarding quotas. I would have thought smaller extent sizes would have provided a finer level of granuality regarding growth of objects meaning you have more control over quota violations (it wasn't the result of just that one large extent being allocated, but the sum of all those littlies)

>
> 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 !
>

I admit the extent map is my "unwritten" guideline but again I'm in a questioning mood. What if I need 100 extent maps. What is the actual effect on performance of reading such a table. The frequent dynamic growth of the table and the effect on inserts is my other main issue. But are these enough to justify having specific tablespaces with specific uniform extents sizes (and subsequent reorgs if we get it wrong) ? As for dba_extents and the such, again it's a point I made in my earlier post but is it reason enough. I think not.
>
> 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.
>

As you say, I can monitor the growth of objects just as effectively (if not more so) with smaller sized extents. Space managment from a capacity management point of view is the crucial point. As long as you plan to have 10000 extents and you've got the space reserved/allocated for such a growth expectancy then the battle is being won.

> 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.

Again, seperating objects based on other characteristics than just overall size. No argument from me. But again why use a different extent size, what is the actual realized benefit ?

I readily admit to following practices regarding all this based on habit, previous experience and a mental weakness (among many) of having to be really really tidy about things (my CD collection is alphabetically ordered based on artist, and then sub ordered based on year. Kids change it, no icecream for desert). So seperating objects based on overall size in appropriate tablespaces and reducing the overall number of extents hits a chord and "seems" right.

Storing CDs the way I do has a point (I want Hunky Dory by David Bowie, I know where to find it) But is there a point in storing objects with different uniform extent sizes ?

Richard

>
>
>
> --
> 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 Mon Jun 17 2002 - 05:36:01 CDT