Aug 14, 2015 2:04:00 PM by Quest SoftwareFragmentation in a tablespace is thought to be a performance killer. This has been proven to be a myth. Fragmentation by itself does not cause performance problems in a multi-user Oracle database. What is a problem is fragmentation of free space within a tablespace. What follows is information pertaining to the fragmentation of free space. If your free space is fragmented enough, you may be wasting space that can never be used by any extents in your tablespace. It should be noted that converting to Locally Managed Tablespaces (available since Oracle 8i) will eliminate the problem where fragmented free space cannot be used. Locally Managed Tablespaces, by their very nature, will fragment extents, but this is not a performance problem.
If you are not using Locally Managed Tablespaces, then the rest of this section is important to you. But you might seriously consider implementing Locally Managed Tablespaces so that you do not have to worry about these details.
ALTER TABLESPACE <name> COALESCE;
Where: <name> is the tablespace name.
Honey-comb fragmentation occurs when one or more objects are deleted or dynamically shrunk leaving contiguous blocks of data storage space empty, like the holes in a piece of honey-comb. Oracle automatically consolidates honey-comb fragmentation about every 30 minutes or so, in high use situations this is not frequent enough to prevent the honey-combs from becoming encapsulated by new data forming swiss-cheese fragmentation.
Swiss-cheese fragmentation occurs when an object is dropped that has been surrounded by the data from one or more other objects. This results in "holes" in the data structure similar to the "holes" in swiss-cheese. Oracle cannot automatically handle swiss-cheese fragmentation.
Of the two types of fragmentation, swiss-cheese causes the most difficulty and results in wasted database space.
The SMON process automatically consolidates contiguous free extents into single large extents for tablespaces whose default value for the pctincrease storage parameter is greater than zero. This reduces but doesn't eliminate the problem. In Oracle 8i you can avoid the overhead associated with either automatic coalescence or manual coalescence by using locally managed tablespaces. Since in a locally managed tablespace the tablespace itself tracks its extents through use of a bitmap, any contiguous free areas of space are automatically coalesced.
This method will consolidate all free space, and, will consolidate all tables into single extents. However, the database won't be available and for large systems, the time required could be extensive.
Honey-comb fragmentation is handled automatically by Oracle.
NOTE: If the SMON process will not coalesce tablespaces whose pct_increase default storage parameter is set to zero any tablespace that has a pct_increase of zero will have to be manually coalesced.
You can use one of two methods. The first method is just to temporarily set the pctincrease to one and await SMON's automatic cleanup. This can take several minutes. The second method involves issuing an event command against the specific tablespace where the fragmentation is happening:
alter session set events 'immediate trace name coalesce level ts#';
where ts# is the tablespace number as specified in the ts$ table.
You can create a view against the DBA_FREE_SPACE view that summarizes the fragmentation state of all the tablespaces. For example:
CREATE VIEW free_space ( tablespace, file_id, pieces, free_bytes, free_blocks, largest_bytes, largest_blks ) AS SELECT tablespace_name, file_id, COUNT (*), SUM (bytes), SUM (blocks), MAX (bytes), MAX (blocks) FROM sys.dba_free_space GROUP BY tablespace_name, file_id;
Written by Quest Software