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
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.
Here is a handy script to locate highly fragmented tables:
"size (kb)" ,
round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
order by 4 desc;
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 groups storage 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 tables using the Oracle dbms_redefinition
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.
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
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.
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
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
. 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 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.
fragmentation - Some
shops will improve SQL speed by deploying sorted hash clusters,
thereby placing related rows into a single data block.