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?
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.
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....
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
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
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
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.
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
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.
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
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?
April 29, 2011 - 8:55 am UTC
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?
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?
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.
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.
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 ?
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?
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
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) .
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
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)
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)?
March 21, 2024 - 12:38 am UTC
hopefully my above bucket metaphor has explained this.
...
TomS, March 19, 2024 - 4:55 pm UTC
March 21, 2024 - 12:38 am UTC
hopefully my above bucket metaphor has explained this.