Re: next extent query

I use AUTOALLOCATE all the time for a couple of instances of mine. It has been my experience that what you write is true. If a 1MB exent is allocated, you are not guaranteed that the next extent will be 1MB or 8MB. In fact, it has been my experience that you aren't guaranteed that *any* of these values will be chosen, as can be seen below:

GASP SQL> select bytes/1024/1024 as bytes_MB,count(*) as num_extents   2 from dba_extents
  3 where tablespace_name='ORTHO_WASH_DC_DATA'   4 group by bytes/1024/1024 order by 1;

  BYTES_MB NUM_EXTENTS
---------- -----------

     .0625          68
         1         130
         5           3
         8         241
         9           1
        16           1
        21           1
        25           1
        26           1
        33           2
        34           3
        56           3
        59           3
        60           1
        63           8
        64        2473

16 rows selected.

GASP SQL> select extent_management,allocation_type   2 from dba_tablespaces
  3 where tablespace_name='ORTHO_WASH_DC_DATA';

EXTENT_MAN ALLOCATIO
---------- ---------
LOCAL SYSTEM And just so people don't jump to conclusions...this tablespace was not migrated from a DMT. It has always been a LMT with AUTOALLOCATE.

Cheers,
Brian

Niall Litchfield wrote:
>
> "Arcangelo" <xxx_at_yyy.com> wrote in message
> news:3eee3431$0$16256$afc38c87_at_news.optusnet.com.au...
> >
> > "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> > news:3eee2ed6$0$11380$cc9e4d1f_at_news.dial.pipex.com...
> > > "Arcangelo" <xxx_at_yyy.com> wrote in message
> > > news:3eee2847$0$24424$afc38c87_at_news.optusnet.com.au...
> > > > (B) The next extent will be the same size as your last one (so check
> out
> > > > BYTES in dba_extents), unless you happen to be on one of the
> > autoallocate
> > > > boundaries. The barebones autoallocate algorithm goes: first 16
> extents
> > > will
> > > > be 64K, then to extent #79 will be 1M, then to extent #199 will be 8M,
> > > > thereafter 64M. So unless you have 15, 79 or 199 extents, your next
> > extent
> > > > will be the same as the last one. However, the algorithm gets more
> > > > complicated if you specify an INITIAL at the time of table creation,
> and
> > > if
> > > > that's the case, see answer (A) above.
> > >
> > > I'm still not entirely convinced that the above is true - since with
> > objects
> > > growing at different rates it could lead to 'unable to allocate extent'
> > when
> > > Oracle could by choosing a different size from the available list. I
> feel
> > a
> > > test coming on. (but not after 4 beers that would be silly - thats the
> > time
> > > for theorizing)
> >
> > Maybe it's the beers, but which bit don't you think is true?
> >
> > I did mention that the numbers quoted were for the *barebones*
> autoallocate
> > algorithm. In other words, take an empty autoallocate LMT, create a table
> > with no storage clause.
>
> What I don't believe to be true - but it might be. Is the implication that
> any given table in an ALMT (autoallocate-lmt) will grow according to the
> pattern you state. In normal usage it wouldn't surprise me if a table grew
> at
>
> 64k for the first 16 extents
> case(max(free_space)>1m 1m) else 64k for the next 79 extents,
> case(max(free_space)>8m 8m,max(free_space)>1m 1m) else 64k etc
>
> In other words pick the largest available free extent under my limit, which
> should simplify to the rule you give in the event of a single growing table
> only. This would seem more sensible since it avoids out of space errors when
> 64k or more is in fact free.
>
> > Anything else, and it gets extremely complicated indeed, granted.
>
> Which may mean we don't disagree - just I misinterpreted your rule.
>
> My Hypothesis above seems to me eminently testable (at least for small
> tablespaces).
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"