RE: Documentation for HHWM and LHWM?

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 06 2008 - 11:38:45 CDT