Re: Documentation for HHWM and LHWM?

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Tue, 6 May 2008 14:34:48 -0500
Message-ID: <7b8774110805061234v70088fa4o3b91fbdb2f1d628e@mail.gmail.com>


Thanks to both of you, Ric Van Dyke and K Gopalakrishnan.

I am the kind of person that likes to see stuff work. I believe I understand how this works in theory, but I want to see it in action. How can I query the database to get HHWM and LHWM? How do I know if my FTS is scanning the bitmap freelist for the grey area between HHWM and LHWM? I suppose I could derive it using a trace event like 10046 and calculate the number of blocks read, but gives a one-dimensional picture and does not really address my curiosity.

I realize this has little value overall, but I am trying to grasp these essential fundamentals for my own clarification. How do non-Oracle employees know these things if they are not documented? *grin*

On Tue, May 6, 2008 at 11:38 AM, Ric Van Dyke <ric.van.dyke_at_hotsos.com> wrote:

> Charles, looks like you are correct that Oracle has been less then clear
> with what these are. I can find some references to them in a few metalink
> notes, most have to do with error messages and a couple of bugs (like you
> mention in your note). But even reading them you would be less then clear
> on what they mean.
>
>
>
> While I was with Oracle I remember having a talk with some the ST folks
> and this whole low and high water mark stuff came up. Basically the idea
> is that with an ASSM table the blocks are managed quite differently from the
> good old tablespaces of the past. Blocks within a table aren't on one "free
> list" any more there are several of these that manage the space, hence
> PCTUSED is ignored with an ASSM table. Also because of the way ASSM works
> there is a part of the table where there are some blocks that have been used
> mixed in with some blocks that have been used.
>
>
>
> In the past any block below the high water mark was a block that at one
> time had data in it. (Even this is a little be of a fudge, since Oracle
> moves the HWM up by several blocks at a time, it's likely that a few block
> might be below the HWM, but indeed empty.) Life was easy a full table scan
> would read in all the blocks up to the HWM and look for what ever data you
> needed.
>
>
>
> With ASSM blocks are on different free lists, each list will have a
> different amount of free space within the blocks on that list. Because of
> this some of the blocks used fall into this gray area where there are blocks
> that have been used right next to blocks that have never been used. It's
> important to understand that Oracle really has to keep these two types of
> blocks separate because a block that has never been used is unformatted and
> hence would cause all kinds of integrity issues if Oracle tried to read
> these unformatted data blocks. So Oracle now has two high water marks. The
> low high water mark is the point where all blocks below it HAVE BEEN USED,
> that is all of them are formatted blocks and can be read just fine. However
> the gray area is between the low high water mark and the high high water
> mark. This area is managed by a bit map index which shows Oracle which
> blocks have been used and which ones aren't.
>
>
>
> When a full table scan happens in ASSM, all the block below the low high
> water mark are read like normal and then the bit map index is referenced to
> find out which other blocks need to be read.
>
>
>
> Does this help?
>
>
>
> -----------------------
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> -----------------------
>
> *Hotsos Symposium 2009 dedicated to performance and nothing but
> performance*
>
> *March 8 – 12, 2009 in Dallas, Texas*
>
> *Be there.*
> ------------------------------
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 06 2008 - 14:34:48 CDT

Original text of this message