Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Shantanu.

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

Last updated: March 21, 2024 - 12:38 am UTC

Version: 9.0.2

Viewed 100K+ 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 Tom 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>

Rating

  (37 ratings)

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

Comments

Very usefull

karthick, October 17, 2007 - 1:47 am UTC

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.



Kai, October 17, 2007 - 4:05 am UTC

"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

karthick, October 18, 2007 - 9:34 am UTC

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

Ben Kim, July 14, 2008 - 7:27 pm UTC

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
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

Fayyaz -Ahmad, August 24, 2008 - 4:29 am UTC

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
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

Sudarsan Pula, October 16, 2008 - 5:20 am UTC

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

Rohit, October 17, 2008 - 6:23 am UTC

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
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

Sukhijnder Bhullar, February 09, 2009 - 3:40 pm UTC

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

Vijay Bhasksar Dodla, February 26, 2009 - 9:43 am UTC

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
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

Vijay Bhaskar Dodla, March 04, 2009 - 4:28 am UTC

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
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...

Vijay Bhaskar Dodla, March 09, 2009 - 10:26 am UTC

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

Sunil, March 15, 2010 - 5:41 am UTC

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
March 15, 2010 - 11:23 am UTC

HWM Query

Abhisek, March 29, 2010 - 4:44 am UTC

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
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

Suresh, June 01, 2010 - 7:12 am UTC

This answer and the picture is Good.

HIGHWATERMARK

Gautham, June 23, 2010 - 9:41 am UTC

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
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

Gautham, June 23, 2010 - 9:44 am UTC

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
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

Jaid, October 14, 2010 - 10:47 pm UTC

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

Raghav, October 21, 2010 - 3:35 am UTC

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
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

ankur jain, April 29, 2011 - 3:14 am UTC

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

Madhu, May 06, 2011 - 4:52 am UTC

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

Kanu, January 03, 2012 - 6:48 am UTC

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

new inserts go above HWM

Jie, January 05, 2012 - 5:51 pm UTC

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
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

Jie, January 06, 2012 - 9:13 am UTC

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
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

Snehasish Das, February 12, 2012 - 11:34 pm UTC

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
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

Al Ricafort, April 27, 2012 - 8:15 pm UTC

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
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

Jaroslav Tachovsky, March 26, 2013 - 11:11 am UTC

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
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

Khurram, April 14, 2013 - 4:34 am UTC

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
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

Khurram, May 01, 2013 - 10:10 pm UTC

Thanks Tom.

deallocate unused

vsn, October 02, 2013 - 12:58 pm UTC

Do I understand this right then:

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


HWM explanation is great

Ganesh, February 09, 2015 - 2:13 pm UTC

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.

A reader, December 07, 2016 - 1:19 pm UTC


Ram T, November 03, 2022 - 4:47 am UTC

Clear explanation. Better Understood
Connor McDonald
November 03, 2022 - 5:21 am UTC

If you want an indepth look at this, check out my latest podcast

https://connor-mcdonald.com/podcast/

truncat preserve storage

TomS, March 18, 2024 - 12:21 am UTC

You mention HWM being reset by a "truncate" (but no mention of optional "preserve storage") and in a later response that direct load accesses blocks above HWM. Does this mean a direct load using SQL loader to completely reload a table should always be in TRUNCATE mode and never its APPEND mode preceded by a manually executed "truncate preserve storage"? Is "preserve storage" only for SQL INSERTS (non-direct load) .
Connor McDonald
March 18, 2024 - 4:43 am UTC

Yes, that is more or less the case.

Direct load goes above the HWM, no matter where that HWM is.

It is conceivable that someone might want to load direct stuff into an empty table that still as a "non-zero" HWM, and then let conventional inserts later fill in the blanks, but I'd imagine that would be a very niche circumstance.

truncate "REUSE STORAGE" option

TomS, March 19, 2024 - 3:43 pm UTC

Q1a) I've seen others (elsewhere) post with uncertainty whether "TRUNCATE" mode of SQL Loader by default uses "REUSE STORAGE" and also lowers the HWM ? Your thoughts ?
Q1b) Does the answer to Q1a hinge upon whether SQL Loader is executed with in Conventional vs Direct-Load mode? I mention " with uncertainty " in Q1a because those posters seemed to not take this Q1b mode into consideration. My amateur guess is that SQL Loder in direct-load mode with TRUNCATE option would not "REUSE STORAGE" because i think i read in this thread that direct-load always uses unformulated space?

p.s. I am so confused: There seem to be so many permutations (!) My DBA has asked me to review all our data feeds that reload and/or update a lot of data daily and modify to reduce the excessive REDO logging
Connor McDonald
March 21, 2024 - 12:36 am UTC

1a) truncate *always* resets the HWM for the table. To use a metaphor

You have a bucket filled with water.

"truncate bucket reuse storage" means tip out all of the water but YOU keep the bucket, no-one else can use it.

"truncate bucket drop storage" means tip out all of the water AND put the bucket back in the garden shed. You might get to use it again, but someone else (ie, another table) might also grab the bucket for their needs.

But in both cases, the bucket is empty (ie, HWM has been reset)

1b) Mode does not matter, because if you have TRUNCATE in the control file, we are definitely going to truncate that table (and hence reset the HWM).

If you did NOT have truncate in the control file, then conventional mode just inserts as per normal, ie, some rows will go into free space below the HWM, and some rows may go above the HWM as the table is filled. In direct mode, its always above the HWM.


typo fix

TomS, March 19, 2024 - 3:49 pm UTC

i meant to write "unformatted" blocks/space (not "unformulated"-- spell-checker did that)
Connor McDonald
March 21, 2024 - 12:38 am UTC

hopefully my above bucket metaphor has explained this.

...

TomS, March 19, 2024 - 4:07 pm UTC

You say "Direct load goes above the HWM, no matter where that HWM is". Is this synonymous with (my interpretation) "Direct load goes to unformatted blocks (will not reuse preserved space of a truncate)" . (In other words?) Are you saying that after a "truncate reuse storage" the HWM is not lowered (i.e. it marks the border of formatted vs unfromatted blocks)?
Connor McDonald
March 21, 2024 - 12:38 am UTC

hopefully my above bucket metaphor has explained this.

...

TomS, March 19, 2024 - 4:55 pm UTC

Connor McDonald
March 21, 2024 - 12:38 am UTC

hopefully my above bucket metaphor has explained this.