Re: Documentation for HHWM and LHWM?
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>, "K Gopalakrishnan" <kaygopal@xxxxxxxxx>
- Date: Tue, 6 May 2008 14:34:48 -0500
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
- Re: Documentation for HHWM and LHWM?
- From: 조동욱
- Re: Documentation for HHWM and LHWM?