Controlling Fragmentation

Fragmentation is best described as a grouping of noncontiguous database objects. Fragmentation usually means extra resources are consumed (disk I/O, rotational delay of the disk drives, dynamic extensions, chained blocks, and so on) to perform the requested database function. Not all fragmentation is bad. Consider disk striping. Striping is spreading fragments out over multiple disks to aid in distributing I/O.

Most fragmentation in the Oracle environment is not the good kind, though. Tablespace fragmentation doesn’t necessarily affect performance, but a significant amount of disk space can be wasted. Deletes can leave unused space behind in data blocks and leave smaller disk segments unused in the tablespace, wasting tablespace space. The Oracle process PMON periodically coalesces contiguous unused smaller extents into single larger available extents but does not reshuffle used extents so that all free space in the tablespace is together. The only way to fix wasted space in blocks is to export the data, drop the object, and then import the data back. The only way to fix unused tablespace due to fragmentation is to reorganize the whole tablespace.

Extent interleaving occurs when two or more data objects in the same tablespace are dynamically growing in such a way that extents are no longer contiguous with the other extents of the object. Oracle performs best with a minimal number of extents, and when extents are next to each other. The fewer extents, the fewer trips Oracle has to make to the data dictionary to find out where the next extent is. Having extents sprinkled all over the tablespace also causes excessive disk head movement (disk thrashing), a time-consuming I/O bottleneck. Objects that are involved in this interleaving should be exported, dropped, and re-created. Extent interleaving can be completely eliminated by simply putting tables with static or very little growth together in a tablespace and putting each dynamically growing object in a tablespace by itself. This increases tablespace maintenance but eliminates this type of fragmentation.

There are two events that will cause an extra read/write process for Oracle per row being accessed. These events are row chaining and row migration. They differ in what actually happens, but the end result is the same: extra I/O to read or write the desired information.

Row chaining is caused when a row being inserted is larger than the Oracle block size. Oracle will chain, or link by block number, an additional block (or blocks) to hold the remaining data from the row. This chained block is not indexed and cannot be referenced without first referencing the original block. This greatly slows the data access as Oracle has to wait for an additional I/O to retrieve the chained block. If there is significant chaining, performance can be seriously affected. The only way to correct chaining is to export the whole database, initialize the database with a larger block size, and then import the whole database, or locate the object and its data into an Oracle database that already has a larger block size.

Row migration is caused by an update that grows the row past available space in its current block. Oracle will relocate the updated row to a different block where it will completely fit, leaving a ROWID pointer in the original block that references the new row location in another block. Like row chaining, there is no other way to access this row in the new location except by accessing the original location first. Once again, Oracle will have to wait while an additional disk I/O is performed to retrieve the new row. It can also be a time-consuming process for Oracle to locate a new data block with enough free space to hold the new row. Row migration can be limited by making adjustments to the PCTFREE parameters in the storage clause. PCTFREE is the amount of space left in each Oracle block after insertions for the purpose of accommodating future updates. Altering the PCTFREE clause will not have any affect on any existing migrated rows. The table will have to be exported, dropped, and then imported.

SQL-Level Tuning

SQL-level tuning is the final area of tuning. The 80/20 rule applies very well here. Twenty percent of the SQL statements will utilize over 80 percent of the resources. It is this 20 percent that needs to be well tuned.

There are various monitoring tools available, discussed in detail in Chapter 22, “Oracle8 Tools,” that can identify resource-intensive and/or long-running SQL statements. Tuning these will have the biggest impact on application performance. There are also a variety of explain-plan tools available from Oracle Corporation and other vendors. Oracle Enterprise Manager has a SQL-monitor tool available. Oracle has always provided a variety of ways of capturing performance information in the form of trace files. These trace files are interpreted by TKPROF, a tool not for the novice. The remainder of this chapter will discuss various features of tuning at the SQL level, the two optimizer choices, indexing features, and the SQL statements themselves.