Notes interleaved:
--
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 ...
>
>"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.
>>
Close to true, but along with the formula
>> time saved x frequency = negligible benefit.
you ought to consider
time to decide x cost of error = vanishingly small
and possible benefit is not negative
>> 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.
Very important point - and one that is usually overlooked -
we should ALWAYS consider separating object based on
the different way in which they are used.
>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.
Note particularly my comment about 'tablespace quotas'.
If you TRUNCATE a table in an LMT when it has 100 extents,
you will find that this results in 100 updates to tsq$. It is very
easy for the first 'quick and easy' experiment to lead you to the
wrong conclusion.
>
>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)
But see above - the need to manage tsq$
>
>
>
>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 ?
>
Can we assume that some databases have objects with extreme
variations in size - e.g. small = 64K, large = 2 GB.
In this case, I suggest that introducing multiple blocks into a freelist
map is using a piece of code that is rarely exercised, and therefore
introduces the possibility of an error right where it would cause most
hassle (i.e. in a big object). Consequently you would want to ensure
that your extent size is at least 4MB to meet the needs of the 2GB
extents.
Would you want to allocate every extent at 4MB when you have lots
of objects less than 64K. (In fact it probably doesn't matter very much
if you do this in most cases - but some systems have been known
to run to 10,000 extents, which would result in 40GB of waste space.
So it is obviously sensible (in some cases) to use multiple extent
sizes.
On a completely different tack - how much weight do you attach
to making the reports intuitively easy to comprehend; or perhaps
how much cunning do you want to embed into determining that
an emergency has arisen ?
An email when 'number of extents exceeds 32' is very easy to
put together. Not something you could do if you didn't decide on
a pattern of extent sizes being appropriate to object sizes.
And again - if a object has moved from 3,712 extents to 3,741
extents overnight, is that good, bad, or indifferent ? If it has moved
from 10 to 11 it is definitely worth a check.
Overall I think you have enough minor details and potential threats,
no sound contra-indications, and a cost that is virtually nil; so it makes
sense to follow the strategy - even if it is unnecessary.
Received on Mon Jun 17 2002 - 09:43:48 CDT
