High-Performance Oracle Database Applications:Performance And Tuning For Distributed Oracle Databases

Tablespace Fragmentation

As rows are added to tables, the table expands into unused space within the tablespace. Conversely, when rows are deleted, a table may coalesce extents, releasing unused space back into the tablespace. As this happens, it is possible for there to be discontiguous chunks, or fragments of unused space within the tablespace. Whenever the value for a table as specified by STORAGE (INITIAL xx) is exceeded, Oracle will create a new extent for the table. If the PCTINCREASE is set to 0, a new extent of the size specified in STORAGE (NEXT xx) will be added to the table. If PCTINCREASE is non-zero, the extent size will be equal to the value of the most recent extent size multiplied by PCTINCREASE.

Note: PCTINCREASE for a tablespace should not be set to 0, since this will disable the automatic coalesce facility for Oracle tablespaces. In general, all tablespaces except the system tablespaces (SYSTEM, RBS) should have PCTINCREASE set to 1. The PCTINCREASE parameter for tablespaces is generally only used when a table is allocated without a STORAGE clause—although Oracle also uses it for coalescing.

This allocation of new extents will be physically contiguous to the table’s initial location, as long as the next physical data blocks are empty. Unfortunately, as many tables populate a tablespace, a table may not have contiguous data blocks for its next extent, which means that it must fragment the extents onto another spot in the datafile, as shown here:

CREATE TABLESPACE SALES
  DATAFILE '/Data/ORACLE/sales/sales.dbf'
  SIZE 500M REUSE
  DEFAULT STORAGE (INITIAL 500K  NEXT 50K  PCTINCREASE 1);

Here we see that the SALES tablespace has been allocated to a physical file called /Data/ORACLE/sales/sales.dbf, created at a size of 500 MB. Assuming that all tables within this tablespace use default storage, they will be initially allocated at 500 K and will extend in chunks of 50 K.

But what happens if the tablespace gets full? Processing will cease against the tablespace, and the Oracle DBA must intervene to add another data file to the tablespace with the ALTER TABLESPACE command:

ALTER TABLESPACE SALES
ADD DATAFILE '/Data/ORACLE/sales/sales1.dbf'
SIZE 200M REUSE;

Obviously, the DBA should carefully monitor tablespace usage so that tablespaces never fill, but Oracle version 7.2 and above offer an alternative. The AUTOEXTEND command can be used to allow a data file to grow automatically on an as-needed basis. Here are the different permutations of this command:

ALTER DATABASE DATAFILE '/Data/ORACLE/sales/sales.dbf' AUTOEXTEND ON;

ALTER DATABASE DATAFILE '/Data/ORACLE/sales/sales.dbf' AUTOEXTEND MAXSIZE
  UNLIMITED;

ALTER DATABASE DATAFILE '/Data/ORACLE/sales/sales.dbf' AUTOEXTEND MAXSIZE
  (500M);

ALTER DATABASE DATAFILE '/Data/ORACLE/sales/sales.dbf' RESIZE (600M);

When tables fragment, additional I/O will be required to access the table data, since the disk must access blocks on two noncontiguous spots on the datafile. The following script will detect all tablespaces whose tables have taken more than 10 extents:

tblsp_fr.sql - shows all tablespaces with more than 10 extents
SET PAGES 9999;
COLUMN c1 HEADING "Tablespace Name"
COLUMN c2 HEADING "Number of Extents"
TTITLE " Tablespaces with more than 10 extents"

SELECT tablespace_name c1,
       MAX(extent_id) c2
FROM dba_extents
WHERE
extent_id > 9
GROUP BY tablespace_name
;

Here is the output of this script:

SQL> @tblsp_fr

Fri Mar 15                                                      page    1
                      Tablespaces with more than 10 extents

Tablespace Name                Number of Extents
------------------------------ -----------------
INDX                                         113
SALES                                         57
SYSTEM                                        56

Contrary to popular opinion, tables with noncontiguous extents do not cause performance problems. It is only the row fragmentation that sometimes accompanies discontiguous extents that negatively affect performance. In some studies, a table with discontiguous extents (and no row fragmentation) actually performed faster than a table that was in a single extent.

Tablespace Reorganization

Because they are dynamic, Oracle databases will always fragment over time and may require a periodic cleanup. In general, reorganization ensures that all tables and indexes do not have row fragmentation, and that they reside in a single extent, with all free space in a tablespace in a single, contiguous chunk. Reorganizing a tablespace can be accomplished in several ways. Rather than bring down the entire Oracle database to perform a full export/import, there are some other options.

Let’s take a look at how a tablespace may become fragmented. At initial load time, all Oracle tables within the tablespace are contiguous—that is, only one chunk of free space resides at the end of the tablespace. As tables extend and new extents are added to the tablespace, the free space becomes smaller but it still remains contiguous.

Basically, a table can fragment in two ways:

• A table extends (without row chaining)—Contrary to popular belief, this is not a problem and performance will not suffer.

• Rows fragment within the tablespace (due to SQL UPDATES)—This causes a serious performance problem, and the offending tables must be exported, dropped, and reimported.

Tablespace fragmentation occurs when some “pockets” of free space exist within the tablespace. So, how do these pockets of free space appear? If tables are DROPPED and re-created, or if individual tables are exported and imported, space that was once reserved for a table’s extent will now be vacant.

Table of Contents