Skip to Main Content

Breadcrumb

Easter

Question and Answer

Tom Kyte

Thanks for the question, Shantanu.

Asked: October 15, 2007 - 3:43 pm UTC

Answered by: Tom Kyte - Last updated: April 22, 2013 - 6:55 pm UTC

Category: Database - Version: 9.0.2

Viewed 50K+ times! This question is

You Asked

What is the main purpose of white watermark and in a case secnario where all the records have been deleted and we try to lookup that table why does the data is looked untill it reaches to an end of whte watermark

and we said...

I believe you mean "high watermark"

The database doesn't know what is on a block unless and until......

It goes to the block.

So, when you delete the information, the block is still "a block", it is just a block that once had active rows - but no longer does.

And when you full scan the table - we have to read all blocks that at any time contained data - because - they could contain data now. We won't know what is there until we read it.

Suppose you deleted all but ONE row - we have to look in every block (unless of course we use an index) to see if that row is on a given block.

<quote src=expert oracle database architecture>

High-water Mark


This is a term used with table segments stored in the database. If you envision a table, for example, as a 'flat' structure or as a series of blocks laid one after the other in a line from left to right, the high-water mark (HWM) would be the rightmost block that ever contained data, as illustrated in Figure 10-1.
+---- high water mark of newly created table
|
V
+--------------------------------------------------------+
|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

      high water mark after inserting 10,000 rows
                                    |
                                    v
+--------------------------------------------------------+
|x |x |x |x |x |x |x |x |x |x |x |x |  |  |  |  |  |  |  |
|x |x |x |x |x |x |x |x |x |x |x |x |  |  |  |  |  |  |  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+


      high water mark after inserting 10,000 rows
                                    |
                                    v
+--------------------------------------------------------+
|x |x |x |x |x |x |x |  |  |  |  |  |  |  |  |  |  |  |  |
|x |x |x |x |x |x |x |  |  |  |  |  |  |  |  |  |  |  |  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Figure 10-1. Depiction of an HWM

Figure 10-1 shows that the HWM starts at the first block of a newly created table. As data is placed into the table over time and more blocks get used, the HWM rises. If we delete some (or even all) of the rows in the table, we might have many blocks that no longer contain data, but they are still under the HWM, and they will remain under the HWM until the object is rebuilt, truncated, or shrunk (shrinking of a segment is a new Oracle 10g feature that is supported only if the segment is in an ASSM tablespace).

The HWM is relevant since Oracle will scan all blocks under the HWM, even when they contain no data, during a full scan. This will impact the performance of a full scan¿especially if most of the blocks under the HWM are empty. To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows), and then execute a SELECT COUNT(*) from this table. Now, DELETE every row in it and you will find that the SELECT COUNT(*) takes just as long (or longer, if you need to clean out the block! Refer to the 'Block Cleanout' section of Chapter 9) to count 0 rows as it did to count 1,000,000. This is because Oracle is busy reading all of the blocks below the HWM to see if they contain data. You should compare this to what happens if you used TRUNCATE on the table instead of deleting each individual row. TRUNCATE will reset the HWM of a table back to 'zero' and will truncate the associated indexes on the table as well. If you plan on deleting every row in a table, TRUNCATE¿if it can be used¿would be the method of choice for this reason.

In an MSSM tablespace, segments have a definite HWM. In an ASSM tablespace, however, there is an HWM and a low HWM. In MSSM, when the HWM is advanced (e.g., as rows are inserted), all of the blocks are formatted and valid, and Oracle can read them safely. With ASSM, however, when the HWM is advanced Oracle doesn¿t format all of the blocks immediately¿they are only formatted and made safe to read upon their first use. So, when full scanning a segment, we have to know if the blocks to be read are 'safe' or unformatted (meaning they contain nothing of interest and we do not process them). To make it so that not every block in the table need go through this safe/not safe check, Oracle maintains a low HWM and a HWM. Oracle will full scan the table up to the HWM¿and for all of the blocks below the low HWM, it will just read and process them. For blocks between the 'low HWM' and the HWM, it must be more careful and refer to the ASSM bitmap information used to manage these blocks in order to see which of them it should read and which it should just ignore.
</quote>

and you rated our response

  (31 ratings)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

Very usefull

October 17, 2007 - 1:47 am UTC

Reviewer: karthick from india

The sketch that you have given is very help full, explains exactly what we want to know.

