Re: Documentation for HHWM and LHWM?
- From: "조동욱" <ukja.dion@xxxxxxxxx>
- To: sacrophyte@xxxxxxxxx
- Date: Wed, 7 May 2008 09:38:00 +0900
As far as I know, the only valid method to differentiate the LHWM and HHWM
is to dump segment header and see the trace file. There you can see the
location of LHWM and HHWM.
The side effect of holes between LHWM and HHWM is observable via FB lock
contention when concurrent DML is involved. For select, increased scan time
might be the only way to observe the side effect of holes(But not sure).
Dion Cho
2008/5/7, Charles Schultz <sacrophyte@xxxxxxxxx>:
>
> 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@xxxxxxxxxx>
> 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
Other related posts:
