Re: Simple Space Question

"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 Tue Jun 18 2002 - 14:50:56 CDT