Fragmentation table

Question:  I have a concern that table fragmentation may be causing my SQL to run more slowly.  How can I detect if my tables have fragmented?  If I have table fragmentation, how can I reorganize the table to prevent further fragmenting?

Answer:  Oracle is a high performance engine, and he can allows for thousands of concurrent tasks to insert into discontiguous data blocks, tossing table rows helter-skelter through the table extents.

There are many names for the types of table and tablespace fragmentation with names like "honeycomb fragmentation" and "bubble fragmentation".  There are also distinctions made between fragmentation that is related directly to tables as well as tablespace fragmentation, which occurs when some “pockets” of free space exist within the tablespace.

Theory note:  When Codd and Date wrote the relational database manifesto, they were adamant that the physical placement of tables rows upon the disks was not germane to the database.  However, from a pragmatic perspective, Oracle offers several types of tools (partitioned tables, sorted hash clusters) for grouping like-minded rows onto adjacent data blocks.

Pristine vs. fragmented tables

To understand table fragmentation, let's start my observing a table in it's "pristine" un-fragmented state.  When read-only tables are archived into a read-only tablespace, the DBA will reorganize the table rows to tightly pack the rows onto the table blocks by adjusting object parameters (e.g. PCTFREE), and rebuild the indexes into a pristine tree.

It's DML activity that fragments Oracle table rows, and several parameters control the extent of the table fragmentation:

• The pctfree parameter (if not using ASM):  The pctfree parameter is used at insert time to determine when a table is logically full, and un-link the data block from the freelist, thereby making the block ineligible to receive new rows.  Using ASM tablespaces automate freelist management and remove the ability to specify pctfree, pctused, freelists and freelist groupsstorage parameters.
• The next parameter (If not using locally managed tablespaces):  The next parameter govern when a table extends.
  
•  Freelists & freelist groups (if not using ASSM): At insert time, multiple concurrent tasks may simultaneously insert into an Oracle table, each using different, non-adjacent data blocks.

When a table becomes fragmented, the DBA may choose to reorganizing tablesusing the Oracle dbms_redefinition utility.

So, how do these pockets of free space appear? If tables are reorganized or 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.
To understand how rows fragment across data blocks, we must know that within a tablespace, objects are scattered throughout the tablespace and corresponding datafiles. 

There are several types of table fragmentation, most commonly the "honeycomb" fragmentation and the "bubble" fragmentation, where it is more difficult to reclaim wasted disk space.  Note that you can remove bubble fragmentation by using locally-managed tablespaces, and see these notes on Oracle data file fragmentation.

Types of table fragmentation

Basically, an Oracle  table can fragment in several ways:

• A table extends (without row chaining):  Contrary to popular belief, this is not a problem and performance will not suffer. In many cases, a table with multiple extents will perform faster than a table in a single extent.
•  Table rows fragment within the tablespace (due to multiple freelists and concurrent DML):  This causes a serious performance problem, and the offending tables must be exported, dropped, and re-imported.
• High Water Mark fragmentation:  After massive SQL delete operations, the high water mark (HWM) remains high, causing full-table scans to run longer than they should.

Oracle table fragmentation

As rows are added to tables, the table expands into unused space within the space. Conversely, when rows are deleted, a table may coalesce extents, releasing unused space back into the tablespace. As DML activity happens, it is possible for there to be discontiguous chunks, or fragments of unused space within the tablespace and fragmentation within the table rows. 

Tables, indexes and tablespaces will naturally fragment as a function of update activity and Oracle has many methods for reclaiming disk space and a segment advisor which will recommend when tables and indexes will benefit from a reorganization to free up disk space.

At insert time, tables with multiple freelists or freelists groups will naturally fragment as discontiguous data blocks are fetched to receive new rows.

The downside of table fragmentation

There are only two cases where table fragmentation can cause a problem, slow scan activity and wasted disk space.  From a performance perspective it's important to understand that SQL which requests single rows will never suffer from a fragmented table while SQL that performs full-scan and large index range scans may run more slowly in a fragmented table.

