Overview

This chapter will cover the second layer of object physical tuning, dealing with explicit block-and extent-level tuning within objects. I like to call block-and extent-level tuning low-level physical tuning.

There are a few things we need to reiterate and freshly discover before going too deeply into low-level Oracle Database tuning.

14.1 What is the High Water Mark?

Often a table, index, or database object is referred to as a segment. The high water mark of a segment is the maximum number of blocks ever used in that segment. Space can be removed from segments by deletions, fully or partially freeing up blocks. Therefore, the high water mark of a table is the highest block number ever used by that table for data, not allocated space. The high water mark is not reset when data is deleted. When a full table scan is performed each block is read up to the table's high water mark. Therefore, if a new table with 100 free blocks, has inserts filling 70 blocks, followed by deletes freeing 20 blocks, then that table's high water mark is 70 blocks and not 50 blocks (70 20). A full table scan on this table will physically read 70 blocks even though...