Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Keith.

Asked: February 12, 2001 - 8:22 pm UTC

Answered by: Tom Kyte - Last updated: March 29, 2009 - 10:57 am UTC

Category: Database - Version: 8.0.5

Viewed 1000+ times

You Asked

What is High-Water Marks ?
What is it used for ??
If I delete all 10000 records from a table, it will talk a long time if i submit a count(*) ??

Can you advise some web-site for DBA ? (other than Oracle.com)

Thanks a lot

and we said...

A high water mark is the set of blocks that have at one pointed contained data. You might have 1000 blocks allocated to a table but only 500 are under the HWM.

The blocks under the HWM are the blocks that will be read when the table is full scanned. So, in your example above - if you put 10,000 record in the table, delete from table and commit -- when we do the count(*) IF WE FULL SCAN THE TABLE, we will hit those empty blocks. If we fast full scan the index -- it'll be different.

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079 <code>for how to find the HWM.


As for web sites -- I'm not sure. Oracle.com is a pretty large place. I assume you've been to technet.oracle.com then?


and you rated our response

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

How to interpret this?

June 01, 2004 - 11:36 pm UTC

Reviewer: Marcio

This was a question from Prometric Simulate Test

A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER) STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6
PCTINCREASE 0);

When you issue the following statement, what will be the size of the table,
if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;

A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB

The question is what would be the size of the table?
As simple as set up a test suppose, but you can interpret what is "if the high-water mark of the table is 200KB?"
Could you explain me?

Assumption:

Odddddddeeeeeeeeeeeeeeeeeeee.. 2m ext, minextents 6, d=12mb, if hwm is 200kb I suppose that was never touched -- so, allocate unused KEEP N will ajust initial to me.
And the answer to me is a) 1000k -- what is wrong accord simulate answer said c) 12mb as the table has been flooded -- that's why would like to interpret "if the hwm of the table is 200kb" is.

ops$marcio@MRP10G> CREATE TABLE MY_TABLE (COL1 NUMBER) STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6
2 PCTINCREASE 0);

Table created.

ops$marcio@MRP10G> exec show_space('MY_TABLE')
Total Blocks............................1536
Total Bytes.............................12582912
Unused Blocks...........................1535
Unused Bytes............................12574720

PL/SQL procedure successfully completed.

ops$marcio@MRP10G> ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;

Table altered.

ops$marcio@MRP10G> exec show_space('MY_TABLE')
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................127
Unused Bytes............................1040384

PL/SQL procedure successfully completed.


Tom Kyte

Followup  

June 02, 2004 - 8:15 am UTC

what a stupid question eh?


my answer is

f) you have given totally insufficient data to even begin answering the question


the reason (F) is the only correct answer is because I'll have created that table in a locally managed tablespace, with uniform extents. The answer set is therefore "infinite" and not knowable by the person asking the question :)




OK

June 02, 2004 - 9:57 am UTC

Reviewer: Marcio

Well, this kind of question evaluate the new OCP (Oracle Certified Professional ;)
But, going to further -- suppose you have a dmt tablespace, which will be your answer and why? -- Please consider (f) as well ;)

A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER) STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6
PCTINCREASE 0);

When you issue the following statement, what will be the size of the table,
if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;

A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB

Thanks

Tom Kyte

Followup  

June 02, 2004 - 11:18 am UTC

answer is definitely "none of the above choices"


ops$tkyte@ORA817DEV> create tablespace silly datafile '/tmp/silly.dbf' size 25m reuse;
Tablespace created.
 
ops$tkyte@ORA817DEV> create table my_table( col1 number ) tablespace silly
  2  storage ( initial 2m next 2m minextents 6 pctincrease 0 );
Table created.
 
ops$tkyte@ORA817DEV> insert into my_table values ( 1 );
1 row created.
 
ops$tkyte@ORA817DEV> insert into my_table values ( 2 );
1 row created.
 
ops$tkyte@ORA817DEV> alter table my_table minimize records_per_block;
Table altered.
 
ops$tkyte@ORA817DEV> insert into my_table select rownum from all_objects where rownum <= 48;
48 rows created.
 
ops$tkyte@ORA817DEV> analyze table my_table compute statistics;
Table analyzed.
 
ops$tkyte@ORA817DEV> select blocks, empty_blocks from user_tables where table_name = 'MY_TABLE';
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
        25         1534
 
1 row selected.

<b>HWM is absolutely 200k</b>
 
ops$tkyte@ORA817DEV> select sum(bytes)/1024 from user_extents where segment_name = 'MY_TABLE';
 
SUM(BYTES)/1024
---------------
          12480
 
1 row selected.

<b>we have 12m allocated...</b>
 
ops$tkyte@ORA817DEV> select extent_id, blocks from user_extents where segment_name = 'MY_TABLE';
 
 EXTENT_ID     BLOCKS
---------- ----------
         0        260
         1        260
         2        260
         3        260
         4        260
         5        260
 
