 |
|
Oracle Tips by Burleson |
OCP Instructors Guide for
Oracle DBA Certification
Chapter 7 - Tuning and Performance
High Water Mark and Table Scans
Oracle uses the high water mark to identify the
highest amount of space used by a particular segment. It acts as
the boundary between used and unused space. As the amount of data
grows, the segment’s high water mark grows accordingly. But as the
amount of data shrinks (i.e. row deletes, updates), the high water
mark is not altered to reflect the segment’s new characteristics.
The high water mark not being adjusted as the data shrinks has the
tendency to create a somewhat confusing performance problem.
During a full table scan, Oracle scans all
blocks up to the table’s high water mark. This happens regardless
of whether those blocks contain data or not. Business needs often
require that all rows of a table be scanned. DBAs often attempt to
increase the performance of the aforementioned table scans by trying
to store as little data as possible in the scanned table. If the
table is purged by SQL delete statements, the high water mark will
not be moved and the table scans will continue to perform poorly.
The above text is
an excerpt from:
OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
Administration Techniques
ISBN 0-9744355-3-8
by Christopher T. Foot
http://www.rampant-books.com/book_2003_2_OCP_print.htm
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