"With ASSM, however, when the HWM is advanced Oracle doesn¿t format all of the blocks immediately they are only formatted and made safe to read upon their first use"

1. Can you also draw a sketch for explaining the above statement and low HWM.

2. HWM is increased when we insert data. But why not it¿s decreased automatically when we delete data. Why it waits for us to rebuild the table. Any specific reason.



October 17, 2007 - 4:05 am UTC

Reviewer: Kai from Germany

"HWM is increased when we insert data. But why not it¿s decreased automatically when we delete
data. Why it waits for us to rebuild the table. Any specific reason."

In order to set the HWM back after a delete, it would have to full scan to find the last occupied block and check if it has to lower the HWM. Not something you want to do after every delete, especially considering you can only lower if you just deleted the very last row.

It makes sense to wait for an ALTER TABLE SHRINK SPACE so it can full scan once and even move rows from the back of the table to free space in the front so it can lower the HWM as far as possible.

Thanks Kai

October 18, 2007 - 9:34 am UTC

Reviewer: karthick from india

Kai,

What i understand from your explanation is lets say my data are arranged in a block like this

^ - Refferes to HWM

1 2 3 4 5 6
^

Now i delete 2 and 3

1 4 5 6
^

Now i insert 7

1 4 5 6 7
^
Now i give the command on this table

ALTER TABLE SHRINK SPACE

1 4 5 6 7
^

Got your point. Thank's for the answer.

HHWM and LHWM in ASSM

July 14, 2008 - 7:27 pm UTC

Reviewer: Ben Kim from Chicago, IL USA

Tom,
Thank you so much for sharing your deep knowledge on Oracle databases. I've heard many people taking about high highwater mark and low highwater mark in ASSM and wonder how it works. Can you please share it with us?
Tom Kyte

Followup  

July 15, 2008 - 9:36 am UTC

in manual segment space management - during a full scan, all blocks under the high water mark are read and processed.

in automatic segment space management (ASSM), during a full scan, all blocks under the "low high water mark" are read and processed - blocks between this low high water mark and the 'high water mark' may or may not be formatted yet (we format them as we use them in ASSM). Therefore, we need to check them before we process them.

blocks between Low HWM and HWM are not formated

August 24, 2008 - 4:29 am UTC

Reviewer: Fayyaz -Ahmad from ENBD-Company -Dubai

I want to know that why blocks between low HWM and HWM are may or may not be formated.

if these are not formated then why . becuase hight water means there was data , so it should be formated only we are deleting data not disturbing the formate of the block.
Tom Kyte

Followup  

August 26, 2008 - 8:11 pm UTC

because that is the algorithm, the way it works, the design.

Automatic segment space management works that way, by design, on purpose. It is what it is.


we advance the high water mark, format and populate the bitmap blocks if needed to manage this space and then format them as we use them. That is all

It is very clear explanation

October 16, 2008 - 5:20 am UTC

Reviewer: Sudarsan Pula from Singapore

I was panic when I was asked about HWM in an interview but this information made me comfortable in just 10 mins.

Thnaks tom.

Great

October 17, 2008 - 6:23 am UTC

Reviewer: Rohit from INDIA

Thanks you so much tom. You made my life easy. I have one doubt which is making my life hell infact the below question was asked during my interview. The question is:
Suppose I have three tables:
1)customer
2)supplier
3)order
In customer we have two columns one is customer name,customer id.
In supplier we have customerid,orderId
In order we have orderid and quantity.
Now I want to see the 10 person name who has placed the maximum orders(qty). and who havenot placed the orders.
Is it possible through unions? I am sure that by using joins it can be done but dont know how....
Tom Kyte

Followup  

October 18, 2008 - 8:09 pm UTC

first, you must reply to the interviewer (in a nice way) that

"you realize of course your question is very ambiguous, you must clarify what you want before we can answer. For you see, anytime - ANYTIME - you ask for the 'top n', you must be specific about what you mean.

What if 100 customers all have 1,000 orders - and what if there are only 100 customers? Which 10 do you want to see. So, before we can proceed, you must tell us if:

a) you want a random 10 customers that happen to have purchased the 'most' stuff. Realize of course that if you run the report and I run the report - against the same data - we could well get radically different answers - because we are getting basically the random records....

b) you want the set of people that have a number of orders that is in the top ten size of orders. That is, we can count how many orders each customer has made, distinct those counts, take the top ten of those counts and return that set of customers

Of course, there are other things you might have meant, but given the specification you gave, we have insufficient knowledge to return an answer!"


