The code is not mine to share and it's pretty extensive PL/SQL, not SQL. I
couldn't count the number of hours of sleep it has permitted us...
With this thread, I'm just trying to point out a few things:
- Number of extents has been a totally bogus metric
since the advent of LMT
- number of extents was largely a bogus metric
even with DMT, except when number got really
really large
- Speed of truncation does not measure the "efficiency"
of extent allocation/deallocation for all operations
- classic case of proving that chickens can't fly
by throwing them out of an airplane. Actually,
chickens fly just fine from 20-30 feet up, and
that's all they ever need in real life... :-)
- Performance of operations such as SELECT, INSERT,
UPDATE, and DELETE don't care about the number of
extents at all
- both LMT and DMT: never have, never will...
- Performance of operations such as SELECT, UPDATE,
and DELETE might possibly be affected by extent
size only if they are really really small
- impact on multi-block reads is cause...
- both LMT and DMT: always have, always will...
- Performance of operations like INSERT is not
affected by extent size at all
- both LMT and DMT: never have, never will...
Hope this helps...
-Tim
P.S. I wrote a paper entitled "Myths about Extents
and Performance" several years ago; it's posted
online at "http://www.EvDBT.com/papers.htm",
close to the bottom of the page...
>
> Just forgot to ask..
>
> Do you mind if you share your "alert" with us ?
>
> I was wondering how long would it take to run that alert which
> "computes" next N extents if you have PCT_INCREASE set for a table ...
>
> I'm impatiently waiting ..
>
>>
>> From a production-support perspective...
>>
>> For years, we've been using a script that would alert if any
>> segments in a tablespace are going to run out of space within
>> "N" extents (i.e. 5, 10, whatever). How do you do this for
>> autoallocate tablespaces?
>>
>> I know that some folks have reverse-engineered the sizing
>> algorithm for autoallocate, but I don't think it is yet
>> documented anywhere. Which means that Oracle can feel free
>> to "tweak" it whenever they wish...
>>
>> ...which means lots of pages in the middle of the night...
>>
>>
>>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 26 2005 - 14:16:53 CDT
