6 Replies Latest reply: Aug 26, 2014 1:21 PM by jgarry RSS

    what is the benefit of High Water Mark ?

    Mohanad Awad



      When i delete huge data from table then High Water Mark still on the same position .


      1-  then deleted block still on the datafile so why ? i understand that these block is not used and it's empty so when it used ?

      2-  Why oracle did not reclaim these block when we delete records automatically ? and we must do that using shrink command ?

      3-  Why before shrink  data we must enable row movement ? what the meaning of  row movement . and why it's disable by default , is there any impact on performance if we enable it  ?

      4- i try to shrink and de-allocate space for some huge data , but still tablespace free space still same , what that mean ? 



        • 1. Re: what is the benefit of High Water Mark ?

          1) The deleted data is still in the data file because that is the least expensive way of implementing a delete.  It's much faster to just mark things as free rather than incurring the cost of, say, formatting the block when the last row is deleted.  Most of the time, you're deleting one or two rows-- it wouldn't make sense to incur that overhead every time you delete a few rows.  The free blocks will be used when subsequent data is inserted (or when subsequent updates increase the size of existing rows and cause them to migrate to new blocks).


          2) Because shrinking the segment is expensive.  If you did that every time you deleted a single row, deletes would be orders of magnitude more expensive than they are now.


          3) Because shrinking the segment means that you have to physically move potentially every row in the database.  Functionally, this is equivalent to deleting potentially every row from the block it's in and moving it to a different physical position at the "head" of the segment so that all the free space is at the "tail" of the segment and can be freed.  Of course, it's unlikely that every row actually has to be deleted and re-inserted.  But a significant fraction of them will be.  Among other things, this will change the row's ROWID which would break any code that was expecting the ROWID to remain constant which is why you need to enable row movement.


          4) You'd need to show us exactly what you are doing (the exact series of commands you are executing, how you are measuring the free space, etc.)  If you are just going to insert roughly the same amount of data into the table that you deleted over time, shrinking the segment is unlikely to be useful.  From a query performance standpoint, for example, you really only care about the high water mark if you are doing a full scan of the table.  But it rarely makes sense to write code that involves doing a full scan of a table that is large enough that you can delete a "huge" amount of data from the table.



          • 2. Re: what is the benefit of High Water Mark ?

            Please always include your four digit version number and platform info


            1. Extending a datafile is a costly operation. Also the datafile needs to be latched and will not be accessible during the extension. Tables tend to grow. It is not sensible to treat files like harmonica. Empty blocks will be reused as soon as a segment needs to extend.
            2. Because those blocks will be re-used when you INSERT or UPDATE records. You better wouldn't shrink a table, because you will force Oracle to extend it all the time. This is only a waste of resources. Remember disk is cheap, and it is less cost-efficient to have you shrink objects, than it is to buy more disks. Managers however see the costs of disks, and don't see you are wasting your time and their money.
            3. Without row movement, a row can not move frome one block to another. Rows can move within a block without problem. The rowid in an index contains the block id, so whenever you move rows, you need to adjust (automatically) your indices also.
            4. As we need to guess what you did, as you didn't post any evidence , I can not reply to this question,


            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: what is the benefit of High Water Mark ?
              Mohanad Awad

                Thanks for answer


                      1- deleted block will be reused be next insert , update  .. when the next move ( increase)  of HWM will be ,  when all free block used and no free block left ?

                      2- The be benefit of HWM is just to manage space , or there any other benefit ? if you can give me clear answer.

                      3- is there any limitation in HWM , is there max value will be reach ? and then stopped or it will be still growing as there space on datafile ?

                      4- enable row movement : why it's disable by default , is there any impact on performance if we enable it  ?


              • 4. Re: what is the benefit of High Water Mark ?

                1.  More or less, yes, the high water mark will not change until the existing free space in the segment is used.  There are exceptions to that (direct path inserts, for example) and there may be cases where new extents are allocated when there is still a bit of free space in the existing segments. 


                2.  The high water mark only indicates the last formatted block in the segment.  It does come into play when you're doing a full scan of the table (as I mentioned earlier).  But that generally doesn't make a lot of sense for a large table.


                3.  Practically, no.  There is obviously a point where you'll hit some internal Oracle limit.  Unless you have a system that is substantially larger than any Oracle system that has ever been created, though, you probably don't care.


                4.  Enabling row movement does not, in and of itself, impact performance.  Actually causing rows to move does affect performance while you're doing it.  And it can affect the ability of applications to function correctly (which is why I mentioned applications that expect a ROWID to remain constant for example).



                • 5. Re: what is the benefit of High Water Mark ?
                  Mohanad Awad

                  Thanks ,


                  i just need now to minimize database space size ?


                  so please advice which best way , shrink ? Deallocate unsued ? or alter table move ? and what the different between each one?


                  or please give me recommend me with best way ?


                  Best Regards,

                  Mohanad Awad

                  • 6. Re: what is the benefit of High Water Mark ?

                    The best way depends on the particular situation.  There are always going to be tradeoffs, and if you have to spend all your time tweaking and twirling to get a little bit of space back, you have a management problem.

                    If you have a particular situation, describe it.  If you are deleting lots of data continuously, you may be doing it wrong.