Re: Finding space-bound objects?

Mark D Powell wrote:
>
> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3CCEE2A5.3D07_at_yahoo.com>...
> > Mark D Powell wrote:
> > >
> > > tim.kearsley_at_milton-keynes.gov.uk (Tim Kearsley) wrote in message news:<725736ef.0204300252.2ce88692_at_posting.google.com>...
> > > > Hi all,
> > > >
> > > > I've looked at several scripts which purport to produce a list of
> > > > tables, indexes etc. which can't extend within the tablespace, because
> > > > the largest contiguous piece of free space is less than the
> > > > NEXT_EXTENT value for the segment.
> > > >
> > > > However, unless I'm not seeing something here, this is too simplistic
> > > > for the real world isn't it?
> > > >
> > > > What happens if the segment has a PCT_INCREASE value non-zero? Surely
> > > > in that case the next extent it wants to create will be the size of
> > > > the last extent it created * (1 + PCT_INCREASE/100) won't it? Or have
> > > > I misunderstood the meaning of PCT_INCREASE or the way it is
> > > > implemented?
> > > >
> > > > For example if the table BIGTAB has a PCT_INCREASE value of 50 and has
> > > > INITIAL_EXTENT and NEXT_EXTENT values of 16384 and 16384 respectively,
> > > > won't the extents be allocated with these sizes:
> > > >
> > > > 16384
> > > > 16384
> > > > 24576
> > > > 36864
> > > > 55296
> > > > etc...
> > > >
> > > > I appreciate that these might not be the exact values, as presumably
> > > > the real values would have to be multiples of the block size, but you
> > > > can see the principle.
> > > >
> > > > Any comments or input very welcome!
> > > >
> > > > Regards,
> > > >
> > > > Tim Kearsley
> > > > Database Manager,
> > > > Milton Keynes Council
> > >
> > > Tim, the value in the next_extent column of dba_tables is the size
> > > that the next allocated extent will attempt to use so you can use it
> > > to find objects that will fail on their next attempt to extend.
> > > PCTINCREASE is already included.
> > >
> > > I recommend you look at conversion/migration to using locally managed
> > > tablespace, but I understand that if you have an existing system
> > > conversion may not be desirable as the database is currently
> > > configured.
> > >
> > > Prior to LMT and inline views I wrote two plsql scripts to accomplish
> > > this task. They are a little long to post but I will email them on
> > > request.
> > >
> > > But here is a query that will tell you how many extents equal to the
> > > largest extent, which any object in the tablespace will request, are
> > > available. The script will work for version 7.3+:
> > >
> > > COLUMN tablespace_name FORMAT a20 heading 'Tablespace Name'
> > > COLUMN extents FORMAT 99999 heading 'Available|Extents'
> > >
> > > select f.tablespace_name
> > > ,sum(floor(nvl(f.bytes,0)/(s.MEXT))) extents
> > > from sys.dba_free_space f
> > > ,( select tablespace_name, max(next_extent) as MEXT
> > > from sys.dba_segments
> > > group by tablespace_name
> > > ) s
> > > where f.tablespace_name = s.tablespace_name(+)
> > > group by f.tablespace_name
> > > /
> > > Available
> > > Tablespace Name Extents
> > > ==================== =========
> > > AHQTS01 7213
> > > FSWIL01 136
> > >
> > > This SQL will not tell which objects may fail but it does point you to
> > > tablespaces that are running low on space and works very well with
> > > LMT's but will also work on dictionary managed tablespaces. Warning
> > > 100% full tablespace have no free space and hence do not show.
> > >
> > > HTH -- Mark D Powell --
> >
> > Its worth noting that the "can I extend?" question is 'unanswerable' in
> > the case of auto-allocate lmt's anway
> >
> > hth
> > connor
>
> My first thought when I read your (email) reply was true, but then the
> question hit me: what does Oracle set the next_extent value to for an
> object in an autoallocate tablespace? I do not have an autoallocate
> tablespace that I can check.
>
> Based on the size of the largest extent size allocated in the
> tablespace you can still make a reasonable guess to when trouble is
> definitely approaching space wise. But that jump from 8M extents to
> 64M extents could present a problem.
>
> If you wanted to I imagine you could write some plsql based on the
> current, but subject to change, allocation pattern as noted by Howard
> J Rogers in an earlier thread on this subject, but it seems like a lot
> of work to try to predict running out of room when using uniform
> extents makes it so much easier.
>
> Conner, I believe I am in basic agreement with you in principle, but
> your statement is a little too absolute. As a DBA it is our job to
> predict when a file is necessary to be added to the tablespace (or an
> object migrated to another tablespace) so an estimate has to be made.
> But you are right about it being difficult for an autoallocate
> tablespace.
>
> -- Mark D Powell --

The main hassle with a prediction algorithm is that I've noticed that it does not appear to be hard-and-fast. In an empty tspace, you observe the 'n' x 64k, 'm' x 1m etc, but there seems to be some other smarts in there when objects are dropped and recreated, or given a large initial extent etc.

In any event, given that auto lmts are the default in 9i, we can expect a hell of a lot more of them. It does give rise to an argument for having only a "small" number of segments per tablespace, since if you had 1000 objects at 1m, then it possible-but-unlikely to get a 7000m increase in a very short time frame

Cheers
Connor  

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

http://www.oracledba.co.uk

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