There are two methods for managing space in segments:
•\ Manual Segment Space Management: You set various parameters such as FREELISTS, FREELIST GROUPS, PCTUSED, and others to control how space is allocated, used, and reused in a segment over time. I will refer to this space management method in this chapter as MSSM, but bear in mind that that is a made-up abbreviation that you will not find widely in the Oracle documentation.
•\ Automatic Segment Space Management (ASSM): You control one parameter relating to how space is used: PCTFREE. The others are accepted when the segment is created, but they are ignored.
MSSM is the legacy implementation in Oracle. It has been around for many years, over many versions. I won’t further talk about this type of space management in this book.
ASSM eliminates the need to fine-tune the myriad parameters used to control space allocation (as was required in the old MSSM method) and provide high concurrency. The only storage settings that apply to ASSM segments are as follows:
•\ BUFFER_POOL
•\ PCTFREE
•\ INITRANS
Segment space management is an attribute inherited from the tablespace in which a segment is contained (and segments never span tablespaces). For a segment to use ASSM, it would have to reside in a tablespace that supported that method of space management.
High-Water Mark
This is a term used with table segments stored in the database. If you envision a table, for example, as a flat structure or as a series of blocks laid one after the other in a line from left to right, the high-water mark (HWM) would be the rightmost block that ever contained data, as illustrated in Figure 10-1.
Figure 10-1. Depiction of an HWM
Figure 10-1 shows that the HWM starts at the first block of a newly created table. As data is placed into the table over time and more blocks get used, the HWM rises. If we delete some (or even all) of the rows in the table, we might have many blocks that no longer contain data, but they are still under the HWM, and they will remain under the HWM until the object is rebuilt, truncated, or shrunk (shrinking of a segment is only supported if the segment is in an ASSM tablespace).
The HWM is relevant since Oracle will scan all blocks under the HWM, even when they contain no data, during a full scan. This will impact the performance of a full scan— especially if most of the blocks under the HWM are empty. To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows), and then execute a SELECT COUNT() from this table. Now, DELETE every row in the table and you will find that the SELECT COUNT() takes just as long to count 0 rows as it did to count 1,000,000 (or longer, depending on whether you need to clean out the block; refer to the “Block Cleanout” section of Chapter 9). This is because Oracle is busy reading all of the blocks below the HWM to see if they contain data. You should compare this to what happens if you used TRUNCATE on the table instead of deleting each individual row. TRUNCATE will reset the HWM of a table back to zero and will truncate the associated indexes on the table as well. If you plan on deleting every row in a table, TRUNCATE—if it can be used— would be the method of choice for this reason.
Caution Keep in mind that a TRUNCATE statement cannot be rolled back, nor will any triggers fire (if they exist) on the table. Therefore, before truncating, ensure you permanently want to remove the data since it can’t be undone.
In an ASSM tablespace, there is an HWM and a low HWM. When the HWM is advanced, Oracle doesn’t format all of the blocks immediately—they are only formatted and made safe to read upon their first actual use. The first actual use will be when the database decides to insert a record into a given block. Under ASSM, the data is inserted in any of the blocks between the low HWM and the HWM, so many of the blocks between these two points might not be formatted. The low HWM is defined to be the point below which all blocks are formatted (because they currently contain data or previously contained data).
So, when full scanning a segment, we have to know if the blocks to be read are safe or unformatted (meaning they contain nothing of interest and we do not process them). To make it so that not every block in the table needs to go through this safe/not safe check, Oracle maintains a low HWM and an HWM. Oracle will full scan the table up to the HWM—and for all of the blocks below the low HWM, it will just read and process them. For blocks between the low HWM and the HWM (see Figure 10-2), it must be more careful and refer to the ASSM bitmap information used to manage these blocks in order to see which of them it should read and which it should just ignore.
Figure 10-2. Depiction of a low HWM