Now, once you have that discussion, you can offer up an answer. I'll assume what they really meant was the "set of people making the top ten count of orders" which is what most people mean (eg: the set might be between 0 and ALL of the records in the customer table!!)

If the answer was "random first 10 you happen to hit, use row_number() where I used dense_rank()....


ops$tkyte%ORA10GR2> create table customer
  2  as
  3  select rownum id, 'customer ' || rownum name
  4    from all_objects
  5   where rownum <= 100;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create sequence order_seq;

Sequence created.

ops$tkyte%ORA10GR2> create table customer_orders( cust_id number, order_id number );

Table created.

ops$tkyte%ORA10GR2> declare
  2          l_rows int;
  3  begin
  4          for x in (select * from customer where mod(id,25) <> 0)
  5          loop
  6                  l_rows := dbms_random.value( 1, 255 );
  7                  insert into customer_orders
  8                  select x.id, order_seq.nextval
  9                    from (select * from dual connect by level <= l_rows);
 10          end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select *
  2    from (
  3  select id, name, cnt, dense_rank() over (order by cnt DESC) dr
  4    from (
  5  select customer.id, customer.name, count(customer_orders.order_id) cnt
  6    from customer, customer_orders
  7   where customer.id = customer_orders.cust_id(+)
  8   group by customer.id, customer.name
  9         )
 10             )
 11   where dr <= 10 or cnt = 0
 12   order by decode( cnt, 0, 1, 0 ), dr
 13  /

        ID NAME                                  CNT         DR
---------- ------------------------------ ---------- ----------
         9 customer 9                            252          1
        92 customer 92                           252          1
         5 customer 5                            251          2
        10 customer 10                           248          3
        15 customer 15                           247          4
        28 customer 28                           247          4
        96 customer 96                           247          4
        29 customer 29                           246          5
        77 customer 77                           244          6
        55 customer 55                           242          7
        94 customer 94                           237          8
        11 customer 11                           236          9
        36 customer 36                           233         10
        75 customer 75                             0         82
        25 customer 25                             0         82
        50 customer 50                             0         82
       100 customer 100                            0         82

17 rows selected.



cnt = 0 are people without orders
others are people with orders ranked by how many they have made.... Note the ties!!!

HWM

February 09, 2009 - 3:40 pm UTC

Reviewer: Sukhijnder Bhullar from NL

It was really helpful content to understand the high water mark and low water mark concepts.

Thanks Tom.

LHWM Vs HWM for segments hosted on ASSM enabled tablespaces

February 26, 2009 - 9:43 am UTC

Reviewer: Vijay Bhasksar Dodla from India

Dear Tom,

I am trying to grasp the concept about low High-watermark(LHWM) and HWM (block formatting and all) which you explained at the very onset of this
blog (for ASSM managed segments). Further, I had also gone through writings on this topic in your famous manuscript: Expert Oracle Database Architecture. But, unfortunately, I am still "struggling" to get to skin of it. Maybe, my basic understading is not sound. However, the picture is very clear for case of MSSM tablespaces though.

I have following questions to ask:-

1) How many times can a block gets formatted? Is it just once or more? If I have acquired a block afresh, Oracle formats it before I can use it. After using it, when I "delete" all the stuff located in this block then will this block be regarded as SAFE or not(to READ) ?

2) For ASSM tablespaces, you said that blocks are formatted as and when they are first used. If it's so, then shouldn't the blocks between LHWM and HWM are all formatted? I think, they should and therefore, no ambiguity (safe/not-safe) should exist when reading these blocks. Please correct me, if I'm wrong.

3) For MSSM tablespaces, you said that blocks are formatted the moment they acquired/allocated to underlying table segment. However, Oracle says the blocks above HWM are unformatted while those below are. How can this situation happen? If a new segment acquires (so, all formatted) a new/initial extent(comprising 5 blocks,say) and out of which only one is used, the HWM points to this blocks. The other 4 blocks are above HWM. But, even all these are formatted.

4) Any idea, what does the block formatting process involve?

Hope, I could explain what I wanted. Apologies if it lacks clarity.

Your expert responses to above would be highly appreciated.

Kind Regards,
Vijay
Tom Kyte

Followup  

March 03, 2009 - 7:57 am UTC

1) once. If you delete everything from it, it is still a formatted block.

2) there would be no LWHM if that were so, would there be?

Ok, consider this:

