Re: Simple Space Question

Ah! Fragmentation!!

Put it this way: DBA_FREE_SPACE stores one row for each piece of free space found in a tablespace. So if a tablespace shows 20 rows, there are twenty pieces of free space, probably discontiguous one with another, and hence scattered throughout the tablespace.

It's also possible, however, that the pieces of free space are contiguous with each other: an area of 10 blocks next to one of 5 blocks is being reported as a 10 and a 5 and not just a single 15 because there used to be an extent occupying the first 10, and another occupying the 5. When someone drops or truncates the segment(s) involved, Oracle leaves behind the old extent boundaries. Only when you coalesce the tablespace (alter tablespace X coalesce) will the two pieces of free space be combined into a single, 15-block, piece. If a segment someday wants to extend by 15 blocks before the coalesce, it wouldn't be able to do so. And that's what fragmentation is all about: plenty of free space, none of it of a size that a segment can make use of.

So DBA_FREE_SPACE can help you determine whether you are suffering from fragmentation. If all you have is 20 pieces of free space, I'd be tempted to say that it's not an issue. When the count of free space records for a single tablespace gets into the multiple-dozens and the size of each of them is fairly small (there's a "blocks" column to work out how big each piece is), and wildly different from each other, then that's a fairly good indication of fragmentation.

So, to answer your query in general terms: try an alter tablespace XXXX coalesce. And if you see the number of records for a tablespace reduce, you know you had some fragmentation caused by segment truncation or drops, and you've just cured it. If it doesn't reduce, then all you have is pockets of free space scattered through the tablespace: so long as each piece is of a sufficiently juicy size, you don't have to worry about it too much. Some segment or another will eventually want to make use of it. And given that you said in your original post that all your tablespaces are locally managed, I would expect to see that all your free space pieces are of identical sizes -which is most definitely not an issue, since every existing segment will someday want to acquire another extent of exactly the same size that your free space pieces are made of. Not an issue at all, therefore. You wouldn't even need to coalesce (and in locally managed tablespace, you can't, since all extents must be of the 'uniform size'. You can't make two uniform sized extents suddenly become one extent of (uniform size * 2), because then it wouldn't be a uniform size anymore!!).

Just out of interest: The only way to reduce the free space count to just 1 is incredibly expensive. You'd have to export everything in the tablespace, drop everything, and then import everything from scratch. Or you could move everything to another tablespace, and move everything back -and then re-create all indexes. None of the options are particularly attractive.

But I wouldn't be concerned about even 1000 pieces of free space in locally managed tablespace: since each will be precisely the same size as the other (assuming 'uniform size' was specified) they are each as usable as any other. And one day, they *will* be used by a segment wanting to extend.

Regards
HJR "Arthur" <amerar_at_iwc.net> wrote in message news:8b622eae.0206190524.4780d03d_at_posting.google.com...
> Howard,
>
> Thanks again for your input. We're not on 9i yet, but we'll soon be
> there if all goes well.
>
> In your first reply you suggested using DBA_FREE_SPACE to look at the
> total free space for a tablespace. Just a small question here, when I
> look at DBA_FREE_SPACE, some of the tablespaces have several entries.
> In fact, a cople have more than 20. Is this bad? Do I need to
> correct it somehow so that each tablespace appears only once or twice?
>
> Thanks again,
>
> Arthur
>
>
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:<aeo2vq$ds6$1_at_lust.ihug.co.nz>...
> > "Arthur" <amerar_at_iwc.net> wrote in message
> > news:8b622eae.0206180837.23a9177e_at_posting.google.com...
> > > Howard,
> > >
> > > Thanks for the informative reply. I found the information very
> > > useful. I wanted to ask a couple of more things if you do not mind.
> > >
> > > In your reply, you said: "I tend to prefer using DBA_TABLES (for
> > > tables alone, obviously), and comparing BLOCKS (below the HWM) to
> > > EMPTY_BLOCKS (above the HWM) -though this breaks down in you're using
> > > ASSM (as does DBMS_SPACE!)."
> > >
> > > I'm not sure what you mean by ASSM?
> > >
> >
> > A new feature in 9i, called Automatic Segment Space Management -which
block
> > is used for the next insert is now worked out by consulting a bitmap,
not a
> > free list. It complicates the picture, because ASSM-segments have Low
High
> > Water Marks as well as the High High Water Mark. And blocks thus has to
be
> > defined as "blocks which are below the low high water mark and below the
> > high high water mark" and empty_blocks has to be defined as "blocks
which
> > are below the high high water mark, but which are above the low high
water
> > marks, and which are above the high high water mark".
> >
> > All of which was such a mouthful that Oracle changed the goalposts and
> > declared that blocks really means "blocks which will be read by a full
table
> > scan" and empty blocks as "blocks which won't be read by a full table
> > scan" -and that definition is actually good enough for both ASSM and
> > non-ASSM segments.
> >
> > > Also, if I analyze the tables frequently, then can I assume that
> > > BLOCKS + EMPTY_BLOCKS will be the total space allocated for that
> > > table? And that if I sum that up for an entire tablespace I can get
> > > the total for the tablespace?
> >
> > Er, off the top of my head, and not having checked it, yes.
> >
> > >
> > > Moving a tablespace to reset the HWM can be expensive, I assume.
> >
> > Certainly. There's a full table scan. Indexes are invalidated. And
> > momentarily there are two versions of the table, so you'd need nearly as
> > much free space as the table currently occupies.
> >
> > >Is
> > > there a way for me to check whether or not I need to reset the HWM?
> > > So, if I saw that my HWM is at block 100,000 and the highest block of
> > > my data is 500, then a move would be beneficial here.
> >
> > Correct. You work out num_rows*avg_row_len, and that's how much the
table
> > OUGHT to be occupying if it were ideally packed. You then compare that
to
> > the number of blocks it's ACTUALLY occupying, and if there is a wild
> > imbalance, then you know the HWM is inflated up the end of the table
> > somewhere, and a re-compaction might be beneficial -always assuming your
> > code does full table scans, otherwise it's not worth it for performance
> > gains, but only space gains.
> >
> > If you do that calculation, remember to allow about 88 bytes per block
for
> > the header, and whatever PCTFREE is set to. For example, if
> > num_rows*avg_row_len = 819200, don't assume that means you ought to be
using
> > 100 8K blocks. Because in an 8192-byte block, with PCTFREE of 10, you've
> > actually only got (8192-88=8104....... 8104*0.9=7293 bytes) of usable
space.
> > Therefore, 819200 bytes of data would have to be stored in
> > 819200/7293=112.32, or 113 blocks. And that's an ideal packing. If it
turned
> > out you were actually using 120 blocks, I'd be happy with that. Only if
you
> > were using something like 550 blocks would I think there was a problem.
In
> > other words, give a bit of slack, and don't panic trying to reclaim
every
> > last block.
> >
> > Regards
> > HJR
> >
> > >
> > > Thanks again,
> > >
> > > Arthur
> > >
> > >
> > >
> > >
> > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:<aelp97$6vm$1_at_lust.ihug.co.nz>...
> > > > Why is it so hard?
> > > >
> > > > You have four views to worry about. DBA_FREE_SPACE tells you how
> > many/what
> > > > size chunks of free space you have within a tablespace.
> > > >
> > > > DBA_SEGMENTS tells you how many blocks in total (above and below the
> > high
> > > > water mark) a segment has acquire. And DBA_EXTENTS tells you how
those
> > > > blocks are broken up into extents.
> > > >
> > > > Which leaves knowing how many of those blocks are above the high
water
> > mark
> > > > and how many are blow. Which can be tricky. The DBMS_SPACE package
can
> > be
> > > > used to return the number above the HWM (it's the FREE_SPACE
procedure).
> > But
> > > > that can be awkward to run, so -assuming statistics have been
> > calculated- I
> > > > tend to prefer using DBA_TABLES (for tables alone, obviously), and
> > comparing
> > > > BLOCKS (below the HWM) to EMPTY_BLOCKS (above the HWM) -though this
> > breaks
> > > > down in you're using ASSM (as does DBMS_SPACE!).
> > > >
> > > > Why on earth you would bother 'resizing the object' when you're
using
> > > > locally managed tablespace beats me, however. The whole point of
locally
> > > > managed tablespaces is that extent acquisitions don't matter (much)
> > anymore.
> > > > All you really need to do is to count the number of extents a
segment
> > has
> > > > (DBA_EXTENTS). When the number reaches a couple of hundred, it's
time to
> > > > move the segment into a new tablespace that comes with bigger
uniform
> > extent
> > > > sizes (say, from a 64K extent size tablespace to a 1M extent size
> > > > tablespace).
> > > >
> > > > Any other approach seems (to me) to be missing the point: locally
> > managed
> > > > tablespaces make extent/segment management a light-weight affair.
You
> > seem
> > > > to have taken advantage of the new technology yet still worrying
about
> > the
> > > > things that the old technology gave us good reason to worry about.
> > > >
> > > > As for re-setting the high water mark: truncate, drop and move are
the
> > only
> > > > commands that would readily lend themselves to that sort of job. Of
the
> > > > three, MOVE is the obvious candidate, since it doesn't require an
export
> > and
> > > > import to complete the job. There is no need to move the table to
> > another
> > > > tablespace. Moving the segment around *within the same tablespace*
is
> > > > perfectly legitimate: just use the command 'ALTER TABLE EMP
> > MOVE;' -there's
> > > > no new tablespace mentioned there, so EMP stays put in the same
> > tablespace.
> > > > But it gets re-populated, and the High Water Mark is adjusted
> > accordingly.
> > > >
> > > > Regards
> > > > HJR
> > > >
> > > > "Arthur" <amerar_at_iwc.net> wrote in message
> > > > news:8b622eae.0206171131.5eeadf38_at_posting.google.com...
> > > > > Hello All,
> > > > >
> > > > > Understanding space usage in an Oracle database is so hard. Maybe
> > > > > someone can help me out with this.
> > > > >
> > > > > All of my tables are locally managed. Basically I am interested
in
> > > > > seeing how much unused space is in each table / tablespace. I
want to
> > > > > know if any of these objects have been 'over allocated' and are
much
> > > > > larger than they need to be. If I need to move the HWM, can I do
> > > > > this without import/export or moving the table to another
tablespace?
> > > > >
> > > > > Also, I'd like to see how much space is left in each table /
> > > > > tablespace. This gives me an idea how close we are to needing to
> > > > > resize the object.
> > > > >
> > > > > If you can send a copy of your response to my email, I'd
appreciate
> > > > > it......
> > > > >
> > > > > Thank you,
> > > > >
> > > > > Arthur
Received on Wed Jun 19 2002 - 18:02:23 CDT