Re: LOCALLY MANAGED EXTENT PERFORMANCE

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 25 Apr 2005 14:39:14 -0600
Message-ID: <BE92B192.265FD%tim@evdbt.com>

Doesn't do the job. This only tells me what the largest extent sizes are for segments. It does not tell me whether any of them are possible to run out of space within "N" extensions...

Think it through, considering the three possible alternatives:

DMT
LMT uniform
LMT autoallocate

And you'll see why the last is very difficult to work with, unless you are willing to encode reverse-engineered observations that can change without warning from version to version. Plus, figuring this out is a non-trivial programming exercise anyway, unlike the first two alternatives...

My view on it: autoallocate is OK for very small applications where space just doesn't change very rapidly. When space management is important, it is worth the time to use LMT uniform intelligently. After all, you can't improve that which you can't measure...

on 4/25/05 2:07 PM, Dogan, Ibrahim - Ibrahim at Ibrahim.Dogan_at_Lowes.com wrote:

> 
> Very simple, this gives a rough idea of what your next extents will be:
> 
> SELECT  e.owner, e.segment_name, e.extent_id, bytes AS last_extent_size
> FROM  dba_extents e,
>       (SELECT owner, segment_name, MAX(extent_id) AS extent_id
>       FROM  DBA_EXTENTS
>       GROUP BY owner, segment_name) ee
> WHERE  e.owner= 'xxx' AND
>      e.owner = ee.owner AND
>      e.segment_name = ee.segment_name AND
>      e.extent_id = ee.extent_id;
> 
> 
> 
> Thanks,
> 
> Ibrahim DOGAN
> Sr. Sybase/Oracle DBA
> www.lowes.com
> 
>

>> -----Original Message-----
>> From: oracle-l-bounce_at_freelists.org
>> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
>> Sent: Monday, April 25, 2005 3:22 PM
>> To: oracle-l_at_freelists.org
>> Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE
>>
>>
>> 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...
>>
>>
>>
>>
>> on 4/24/05 8:53 PM, Tanel P=F5der at tanel.poder.003_at_mail.ee wrote:
>>

>>> Hi,
>>> =20
>>> I haven't read the whole thread - but I'd just like to

>> contribute the

>>> fac=

>> t,

>>> that nowadays I save my time and create all tablespaces as
>>> autoallocate -  and haven't seen any performance nor other

>> problems so

>>> far. And I don't  worry about the number or size of extents

>> at all. =20

>>> Tanel.
>>> =20
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 25 2005 - 16:44:57 CDT