Path: news.easynews.com!easynews!news.good.net!kibo.news.demon.net!demon!diablo.theplanet.net!news.theplanet.net!not-for-mail
From: Connor McDonald <connor_mcdonald@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Generic "Can I extend?" check
Date: Thu, 08 Aug 2002 21:37:01 +0100
Lines: 78
Message-ID: <3D52D66D.4AC0@yahoo.com>
References: <3D518D84.4A77@yahoo.com> <3d523838$0$238$ed9e5944@reading.news.pipex.net>
Reply-To: connor_mcdonald@yahoo.com
NNTP-Posting-Host: modem-3774.chameleon.dialup.pol.co.uk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: news7.svr.pol.co.uk 1028839150 13341 217.134.94.190 (8 Aug 2002 20:39:10 GMT)
NNTP-Posting-Date: 8 Aug 2002 20:39:10 GMT
X-Complaints-To: abuse@theplanet.net
X-Mailer: Mozilla 3.01 (WinNT; I)
Xref: easynews comp.databases.oracle.server:157301
X-Received-Date: Thu, 08 Aug 2002 13:36:16 MST (news.easynews.com)

Niall Litchfield wrote:
> 
> "Connor McDonald" <connor_mcdonald@yahoo.com> wrote in message
> news:3D518D84.4A77@yahoo.com...
> > I've been asked to write a generic routine to determine if a segment can
> > extend.  Easy enough except for auto-alloc lmt's where 'next_extent' is
> > null (ie indeterminate) in xxx_SEGMENTS.
> >
> > So here is my current compromise (in pseudo-code)
> >
> > where nvl(next_extent,
> >   case
> >     when initial_extent < 1m then
> >       case when extents < 16 then next = 64k,
> >            when extents < 80 then next = 1m,
> >            when extents < 200 then next = 8m,
> >            else next = 64m
> >     when initial_extent >= 1m then
> >       case when extents < 64 then next = 1m,
> >            when extents < 184 then next = 8m,
> >            else next = 64m )
> >   > largest_free_space_chunk_in_tablespace
> >
> > The rough translation being:
> > - auto-alloc extents are 16x64k, then 64x1m, then 120x8m, then 64m, when
> > the initial_extent is less than 1m
> > - auto-alloc extents are 64x1m, then 120x8m, then 64m, when the
> > initial_extent is equal/more than 1m
> >
> > In the spirit of "open source", I'm throwing this out the community
> > asking for counter-examples which break the above formula.
> 
> Jonathan pointed out to me back in March that the results you get above are
> what happens when you test creating one table in an otherwise empty
> tablespace, (and that even these values differ between oracle versions yours
> looks like it came from 9i). This discussion is here
> 
> http://shrinkalink.com/307
> 
> I haven't actually done the test (I'm in the middle of upgrading our ERP
> system so don't get much time for playing) but I suspect that you would be
> able to break your code by filling a small tablespace with a number of
> differently sized objects leaving random chunks of differently sized free
> space scattered about the place. In other words if the tablespace has say a
> 64k and a 1m chunk of free space left but you are now in the 8mb extent
> range I rather suspect that an autoallocate tablespace will allocate first
> the 1m and then the 64 k as required.
> 
> --
> 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
> 
> ******************************************

Exactly.

I did some basic playing - like leaving (say) some 800k chunks free in a
tspace and then trying "(initial 2m)" and seeing if Oracle was smart
enough to divvy it up...and it did not.

But I appreciate the testing you did - thats what I'm after.  I'm keen
to have someone break (and thus improve) my formula

Cheers
Connor
-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."