| Getting to Know HWM [message #59131] |
Thu, 30 October 2003 13:17  |
N.A. Jam
Messages: 23
Registered: January 2003
|
Junior Member |
|
|
Hello fellows.
Can someone explain why it is necessary to keep watch of HWM, when is HWM value is unacceptable, what effects it has on performance?
Thanks.
|
|
|
|
| Re: Getting to Know HWM [message #59134 is a reply to message #59131] |
Thu, 30 October 2003 22:41  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
HWM is actually a location of a segment which is the highest level of block ever touched by the data. Even after deleting the data too the HWM does not decreased.
And it is important to take a look on the HWM of the table bcz whenever a FTS(Full Table Scan) is performed on the table then oracle reads the data upto the HWM of that table.And there may be a huge difference between the actual data and HWM of the table which increase I/O and degrade performance.
Lets take a small and simple exaple : You insert 1000 records in a table (assuming that the table is empty) then that 1000 records will be the HWM of the table (for understanding we are taking record actually its block) than u delete 900 records from the table.Now table contain only 100 records but the HWM is still 1000. So whenever you perform FTS on table oracle will read 1000 records 100 with data and 900 without data.
I hope it will help u.
|
|
|
|
|
|
|
|
| Re: Getting to Know HWM [message #59144 is a reply to message #59140] |
Fri, 31 October 2003 08:14 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
briefly,
i) Export,truncate/drop,import
ii) Move the table
iii) CTAS another table
Let me show how MOVE resets the HWM:
SQL> create table t as select * from all_objects where rownum < 4000;
Table created.
-- The following shows the HWM at 87 blocks..
SQL> select segment_name,bytes,blocks,extents from user_segments where segment_name='T';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES BLOCKS EXTENTS
---------- ---------- ----------
T
712704 87 8
-- Lets delete all the data
SQL> delete from t;
3999 rows deleted.
SQL> commit;
Commit complete.
-- Following shows the delete had no impact on HWM. FTS will still scan upto HWM
SQL> select segment_name,bytes,blocks,extents from user_segments where segment_name='T';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES BLOCKS EXTENTS
---------- ---------- ----------
T
712704 87 8
-- Lets reset the HWM
SQL> alter table t move ;
Table altered.
SQL> select segment_name,bytes,blocks,extents from user_segments where segment_name='T';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES BLOCKS EXTENTS
---------- ---------- ----------
T
16384 2 1
-- now it occupies only the 2 blocks and 1 initial extent.
HTH
-Thiru
|
|
|
|
|
|
| Re: Getting to Know HWM : Correction [message #59160 is a reply to message #59144] |
Sat, 01 November 2003 05:18 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Pls use user_tables instead of user_segments in the previous demo. User_tables.BLOCKS gives you the used blocks in the table(ie HWM) , while user_Segments.BLOCKS gives you the total blocks allocated for that segment. My error.
Also refer to my other posting related to this..
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
