remote dba support
More
    HomeDatabase ServicesFragmentation in Dictionary Managed Tablespace in Oracle Database

    Fragmentation in Dictionary Managed Tablespace in Oracle Database

    Preventing fragmentation in a tablespace is the most crucial part of database administration. It degrades performance and wastes disk space. The article explains how fragmentation occurs in dictionary-managed tablespace in the Oracle database.

    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 them.

    - Advertisement -
    dbametrix

    Tablespace fragmentation can be evidenced either as a contiguous chunk of free space (termed a 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 numerous bubbles or even a few large bubbles, then the chances of space-wastage are 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 extent if previously occupied are released. Other reasons for extent being released include the 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 in the case of a honeycomb, by default, the border around each bubble remains intact, thus separating them from each other.

    - Advertisement -
    dbametrix

    However, it is possible to collect them to bring the borders down and form a contiguous, large bubble. Also, if there are additional neighbouring 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 to that bubble. If the chunks released from dropping the table are not adjacent to each other nor are there any neighbouring chunks of free space available, each extent remains an independent bubble and a new extent can utilize the bubble only if the new extent’s a specific size of the bubble. This bubble is the main culprit to degrade your database performance.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

    This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    dbametrix
    - Advertisment -
    remote dba services

    Most Popular