Re: Simple Space Question

With a plain 'analyze table emp compute statistics' (which you aren't doing any more, are you, because it's deprecated in 9i!!), it collects statistics on the index, too.

This is from 9iR2:

SQL> drop index YADDA;
Index dropped.

SQL> create index yadda on emp(mgr);
Index created.

SQL>
SQL> select
BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER _KEY,CLUSTERING_FACTOR,
  2 STATUS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED,DEGREE from dba_indexes where index_name='YADDA';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS

---------- ----------- ------------- ----------------------- ---------------
-------- ----------------- -------- ----------

----------- --------- ----------------------------------------

VALID
                      1

SQL> analyze table emp compute statistics; Table analyzed.

SQL> select
BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER _KEY,CLUSTERING_FACTOR,
  2 STATUS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED,DEGREE from dba_indexes where index_name='YADDA';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS

---------- ----------- ------------- ----------------------- ---------------
-------- ----------------- -------- ----------

----------- --------- ----------------------------------------
         0           1             6                       1
1                 1 VALID            13
         13 25/JUN/02 1

Regards
HJR "Arthur" <amerar_at_iwc.net> wrote in message news:8b622eae.0206240715.50a07eaf_at_posting.google.com...
> Howard,
>
> I have a quick question for you. If I analyze a table and compute
> statistics, does it automatically analyze the indexes on the table
> also, or do I need to do that seperately?
>
> Thanks,
>
> 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 Mon Jun 24 2002 - 14:18:06 CDT