All blocks are 100% full below the HWM, all of them are. we insert a new record. There is NO SPACE below the HWM, we need more blocks. So, we go to the extent and allocate another section of blocks (say 10 of them). We advance the HWM to include these 10 new blocks. The LHWM is now pointing where the OLD HWM was. The new HWM was advanced 10 blocks and between the LHWM and the HWM are 10 blocks - that HAVE NOT BEEN TOUCHED yet. They are "unsafe", not formatted. We take one of them and format it and put our new row on it. Now there are nine unsafe blocks and 1 safe block between the LHWM and the HWM. Eventually these other nine blocks will fill up with data and we'll do it all over again.


3) as we grab new blocks to put below the HWM, we formatted them.


4) setting up the block structure, the header, the footer, etc.

Quite clear now

March 04, 2009 - 4:28 am UTC

Reviewer: Vijay Bhaskar Dodla from India

Tom,

Thanks for providing the explanation. It's much clear now.
However, with reference to your response to query(#2) as given above, I want to know:

2.1) Assume that an extent comprises of 8 DB blocks and that, all blocks below HWM are 100% FULL. Now, when HWM is advanced(to accomodate new inserts) and as a new extent is acquired, will all the 8 blocks be put below HWM?. If it's NO then, I reckon only some of the blocks (determined by internal algorithm) are put below HWM and rest will be present above HMW (as represented by EMPTY_BLOCKS). Please comment on this.

2.2) You commented at the end, in response to Q #2, that between LHWM and HWM, one(safe) and nine(unsafe) blocks would be left, after performing the transaction. In this case, wouldn't Oracle extend LHWM by one more block to include newly formatted/SAFE block beneath LHWM and thereby reducing gap between LHWM and HWM to just 9 blocks and not 10. Further, if this happens, then ambiguity (safe/unsafe) would also be eliminated when full-scanning blocks between LHWM and HWM for these blocks would certainly be unsafe and those below LHWM are safe to read. This scenario is again more or less the same as in MSSM. Please comment on this.

Best Regards,
Vijay

Tom Kyte

Followup  

March 04, 2009 - 1:23 pm UTC

... and as
a new extent is acquired, will all the 8 blocks be put below HWM? ...

it is called ASSM - automatic segment space management, it does what it wants to do. Might all 8 go below the high water mark? maybe, maybe not - it doesn't matter, it is internal, undocumented subject to change and - this is important - doesn't affect our ability to understand what is happening. All we need to know is that when all of the blocks below the HWM are full and we insert a new row, the HWM will advance some number of blocks - and the LHWM (low high water mark) will advance to be where the OLD HWM was.


When the HWM advances into a new extent, some of the blocks (at least one) from that extent will be placed under the HWM, some above (possibly zero).


2) you assume that the block I was talking about was the "first" block. It need not be, it could have been the middle or the last block. ASSM "sprays" data around, spreads it out. It does this to promote concurrency over "maximum space utilization". It does not have to be the first block.

Excellent...

March 09, 2009 - 10:26 am UTC

Reviewer: Vijay Bhaskar Dodla from India

Dear Tom,

Thanks a ton for all your time and apologies, if I've troubled your peace by posing questions such as these.

The concept is now absolutely clear to me and this site played great role in achieving that. I can't imagine what would happen, if it's not there otherwise.

Once again, thanks for all your help to Oracle community.
It simply marvellous !

Kindest Regards,
Vijay

March 15, 2010 - 5:41 am UTC

Reviewer: Sunil from India

Hi Tom,
I have to say, you're explanation was extremely useful and helped clear some of my doubts. I have a few questions regarding HWM enqueues when using ASSM tablespaces.

1) What are the circumstances in which ASSM tablespaces might encounter HWM enqueues in? Will it happen when the tablespace contains too many LOGSEGMENTs?

2) How can the enqueues be resolved? I read somewhere that increasing the Next Extent size helps. Is this applicable even when using ASSM with locally managed Extents?

Any help in this regard would be appreciated.

Thanks in advance.

Regards
Sunil
Tom Kyte

Followup  

March 15, 2010 - 11:23 am UTC

http://orainternals.wordpress.com/2008/05/16/resolving-hw-enqueue-contention/

has a pretty good write up

HWM Query

March 29, 2010 - 4:44 am UTC

Reviewer: Abhisek

Hi Tom,

I got one query to calculate the HWM for any table:

SELECT BLOCKS
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER(TABLE_NAME);

