Recently a potential client in San Diego quizzed me on the high water mark and performance tuning in Oracle.
Basically the high water mark (HWM) has a function that comes into play with tables that have heavy insert, update and delete activity. Every time data is changed in a table, the HWM moves to a setting in each table that shows how much free space is left in terms of blocks used and free in the segment object. What this means is that say I have a segment object type table A that has 100,000 rows inserted and then I perform a delete operation to delete 10,000 rows from that table, Oracle will move the HWM to a value dependent on the PCT_FREE and PCT_USED values based on the free list available in that table.
Things get tricky when we go to Oracle 9i which added the option to create automatic segment space management (ASSM) functionality with the table. Prior to Oracle 9i, all tables were manual segment managed which meant that a DBA had to play around with various parameters for PCT_FREE, PCT_USED, etc. to get the balance right for performance optimization of that table. ASSM allows bitmaps to manage that table and improves performance and management is far easier than tweaking all of the previous parameters.
Which leads us to the main issue of HWM and Oracle performance. When a DELETE operation is taking a long time, that is because the HWM value has not been reset. To clean this issue up and improve performance, the HWM has to be reset.
Now there are a couple of ways to do this: one is to perform a TRUNCATE operation on the table which will reset the HWM values and run a lot faster than a DELETE operation.
A second option is to reorganize the table using either export or import for Oracle 8i/9i releases or to use the data pump (expdp/impdp) utility available with Oracle 10g and Oracle 11g to reset the HWM values.
Lutz Hartman has an excellent discussion on the new high watermark settings for Oracle 10g and 11g:
To quote Oracle ACE Lutz Hartman on the concept of the HWM for Oracle:
"Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management."
Which brings us to a new discussion with terms that there is now a HHWM and LHWM values for segment objects for tables and other objects.
To further enhance performance, in terms of the values for tables, it is now possible with Oracle 10g and 11g to shrink segments in a table.
The nice thing about Oracle 10g and 11g is that we are not having to do the export and import tables and move them around to reset the HWM for performance reasons we can use the "ALTER TABLE ... SHRINK " commands after we have enabled row movement for the table and all of this can be done online with no downtime for the table!
Subscribe to:
Post Comments (Atom)
2 comments:
I am sorry but I think your posting is very confusing to someone with little or no experience with HWM. In particular your statement Every time data is changed in a table, the HWM moves to a setting in each table that shows how much free space is left in terms of blocks used and free in the segment object .
You are right about the space left as the documentation:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/logical.htm#CNCPT301
Clearly states:
"The high water mark is the boundary between used and unused space in a segment."
I think you need to go into more details about the difference between space left in blocks and space left in segements.
Changes occur within the data block (which is within the used segment) for insert/updates and deletes. If you have a bunch of deletes then the data left maybe able to fit within lets say 1000 blocks. However, it might be strung out between 50000 blocks. A new delete statement would have to search through the entire 50000 blocks.
Export/Import and with 10g/11i ALTER TABLE ?? ENABLE ROW MOVEMENT does allow us to put all the rows within the minimal number of blocks at the beginning of the segment. Which will allow you to shrink the segment and lower the HWM. Therefore a delete statement will only have to search through the 1000 blocks NOT 50000.
Hi Tim,
Sorry it is confusing but the topic of HWM is pretty advanced yet basic concept at the same time.
I plan to go into more details in future posts on space usage in segments and the high water mark. In Oracle 9i, 10g and 11g there is actually two HWM- a lower high water mark (LHWM) and a higher high water mark (HHWM).
The Low High Water Mark (LHWM) is similar to the older High Water Mark in releases of Oracle before 9i, in that all of the blocks below it have been formated for use by Oracle.
In contrast, the High High Water Mark (HHWM) references the area over which no blocks have been formatted for use.
I will cover more on this and many other Oracle database internals in future blog posts, white papers, presentations and books.
Thank you,
Ben Prusinski
Post a Comment