Re: High water marks

From: <mjain_at_my-dejanews.com>
Date: Fri, 05 Jun 1998 18:09:19 GMT
Message-ID: <6l9c8e$d7b$1@nnrp1.dejanews.com>

High Water Mark: When data is written to a table, Oracle records the number of highest block to which the data was written. That block number- the highawtermark-is kept with the table header block information i.e. segment header block of the table.

The highwater mark is used in two different situations. First, when you perform a SQL*Loader Direct Path Load, the highwater mark is used to determine where the data should start to be loaded. The formatted blocks of data generated by SQL*Loader Direct Path are inserted above the high watermark of the table. Second, when you perform a full table scan of a table, Oracle will scan upto the highwater mark block of a table, regardless of the number of blocks ( and number of records ) allocated to the table.

When you delete all the rows of a big table, the Oracle does not reset the Highwater Mark of the table and so if a fullscan is done it has to go upto the the Highwater Mark which will be somewhere towards the end of the segment. So when you query which involves full scan you will have to read upto the Highwater mark which is far away even thought there is not much data in the table. Example of this would be

There is a bigtable with 10 million rows and you deleted all rows of that table using DELETE Command, now if you try to count the number of rows issuing the commnad:-

Select count(*) from bigtable
It will take forever to get the count which in the end will have a zero value.

Truncate command deletes all rows and resets the highwater mark to start of the segment and is also efficient as it does not use redo logs. so it enhances the performance in such situtaions.

After Truncate of bigtable the same query i.e. Select count(*) from bigtable
will be executed fast as the highwater mark was at the beginning of segment for full table scan.

Method to calculate the high water mark:-

There is no system table which containts the high water mark (HWM) information. You can calculate the HWM using the results from the following SQL statements: SELECT BLOCKS FROM DBA_SEGMENTS WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

        ANALYZE TABLE owner.table ESTIMATE STATISTICS;

        SELECT EMPTY_BLOCKS
        FROM   DBA_TABLES
        WHERE  OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

Thus, the tables' HWM = (query result 1) - (query result 2) - 1

Hope this information is helpful to you.

Manoj Jain
Certfied Oracle Professional DBA

In article <343767eb.6243919_at_news.demon.co.uk>,   colin_at_daemon-computing.demon.co.uk (Colin Polykett) wrote:
>
> Folks,
>
> Is there any way to find out what the current high water mark is for
> table ?
>
> I have no real need to know this, except that it's bugging me that the
> high water mark can make such a difference in execution speed of a
> query and yet there's no obvious way to find out what it is (not
> obvious to me anyway !).
>
> TIA.
>
> Colin Polykett.
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Jun 05 1998 - 13:09:19 CDT