SELECT EMPTY_BLOCKS
FROM USER_TABLES
WHERE TABLE_NAME = UPPER(TABLE_NAME);

HWM =(query result 1) - (query result 2) - 1


Now I have got the results as:

Table | USER_SEGMENTS_BLOCK | USER_TABLES_EMPTY_BLOCKS| HWM
__________________________________________________________

EMPLOYEE | 1024 | 0 | 1023

So does that mean that 1023 blocks are above .. and can be reset if I try to use the measures to reset HWM like MOVE command.

Can you please interpret the result and query for me once. I am not able to get it correctly.
Tom Kyte

Followup  

April 05, 2010 - 9:45 am UTC

it would be nice if things lined up - in order to be readable...

do not use empty_blocks, it is populated only by ANALYZE, not by dbms_stats and we all know that you do not use analyze anymore to gather statistics - so empty_blocks will never show you anything useful.

And further you MIGHT be able to lower the HWM by moving, or you might not touch the HWM, or you might make the HWM higher. eg: one of three things will happen if you reorganize the table -

it will get LARGER
it will not change at all
it will get smaller

with the data you have provided, no one can say which will happen.


use dbms_space to see how space is used in a segment, it'll show you everything you need to know - number of blocks, number of blocks used, number of blocks never used, and other useful things.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5350053031470#1752788300346387205

High-water Mark

June 01, 2010 - 7:12 am UTC

Reviewer: Suresh from INDIA

This answer and the picture is Good.

HIGHWATERMARK

June 23, 2010 - 9:41 am UTC

Reviewer: Gautham from INDIA

Hi Tom,

First, I would like to thank you a lot for maintaining this site.

As I am completely new to DBA,My questions may sound simple, but I appreciate if you could clear my doubts.

Consider the following example.

+---- high water mark of newly created table
|
V
+--------------------------------------------------------+
| | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

high water mark after inserting 10,000 rows
|
v
+--------------------------------------------------------+
|x |x |x |x |x |x |x |x |x |x |x |x | | | | | | | |
|x |x |x |x |x |x |x |x |x |x |x |x | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+


high water mark after deleting 5,000 rows
|
v
+--------------------------------------------------------+
|x |x |x |x |x |x |x | | | | | | | | | | | | |
|x |x |x |x |x |x |x | | | | | | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

1)Suppose if i want to insert new rows,will oracle inserts that rows in to the free blocks which are under HWM or free blocks above high water mark.

2)The free blocks under the HWM comes under free space(i.e.. dba_free_space) or it is under used space (dba_segments)

3)After deleting some of the rows in the table,I am not able to resize the datafile.Can you explain me the reason for this with example?





I found this query in meatalink.

select a.tablespace_name
,a.file_name
,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a
,(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b
,dba_extents c
,(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name
/

When i run this query i am getting following output...


TABLESPACE_NAME FILE_NAME HIGHWATER
--------------- --------------------------------------------
ABC /sssss/datafile11/dddd/cdddep 5263917056

XYZ /SP0ssss_erpt/ssss/clarerep 5420154880




Tom Kyte

Followup  

June 24, 2010 - 6:50 am UTC

1) if you use a normal insert, it will use the free space below the high water mark (HWM) before using space above the HWM.

If you use a direct path load (sqlldr direct=y, insert /*+ APPEND */ as select), then we only load above the existing HWM and will not reuse the existing free space (but any subsequent normal inserts would - even after the direct path load)


2) the space - all of it - above and below the HWM of the segment - will NOT be in dba_free_space. The only thing in dba_free_space is space that is free to be allocated to any segment. Obviously, if this segment owns these blocks - the only segment that can use them is - that segment. They are not "free" to be used by anything else. It will be allocated, used space.

3) read
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899



I don't know what you want me to do with that query - so I'll just skip it for now :)

HWM

June 23, 2010 - 9:44 am UTC

Reviewer: Gautham from INDIA

Hi Tom,

Sorry for not clarifying my question on above query.

Please explain,If i delete some rows from a table related to 1st daatafile,will the HWM value changes or Reorg of that table is required.

Tom Kyte

Followup  

June 24, 2010 - 6:51 am UTC

a reorganization is NOT required after a delete, it would be rare for you to even consider a reorg after a delete. Space is managed quite well by the database, it is what it in general was "born to do"

Most useful

October 14, 2010 - 10:47 pm UTC

Reviewer: Jaid from India

Hi all,

