Thalasar Ventures

Tablespace fragmentation is fairly common in most databases, and to a certain degree, it is almost inevitable, unless specific steps are taken to prevent it right from the time of initial database configuration and segment creation. Let’s take a look at tablespace fragmentation to understand the issues involved and some preventive and ameliorative measures necessary to overcome it.

Tablespace fragmentation can be evidenced either as a contiguous chunk of free-space (termed as bubble) or as a series of chunks appearing adjacent to each other (termed as a honeycomb). Thus, a bubble comprises one or more blocks that are coalesced, whereas a honeycomb comprises multiple bubbles. A honeycomb can be coalesced to form a single large bubble. Bubbles exist between subsequent extents allocated for segments stored in that tablespace. A small bubble, comprising one or just a few blocks, may result in wastage of space unless it is able to accommodate newer extents of segments within the tablespace. In case of honeycombs, if they comprise a large number of bubbles or even a small number of large bubbles, then chances of space-wastage is minimal, since the honeycomb will be able to accommodate new extent allocations. However, small honeycombs consisting of two or three block bubbles, which are fairly common, are more likely to result in space-wastage, unless the extent sizes of segments that need to expand are small enough to fit within them.

Both bubbles and honeycombs are remnants of extent de-allocations within the tablespace. When a table is dropped or truncated, the extents if previously occupied are released. Other reasons for extents being released include use of the SQL command of de-allocate. Additionally, in the case of rollback segments or undo segments, extents may be released when the segment is shrunk. If the released extents are adjacent to each other, they form a honeycomb. Otherwise, f they are scattered throughout the tablespace, each extent effectively remains as a bubble. Also, if some of these de-allocated extents are located next to pre-existing bubbles, then they collectively form a honeycomb along with those older bubbles. Note that is in the case of honeycomb, by default, the border around each bubble remains intact, thus separating them from each other. However, it is possible to collect them to bring the borders down and form a contiguous large bubble. Also, if there are additional neighboring free-space chunks, they can be made part of this large chunk. Whenever a new extent needs to be allocated for a segment and the size of the new extent is lesser than or equivalent to the largest free-space bubble available, the extent can be allocated onto that bubble. If the chunks released from dropping the table are not adjacent to each other nor are there any neighboring chunks of free-space available, each extent remains an independent bubble and a new extent can utilize the bubble only if the new extents specific size of bubble.

Related Oracle Articles