Re: Determine number of blocks above HWM and below HWM

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E4C80FA.262E0E6F_at_exesolutions.com...
> Bill & Mary Border wrote:
>
> > I think I know what you are asking. Download
> > "orastat" from http://dbamon.com/orastat and
> > run "orastat -dh".
> >
> > "Akin S. Walter-Johnson III" <asjohns_at_uchicago.edu> wrote in message
> > news:eIR2a.60$K4.25165_at_news.uchicago.edu...
> > > Anyone knows how to determine number of blocks above and below the
High
> > > water mark, and if so
> > > how can I reset the HWM.
> > >
> > >
>
> I'm not interested in whether some tool written by some person is
reporting
> some value next to some label. I am asking how can there be a block above
the
> HWM? It goes against my entire understanding of the concept behind HWM.
>
> Howard, Tom, Jonathan, Richard ... any help here? Thanks.
>

Hi Daniel,

I believe this is what the OP is referring to.

When you create a (say) table, you specify an initial size (say 100M).

The table has at least a segment header, although there might be other "overhead" blocks if it's say a ASSM table. Within the header is the "pointer" which effectively points to the HWM within the table. When we insert a bunch of rows, the freelist(s) point to those blocks below the HWM which can be inserted into. When there are no more free blocks, the HWM is incremented and additional blocks get put on the freelist. The blocks below the HWM are therefore those blocks that have been formatted and have at some point in time been placed on the freelist. Those blocks above the HWM have never been used by the table and have never been on a freelist.

So in our example, if we insert 20M or so into the table, then the first 20M or so of blocks are below the HWM and the remaining 80M or so of blocks are above the HWM.

When Oracle performs a full table scan, Oracle only need read up to the HWM (ie. the first 20M) as it's impossible for there to be any data in the later allocated 80M of the table.

It may be useful to know the HWM and the current number of blocks above it as it might help determine when a table will next allocate another extent, determine if your initial allocation was way over the top (maybe minextents shouldn't have been 100) and a deallocate operation is appropriate, etc. etc.

To determine how many blocks are currently above the HWM, you can look at the EMPTY_BLOCKS stat in the DD (provide you've analyzed your tables) as it accurately reports the number of blocks above the HWM at the time of analyzing. If you haven't analyzed for a while, then the dbms_space.used_space procedure will also provide the necessary data.

As Pete said, it's a touch different with ASSM in that tables are not necessarily filled in a "left to right, one extent at a time" fashion and each allocated extent has it's own version of a HWM, although a "High" HWM still exists.

Cheers

Richard Received on Fri Feb 14 2003 - 08:38:36 CST