Slow count(*) and the Highwater Mark[akadia]

For each object, Oracle also maintains a record of the highest relative block of the table used to hold data. This highwater mark is maintained in multiples of five blocks and is not reset unless the TRUNCATE command is executed.

When Oracle performs operations requiring a full table scan, such as SELECT count(*), all blocks up to and including the highwater mark are read. If a table is created with 50,000 rows occupying 10,000 blocks, and those rows are subsequently deleted, the highwater mark will remain at 10,000, and a SELECT count(*) command will read all 10,000 blocks even though they are all empty.

An even worse scenario is possible. Suppose that a table contains 50,000 rows, and the first 49,000 rows are then deleted. The blocks corresponding to the deleted data are placed at the end of the free block list. When the next INSERT statement is executed, Oracle finds the first block on the free block list, which is beyond the highwater mark. The effect is that all the free space (49,000 rows worth) is ignored, and the physical table becomes bigger.

Full table scans and other similar operations still have to read all the empty blocks, and performance is significantly impacted. If you use SQL*Loader with the direct path option, these loads always begin at the highwater mark, so the table size may grow while leaving significant amounts of free space unused.

To easily determine the current value of the highwater mark, use the following formula after analyzing the table:

highwater mark = total blocks - empty blocks - 1

Total blocks for a table can be obtained by using the following query.

SELECT blocks
FROM dba_segments
WHERE owner = '&Owner'
AND segment_name = 'Tablename';

Likewise, the number of empty blocks (blocks above the highwater mark) can be obtained with this query:

SELECT empty_blocks
FROM dba_tables
WHERE owner = '&Owner'
AND table_name = 'Tablename';

John Dixon, published on http://www.revealnet.com/ a script which can be used to list all of the tables specified by owner, where the High Water Mark is say 20% larger than the actual data in the tables. This will indicate which tables require a rebuild.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60603/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60603/