This overall session is very useful and fruitful, it covers all the require point

Thanks a lot
Jaid

Low and High Water Marks

October 21, 2010 - 3:35 am UTC

Reviewer: Raghav from India

Tom
As ususal, the information you provid is of great help to understand the concepts in detail.

I need few clarifications regarding LHWM and HWM to get more clear picture.

1. we can depict the low and high water marks after allocating new blocks for insertion of new rows as below. Is that right?
| |
v v
+--------------------------------------------------------+
|x |x |x |x |x |x |x | | | | | | | | | | | | |
|x |x |x |x |x |x |x | | | | | | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
lhwm hwm


2. At the time of direct insert, the data will be loaded above the HWM.
My question is, after direct insert whether the low and high water marks are updated and takes new positions? If yes, how we can depict in the below diagram?
| |
v v
+--------------------------------------------------------+
|x |x |x |x |x |x |x | | | | | | x | x | x | | | |
|x |x |x |x |x |x |x | | | | | | x | x | x | | | |
+--+--+--+--+--+--+--+--+--+--+--+--+---+---+---+--+--+--+
lhwm hwm


3. After deleting some rows (used * to depict deleted), the HWM is not changed. What about LHWM? Whether it will be taking new place?

high water mark after deleting some rows
| |
v v
+--------------------------------------------------------+
|x |x |x |x |x |x |x |* |* |* |* |* | | | | | | | |
|x |x |x |x |x |x |x |* |* |* |* |* | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
lhwm hwm

Thanks & Regards
Raghav
Tom Kyte

Followup  

October 25, 2010 - 3:15 pm UTC

(1) yes
(2) the lhw and hwm would both be advanced.
(3) high water marks (and low) need a reorg of some sort to be lowered... delete will not move them

dear tom please please kindly tell

April 29, 2011 - 3:14 am UTC

Reviewer: ankur jain from india

what the meaning of this line only with ASSM, however, when the HWM is advanced Oracle doesn¿t format all of the blocks immediately¿they are only formatted and made safe to read upon their first use.?

i m confuse hwm is only advanced when we insert row but if we insert dat means oracle has to format the blocks allocated for the insertion all but its being said that oracle not format all of the block but on read upon first use. i m not getting its meaning. when we can say blocks get first use?

Most Useful

May 06, 2011 - 4:52 am UTC

Reviewer: Madhu from India

Hi Tom,
You are lighting up lot of dark cells in our minds with oracle knowledge by clearing their doubts.It's good that we have you to support us.

Thanks a lot.

Regards,
Madhu

Nice explanation

January 03, 2012 - 6:48 am UTC

Reviewer: Kanu from India

Tom ..after reading your blogs and answers..it answers all doubts

new inserts go above HWM

January 05, 2012 - 5:51 pm UTC

Reviewer: Jie from St Louis, MO

it is asked that:Suppose if i want to insert new rows,will oracle inserts that rows in to the free blocks which
are under HWM or free blocks above high water mark.

Answer is: if you use a normal insert, it will use the free space below the high water mark (HWM) before using space above the HWM.

Now I have a tablespace that has about 20 tables in it. New data goes into these tables constantly. We are only required to keep the latest 6 months worth of data so each month we archive the oldest data and delete these records. This created holes under HWM in the segments. When all the datafiles expanded to the max size allowed by the disk storage, we got the ORA-01654: unable to extend **** by 8192 in tablespace ***. Why didnot the new records go into those holes?
Tom Kyte

Followup  

January 06, 2012 - 4:42 am UTC

Why didnot the new records go into those holes?

perhaps you were direct path inserting. (insert /*+ append */), you don't tell us precisely what you were doing.

perhaps you really did run out of space. it is possible, volumes increase over time.

perhaps your pctfree settings are such that the space wouldn't be reused YET.

