Previous | Table of Contents | Next

Page 487

CHAPTER 21

Managing Database Storage

In this chapter

Page 488

Administering Database Objects

An Oracle database has many different components that consume disk and system resources. As we will see, each resource has unique storage characteristics and requires unique handling and care. In the following sections, we'll look at database segments and their unique storage concerns. We'll talk about the segments themselves, such as tables, clusters, indexes, rollback segments, and temporary segments, as well as the internal methods used by the Oracle database to store them.

Managing Oracle Blocks

The smallest unit of storage addressable by the Oracle server is the Oracle block. All database segments are composed of Oracle blocks, and the block structure is the same whether the segment is a table, index, cluster, or other object. Designing a database with optimal characteristics starts with the proper configuration and administration of the Oracle blocks.

Each Oracle block is made up of the following three sections:

The block header contains information about the block—what type of segment data is stored in the block, what segments have data in the block, the address of the block, and pointers to the actual rows stored in it. The header size is comprised of a fixed part and a variable part, and a block header in general uses 85 to 100 bytes in the block.

Within the Oracle block, managing the data storage and free space areas are directly related to each other. The data area is where the rows are actually stored in the block. The reserved free space area is a region, defined as a percentage of the total available space, that is reserved to store information for future updates of rows stored in the block. Managing the data and reserved block areas are the main concern of Oracle block administration, and are discussed below.

Understanding PCTFREE and PCTUSED

PCTFREE and PCTUSED are two storage parameters that are often misunderstood, but the concept is actually quite simple. When the Oracle RDBMS writes information to the database blocks, it uses the PCTFREE and PCTUSED parameters to tell itself whether a block is available for new rows to be added. If the percentage of space in the reserved area is greater than the PCTFREE parameter, the block can be used to store new rows. Once the reserved space has fallen below PCTFREE, the block is considered "full," and Oracle will not add any additional rows. Should the amount of used space in the block fall below PCTUSED, Oracle will then again use the block to add new rows. This method allows Oracle to keep enough extra space for rows to grow without having to span more than one block. Keeping rows confined to a single block will help keep your database running at peak performance.

Page 489

Once a block's reserved free space falls within the PCTFREE region, it stays off the available list until its free space percentage reaches the PCTUSED value (through deletions of rows stored in the block, or updates that decrease the length of rows in the block). PCTUSED merely specifies the percentage of free space the block must have before Oracle will consider it for new rows.

The values of PCTFREE and PCTUSED should never equal 100 percent. If a segment is configured this way, it is possible that the block will reach a point where it is continuously being taken off of and placed on the free list by every minor data manipulation. The overhead incurred by the database engine handling can easily be avoided by leaving a margin of at least 20 percent between PCTFREE and PCTUSED. The Oracle defaults of 10 for PCTFREE and 40 for PCTUSED illustrate this margin.

PCTFREE and PCTUSED are specified in the storage clause of the database segment. You can query their current values using the dba_tables, dba_clusters, or dba_indexes data dictionary views. Using PCTFREE and PCTUSED, you can fine-tune the storage characteristics of individual tables to meet their particular storage needs. For example, a table that will never be updated (a mainframe database extract used for reporting, or tables held in read-only tablespaces) can be safely set with a PCTFREE of 0. This saves you space in each block—for a 4KB block and PCTFREE setting of 10, approximately 800 bytes per block is available for data that would have been reserved. For a 500MB table, you could realize savings of around 50MB for implementing this one minor change. On the other hand, a table containing columns that are guaranteed to be updated in the future can be set with a higher PCTFREE to avoid row chaining or migration. Row chaining and migration are discussed in detail in the "Understanding Database Fragmentation" section of this chapter.

Figure 21.1 shows how PCTUSED and PCTFREE work together to control space utilization inside the block. In the example, we assume that PCTFREE is set to 20 percent and PCTUSED is set to 80 percent. Number 1 shows that inserts can utilize all the space inside the block until the 80 percent limit is reached. This is because 20 percent of the space after block overheads is reserved for future updates via the PCTFREE parameter. Number 2 shows that once the PCTFREE limit is reached, the reserved free space can only be used for updates of current row data—no new inserts are permitted in this block. Number 3 shows the block being placed back on the free list as a result of deletions or updates and the used space in the block falling below 40 percent (the PCTUSED setting of the table). Number 4 shows that once the used space falls below PCTUSED, new insertions are now possible in the block. As insertions are made inside the block, the used space starts increasing, again touching the upper limit, and the cycle begins again.

Managing Table Storage

Tables are probably the most straightforward objects for determining storage needs. A table's space requirements are roughly the product of average row length and number of rows. When DBAs are discussing the approximate size of the databases in their care, the number of rows is often used as a metric. Although this by itself does not provide a lot of information for planning long-term storage needs, it does tend to give us some insight as to the magnitude of the database size.

Previous | Table of Contents | Next