Re: High water marks

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Fri, 05 Jun 1998 12:12:17 GMT
Message-ID: <3577dce3.20360745@www.sigov.si>

On Fri, 05 Jun 1998 10:12:31 GMT, 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 !).

If you do "ANALYZE TABLE mytable COMPUTE STATISTICS", this will show you the highwattermark:

SELECT blocks FROM user_tables WHERE table_name = 'MYTABLE';

The name of this column (BLOCKS) is somewhat misleading, as it doesn't show the number of blocks *currently* occupied by the table's data. It rather shows the maximum number of blocks that were *ever* occupied by this table's data since it was created/truncated. Anyhow, when performing the full table scan, oracle will visit this number of DB blocks, even if there is no rows in the table.

There is another column EMPTY_BLOCKS in this view. It shows the number of blocks that were newer used by the table's data.

>TIA.
>
>Colin Polykett.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Jun 05 1998 - 07:12:17 CDT