Re: LOCALLY MANAGED EXTENT PERFORMANCE

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

Other related posts: