Richard Foote wrote:
> "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
Thanks.
Daniel Morgan
Received on Fri Feb 14 2003 - 10:50:51 CST