6 rows selected.

<b>in 6 extents...</b>
 
ops$tkyte@ORA817DEV> ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
Table altered.
 
ops$tkyte@ORA817DEV> analyze table my_table compute statistics;
Table analyzed.
 
ops$tkyte@ORA817DEV> select blocks, empty_blocks from user_tables where table_name = 'MY_TABLE';
 
    BLOCKS EMPTY_BLOCKS
---------- ------------
        25          125
 
1 row selected.

<b>HWM is still 200k -- but we have lots less blocks</b>
 
ops$tkyte@ORA817DEV> select sum(bytes)/1024 from user_extents where segment_name = 'MY_TABLE';
 
SUM(BYTES)/1024
---------------
           1208
 
1 row selected.

<b>the answer it 1208Kb -- not in that list - perhaps the 12000kb is a typo, but still "wrongish"</b>

 
ops$tkyte@ORA817DEV> select extent_id, blocks from user_extents where segment_name = 'MY_TABLE';
 
 EXTENT_ID     BLOCKS
---------- ----------
         0        151
 
1 row selected.



 

Too bad explanations

June 02, 2004 - 1:01 pm UTC

Reviewer: A reader

Tom,
You need to answer the questions to the point.

Tom Kyte

Followup  

June 02, 2004 - 1:32 pm UTC

I need to do whatever i want maybe. If you don't like the answer, you don't really have to read them?

The point was "silly question".

The answer is, was, will be "none of the above".


The point is, as asked the question "could not be answered", period.

The point is, as refined, the question doens't have an answer in that list and the point was made.



re: Too bad explanations

June 02, 2004 - 1:52 pm UTC

Reviewer: Marcio

Sorry "a reader" allow me as poster to follow up, to accept the answer... and it is the only valid answer I've found.

Just set it up and you see (create table, fill it until 200k and alter table)...

I had not understood "200k hwm part", but it was explained as well.

This question have (am sure) a typo on list's answers, and such Tom's sample proved it -- 1208k table size.

reducing high water mark with out truncating

July 28, 2006 - 8:26 am UTC

Reviewer: Prasanth PV

Hi Tom,
Is there any method to lower the HWM without doing the truncate table operation.


Thanks And Regards
Prasanth PV.

Tom Kyte

Followup  

July 28, 2006 - 8:00 pm UTC

in 10g, sure.

using the alter table T shrink space compact and shrink space commands.

that would be an online operation.

in 9i and before, alter table T move; would do it - but that is "offline" (table is not useable while you do this) and would require rebuilds of indexes as well.

row chaining

August 11, 2006 - 1:07 am UTC

Reviewer: Prasanth.pv

Hi Tom,
Whether row chaining will reduce the performace of insert?
Regards,
Prasanth.PV

Tom Kyte

Followup  

August 11, 2006 - 10:39 am UTC

ask in one place please. so go look in the other place you cut and pasted this same text.

how to find table HWM if...

March 13, 2007 - 6:33 am UTC

Reviewer: Peter Larsen from Denmark

Hi

Years ago when I was still working with ANALYZE command if was pretty easy to find those tables whose HWM is high and the table is quite empty.

It was done with

BLOCKS
EMPTY_BLOCKS
NUM_ROWS * AVG_ROW_LEN

if NUM_ROWS * AVG_ROW_LEN is much smaller than BLOCKS then the HWM is high and we probably need to rebuild the table.

Can we still apply the same logic with DBMS_STATS?


Tom Kyte

Followup  

March 13, 2007 - 11:29 am UTC

sure, it populates those

August 17, 2008 - 7:31 pm UTC

Reviewer: A reader

Very good explanation Tom.

High water mark

March 24, 2009 - 3:30 pm UTC

Reviewer: Vithal from UK

Hi Tom,

I have table with 1 column with number format and 100 rows. this table is cosuming 160 GB space. when I have asked to DBA's they have told that its highwatermark issue but how can i find what is the high watermark for this table? is there any stadered query for this?
Tom Kyte

Followup  

March 29, 2009 - 10:57 am UTC

you know the high water mark, it is 160gb. You told us how big the table is. The high water mark of a table is simply the largest size it was at some point.


If you only have 100 rows and it is 160gb, it would sort of make sense to fix it.

assuming you are using locally managed tablespaces:
ops$tkyte%ORA11GR1> alter table t1 move storage ( initial 1k next 1k minextents 1 pctincrease 0 );

Table altered.



would shrink it right down. (rebuild all indexes on T1 after this) If you have 10g and are using ASSM (automatic segment space management), you could alternatively:


ops$tkyte%ORA11GR1> alter table t1 enable row movement;

Table altered.

ops$tkyte%ORA11GR1> alter table t1 shrink space compact;

Table altered.

ops$tkyte%ORA11GR1> alter table t1 shrink space
  2  /

Table altered.


that will shrink the space down to nothing as well.