perhaps over time your blocks have become intermingled such that there is really old, sort of old, not so old, pretty new, new, really new data on them. When you delete "really old", you delete a very small number of rows from many blocks - instead of a lot of rows from a few blocks (this would lead to "not enough space on this block YET to insert into)

I'll guess most likely that last one.

In order to archive like that - the common (and 'best practice') way would be to utilize partitioning. Instead of issuing a delete (delete being just about the most expensive statement to ever execute), you would simply truncate/drop an old partition. No undo, no redo, no space management issues at all.

re

January 06, 2012 - 9:13 am UTC

Reviewer: Jie from St Louis, MO

Those inserts are normal non-direct-load inserts. And all table has pctfree set to 10%. We ran segment advisor in grid control and every table has some reclaimable space, some as high as about 20%. Every time we delete old records, we get rid of a month worth of data so I would say most likely these records tend to be continuous within a block. Is there a script that I can run to analyze how these to-be-deleted records are distributed before my next monthly deletion?
Tom Kyte

Followup  

January 10, 2012 - 9:40 pm UTC

so it sounds like this:

perhaps over time your blocks have become intermingled such that there is really old, sort of old, not so old, pretty new, new, really new data on them. When you delete "really old", you delete a very small number of rows from many blocks - instead of a lot of rows from a few blocks (this would lead to "not enough space on this block YET to insert into)

you can use my show_space script (or just dbms_space directly) to see how many blocks in the table have sufficient free space to be reused.

it would be easier by far to just let us allocate a little bit more space.

Parallel Insert and HWM

February 12, 2012 - 11:34 pm UTC

Reviewer: Snehasish Das from USA

Hi Tom,

I have seen that the parallel DML (Insert) writes above the HWM .It seems only the serial Insert only uses the space below HWM. Database version is 10.2.0.5.
SQL >select segment_name,sum(bytes)/(1024*1024) from user_segments where segment_name = 'W_UKB_BNC_WRKG_STG1_DROP' group by segment_name;

SEGMENT_NAME |SUM(BYTES)/(1024*1024)
---------------------------------------------------------------------------------|----------------------
W_UKB_BNC_WRKG_STG1_DROP | 445

1 row selected.

Elapsed: 00:00:06.87
SQL >delete from W_UKB_BNC_WRKG_STG1_DROP where rownum < 1200000;

1199999 rows deleted.

Elapsed: 00:03:04.92
SQL >commit;

Commit complete.

Elapsed: 00:00:00.51
SQL >select segment_name,sum(bytes)/(1024*1024) from user_segments where segment_name = 'W_UKB_BNC_WRKG_STG1_DROP' group by segment_name;

SEGMENT_NAME |SUM(BYTES)/(1024*1024)
---------------------------------------------------------------------------------|----------------------
W_UKB_BNC_WRKG_STG1_DROP | 445

1 row selected.

Elapsed: 00:00:03.99
SQL >insert /*+ PARALLEL(W_UKB_BNC_WRKG_STG1_DROP,2) */ into W_UKB_BNC_WRKG_STG1_DROP select /*+ PARALLEL(W_UKB_BNC_WRKG_STG1,2) */ * from edw_perf.W_UKB_BNC_WRKG_STG1 where rownum < 1200000;

1199999 rows created.

Elapsed: 00:00:11.95
SQL >commit;

Commit complete.

Elapsed: 00:00:02.70
SQL >select segment_name,sum(bytes)/(1024*1024) from user_segments where segment_name = 'W_UKB_BNC_WRKG_STG1_DROP' group by segment_name;

SEGMENT_NAME |SUM(BYTES)/(1024*1024)
---------------------------------------------------------------------------------|----------------------
W_UKB_BNC_WRKG_STG1_DROP | 483.375

1 row selected.

Elapsed: 00:00:02.22
SQL >
SQL >
SQL >alter table W_UKB_BNC_WRKG_STG1_DROP move;

Table altered.

Elapsed: 00:02:21.94
SQL >
SQL >select segment_name,sum(bytes)/(1024*1024) from user_segments where segment_name = 'W_UKB_BNC_WRKG_STG1_DROP' group by segment_name;

SEGMENT_NAME |SUM(BYTES)/(1024*1024)
---------------------------------------------------------------------------------|----------------------
W_UKB_BNC_WRKG_STG1_DROP | 449

1 row selected.

Elapsed: 00:00:02.48

I tried to search for oracle docs relating to this, but could not find anything in perticular to help me regarding this.

Regards,
Snehasish Das.
Tom Kyte

Followup  

February 13, 2012 - 8:11 am UTC

parallel only does direct path loading - period. Parallel is always direct path and direct path is always above the high water mark.

each parallel execution server will allocate its own extent to load into.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables004.htm#sthref2200

Index and High Water Mark

April 27, 2012 - 8:15 pm UTC

Reviewer: Al Ricafort from Hong Kong

Hi Tom,

The Oracle doc says that "The high water mark is the boundary between used and unused space in a segment." My questions now are:

1) Since index has its own segment does that mean it also maintains a HWM?

2) If yes then what is it use for? Is it only used when allocating new blocks?