A table can have lots of internal fragmentation for several reasons. (Note that if you are using ASSM, PCTFREE, PCTUSED FREELIST_GROUPS and FREELISTS are ignored:

• Empty table extents - You may have had lots of deletes and the high-water mark was left at a high value.  After massive SQL delete activity there may be many megabytes of unused space between the last rows and the high water mark.  This will cause slower full-table-scan performance since Oracle must read to the high water mark.
• Sub-optimal insert freelist unlink - You may have lots of insert activity that left loads of half empty pages due to at too low PCTFREE setting.  This causes a page to un-link itself from the freelists while it still has lots of space to accept new rows.
• Sub-optimal freelist re-link - You may have lots of pages with some free space. but not enough to accept new rows.  This happens when PCTUSED is set
• Row Chaining - You may have row-chaining/row-migration of you store LOB's that are greater than the table blocksize .
• Sparse tables - Sparse tables generally occur when a SAP table is defined with many free lists, and the table has heavy insert and delete activity. This causes the table to extend, even though it may be largely empty. Extension occurs because each free list is unaware of the contents of other free lists inside each free list group. TFor more, see my notes on identifying sparse tables.
• Row adjacency fragmentation -   Some shops will improve SQL speed by deploying sorted hash clusters, thereby placing related rows into a single data block.
• 
  

Row adjacency and SQL performance

For queries that access common rows with a table (e.g. get all items in order 123), unordered tables can experience huge I/O as the index retrieves a separate data block for each row requested.

If we group like rows together (as measured by the clustering_factor indba_indexes) we can get all of the row with a single block read because the rows are together.  You can use 10g hash cluster tables, single table clusters, or manual row re-sequencing (CTAS with ORDER BY) to achieve this goal:

Oracle has had several methods for re-sequencing table rows together, but the best is the Oracle 10g sorted hash cluster:

• Single table cluster tables - This uses an overflow area
• CTAS with order by clause - The DBA manually reorgs the table, reordering the table rows together on adjacent data blocks.
• Sorted hash clusters - The new 10g way of assigning rows to adjacent data blocks.

Now that we understand the basics of table fragmentation let's examine some ways to reduce table fregmentation.

Fixing table fragmentation

If you experience slow SQL (ONLY for large index range scans and full-table scans), or if you want to reclaim disk space, Oracle has several tools to reorganize a table to remove fragmentation:

• If you want to reduce table fragmentation and force adjacent rows you can deploy sorted hash clusters but you must watch the overflow area within the table.
• "alter tablespace xxx coalesce" - This command will reclaim space from honeycomb fragmentation.
• "alter database datafile xxx.dbf resize yym;" - This will remove space that the physical "end" if the datafile, and the command will not work if any segments extend beyond your resize boundary.

Oracle leaves the high-water mark alone after rows are deleted, and you can reclaim space at the table level with these techniques, all of which lower the high water mark for the table, thereby freeing-up the space:

• export-import - For a complete reorganization and space reclamation, export/import allows you to restructure your files and reclaim lost space.
  
• dbms_redefinition - This procedure will reorganize a table while it remains online for updates.
  
• "alter table xxx shrink" - - If you were 10g and beyond you could use "alter table xxx shrink space compact;" syntax.  Also see the coalesce table syntax.

 Unlike the "deallocate unused space" syntax which removes space above the high-water mark, "coalesce" puts together discontiguous fragmented extents.  There are two type of space fragmentation in Oracle.  First is the honeycomb fragmentation, when the free extents are side by side, and the "Swiss Cheese" fragmentation, when the extents are separated by live segments.

alter table xxx coalesce; 

You can deallocate unused space - Oracle notes that the "deallocate unused space" clause is used to to explicitly deallocate unused space at "the end" of a segment and makes that space available for other segments within the tablespace.

alter table xxx deallocate unused space;
alter index xxx deallocate unused space;

Internally, Oracle deallocates unused space beginning from the end of the objects (allocated space) and moving downwards toward the beginning of the object, continuing down until it reaches the high water mark (HWM).  For indexes, "deallocate unused space" coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.

Regardless of  your approach for reclaiming disk space, you will need to runcomplex dictionary scripts to detect the areas of fragmentation, and these scripts can also be used to reclaim the space wastage.