Re: High water marks

Let me politely disagree -- I think you do need to know this information.

From "Advanced Oracle Tuning and Administration (Oracle Press )", pages 92-94:

 analyze table tablexyz compute statistics;  select blocks from dba_segments where segment_name = "TABLEXYZ";  select empty_blocks from dba_tables where table_name = "TABLEXYZ";

• high water mark = dba_segments.blocks - dba_tables.empty_blocks - 1

There's also a 2nd method available as of version 7.3 which uses the DBMS_SPACE package which is a little more complicated but gives you extra information on extents.

Using DBMS_SPACE is described in both "Advanced Oracle Tuning and Administration (Oracle Press )", pages 92-94 and "Oracle 8 DBA Handbook (Oracle Press)", pages 123-124.

>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 !).
Received on Fri Jun 05 1998 - 13:30:10 CDT