3) If again (1) is yes, is HWM used when reading the index blocks? I assume not because index is basically a linked list so the last block in the list should tell Oracle that no more blocks to read.


Tom Kyte

Followup  

April 30, 2012 - 8:00 am UTC

1) yes

2) yes, just like any other segment. we allocate extents to segments, we start using blocks from the extent as needed until all blocks in the extent are used - then we add a new extent.

3) during an index fast full scan - when we use multiblock IO to just read blocks of the index in any order (we read them "unsorted") - we use the high water mark to determine when to stop reading.

some test case

March 26, 2013 - 11:11 am UTC

Reviewer: Jaroslav Tachovsky from Hradec Kralove, Czech Republic

Hi,
I did some tests on count(*) of full and then empty table to compare times. It shows that delete from ... will not speed count(*), nor stat collection helps. Only truncate will work. All commands were run twice to prevent caching influence:
SQL>set timing on 
SQL>select count(*) from test1; 
  COUNT(*) 
---------- 
   7059968 
Elapsed: 00:00:03.04 
SQL>delete from test1; 
7059968 rows deleted. 
Elapsed: 00:02:24.76 
SQL>select count(*) from test1; 
  COUNT(*) 
---------- 
        0 
Elapsed: 00:00:03.77 
SQL>exec dbms_stats.gather_table_stats('PARA','TEST1'); 
PL/SQL procedure successfully completed. 
Elapsed: 00:00:05.54 
SQL>select count(*) from test1; 
  COUNT(*) 
---------- 
         0 
Elapsed: 00:00:03.31 
SQL>truncate table test1; 
Table truncated. 
Elapsed: 00:00:08.47 
SQL>select count(*) from test1; 
  COUNT(*) 
---------- 
         0 
Elapsed: 00:00:00.01 

I have a question - when I compute stats (after delete, I dont do truncate) then all plans consider that table is small and therefore full scan will be done. But full scans will 'go through previously filled blocks. Some index access would be faster but will not happen here. Does this have some nice solution ?

Tom Kyte

Followup  

March 27, 2013 - 3:18 pm UTC

if there is an index on a not nullable column (eg: a primary key index), it should be doing a fast full scan of that. So, it should be using an index - it probably is if you have an index on a not nullable column.

but you show no plans :(

and you are using elapsed time which is not very useful (you should use sql_trace or at least autotrace to see the work being done)

and you might consider using alter table t shrink space on a table you know was very large and you have made very small all of a sudden.

or using partitioning to avoid filling and deleting and filling and deleting a table over and over. delete is the most expensive statement to execute we have.

the statement:

when I compute stats (after delete, I dont do truncate)
then all plans consider that table is small and therefore full scan will be
done.


is not true, the cost of the full scan involves the number of blocks to be read - not the number of rows in the table. The number of rows returned by the full scan can affect subsequent row sources, but just because there are zero rows in 1,000,000 blocks doesn't make the full scan "cheap"


LHWM and HWM ASSM

April 14, 2013 - 4:34 am UTC

Reviewer: Khurram from Australia

freelist are no more applicable in ASSM to enhance concurrency.PCTUSED is also no more usable in ASSM , how a block is set to be free and not free without usage of PCTUSED?
Tom Kyte

Followup  

April 22, 2013 - 6:55 pm UTC

freelists are used, they are bitmap freelists stored in the segment itself - not as a structure outside the segment as they are in manual segment space management. so there are freelists, just of a different type.


pctused is not used to determine when a block can be reused - rather we track whether a block is 0-25% full, 25-50%, 50-75%, 75-100% or full and we use that information (contained in the freelists!) to decide internally which block we would like to stuff new data into.

HWWM freelist

May 01, 2013 - 10:10 pm UTC

Reviewer: Khurram from Australia

Thanks Tom.

deallocate unused

October 02, 2013 - 12:58 pm UTC

Reviewer: vsn from UK

Do I understand this right then:

Does ALTER ... DEALLOCATE UNUSED
deallocate space between the 'low' HWM and the HWM then?


HWM explanation is great

February 09, 2015 - 2:13 pm UTC

Reviewer: Ganesh from INDIA

Thanks a lot Tom.

The perfect understanding in a just a single read. This was very useful for me during the tuning part of the database. Thanks once again.

December 07, 2016 - 1:19 pm UTC

Reviewer: A reader