Re: Simple Space Question

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 - 08:24:24 CDT