RE: Documentation for HHWM and LHWM?
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <sacrophyte@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 May 2008 11:38:45 -0500
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.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Charles Schultz
Sent: Tuesday, May 06, 2008 10:43 AM
To: ORACLE-L
Subject: Documentation for HHWM and LHWM?
Broswing through various blogs, whitepapers and other presentations, I
have seen folks like Tom Kyte and Lutz Hartman reference the HHWM and
LHWM of ASSM tables, however I have been unable to find where they are
officially documented. Perhaps this is a limitation of tahiti and
metalink (metalink gave me one hit for bug 6416822). I have looked
through the Concepts, New Features and DBA books in the documentation
set, learning a lot in the process, and still have a lot to go back
over. *grin* Additionally, I appreciate that dbms_space gives us access
to detailed information about used blocks, but I find it a little odd
that the HWM is not specifically included, much less HHWM and LHWM.
Forgive me if I missed it, but did Oracle ever document these ASSM high
water marks?
Thanks to all of you who have worked hard to expose various internal
tidbits and publish helpful hints.
--
Charles Schultz
Other related posts:
