Oracle Table Fragmentations and Defragmentation

Symptom 1. What does space utilization and fragmentation mean? 2. Which types of fragmentation are there? 3. Which other factors are important with regard to space utilization? 4. What are the disadvantages of fragmentation and poor space utilization? 5. How can I determine the scope of the different fragmentation types and the unused space? 6. How can I reduce fragmentation and improve space utilization? Other terms FAQ, frequently asked questions Solution 1. What does space utilization and fragmentation mean? The term 'space utilization' refers to the percentage of available space that is actually filled with user data. You can evaluate the space utilization on different levels (file system, tablespace, segment, block). For detailed information about the logical components of the Oracle database,  such as tablespaces, segments, and extents, refer to Note 666061. The term 'fragmentation' refers to unused space in the database that cannot be used for technical reasons. Frequently, the effects of fragmentation are responsible for poor space utilization. 2. Which types of fragmentation are there? Block fragmentation ("internal fragmentation") The term 'block fragmentation' refers to the average space utilization in an Oracle block. Depending on the segment type in question, the following subtypes occur : Index fragmentation                     The term 'index fragmentation' refers to block fragmentation in an index tree. Index fragmentation is caused by repeatedly deleting and entering data, in particular if you usually delete the entries on one side of the index and make new entries on the other side. For more information, refer to Note 771929 . Index fragmentation also occurs when you delete a large number of table entries and therefore the index entries are deleted as well. Table fragmentation The term 'table fragmentation' refers to block fragmentation in a table. Table fragmentation mainly occurs when you delete a large amount of data from a table. After the deletion, the blocks that are still allocated contain considerably less data than before, which leads to poorer space utilization. LOB segment fragmentation                     LOB columns are generally paged out into separate LOB segments (see Note 500340). Unfavorable space utilization in an LOB segment is often caused by the fact that a lot of undo information is stored in the segment itself. To limit the scope of retained undo information, make sure that PCTVERSION storage parameter is not set too high. Default value 10 is usually sufficient. Tablespace fragmentation ("external fragmentation")            Tablespace fragmentation occurs if a tablespace contains a large number of small freespace areas that cannot be reused due to their size.            Tablespace fragmentation is mainly a DMTS problem. Due to the AUTOALLOCATE and UNIFORM assignment strategies, fragmentation effects rarely occur in LMTS  (see Note 214995). File system space utilization            Space utilization in the file systems of the Oracle data files does not affect the datafiles directly, but it may be critical due to the cost of disk space. If the file system is configured considerably larger than the datafiles contained in the file system, a lot of space is wasted. 3. Which other factors are important with regard to space utilization? By default, indexes are created with PCTFREE set to 10, which means that 10% of every index block is  reserved for future updates. Frequently, this 10 % is permanently unused space in index blocks. Due to the "PCTFREE = 10" and "PTCUSED = 40" storage parameters used in the SAP environment,  the fill levels of Oracle blocks vary between 40 % and 90 %, even if you do not delete large data volumes.  Therefore, the space that is actually used is significantly less than 100 % simply because of these storage parameters.  PCTUSED no longer has a function if you use ASSM (see Note 620803). However, PCTFREE is still taken into account. Due to an increase in INITRANS (in accordance with Note 84348, for example), more space for the transaction slots  is required in every block. Each INITRANS slot requires about 20 bytes.  The higher the INITRANS selected, the less space is available for user data. If a segment is created with Parallel Query, each Parallel Query Thread allocates separate extents.  As a result, more space may be allocated than during the sequential creation of a segment. The last extent of each segment is usually only filled partially. The block that was filled last is specified by the  High Water Mark. Usually, all blocks that are located before the High Water Mark have been filled at some point.   However, when you use ASSM (Note 620803), several FREELISTs or several FREELIST GROUPs, blocks that have  not been used yet may be located before the High Water Mark. The larger the last extent, the more space may be wasted. To avoid empty tables and indexes using up space  unnecessarily, SAP creates segments with a very small INITIAL extent size (for example, 16K for DMTS, see also  tables TGORA and IGORA). If segments with an INITIAL or NEXT size that is too big (due to a handling or tool error)  are created , a lot of space is lost due to unused extent areas that are unnecessarily large.  The space lost due to unused extent areas can also be considerable if there is a large number of small objects.  If a tablespace is created as LMTS or AUTOALLOCATE, the segments are always created with extents of at least 64 K.  As a result, LMTS or AUTOALLOCATE lead to a slightly higher space utilization in empty tables and indexes than DMTS. The space currently unused in a tablespace depends on the tablespace size and the segments contained in  the tablespace. If a tablespace is defined as relatively large from the start, the space utilization is relatively small. If you use long LONG RAW column values, extreme fragmentations may occur in tables such as DDLOG or VBDATA  as a result of the Oracle response described in Note 830965. As part of a Unicode conversion to Basis Release 7.00, the LOB columns of the DYNPSOURCE table may grow  significantly. For more information, see Note 1010237. 4. What are the disadvantages of fragmentation and poor space utilization? Increased space requirements            Due to the poor utilization of the existing space, the database requires more space. Longer runtimes for Backup and Restore            The more space required by the database, the longer the runtimes for operations such as Backup and Restore. Performance problems due to index fragmentation            If indexes are fragmented, SEVERE performance problems may occur when you access the indexes. Performance problems due to table fragmentation            Table fragmentation may also have a negative effect on system performance when the relevant tables are accessed. However, in general, the consequences are less severe than in the case of index fragmentations. Global performance reduction due to buffer pool displacements            The poorer the space in index and table blocks is utilized, the more blocks need to be loaded into the Oracle buffer pool during the data access. As a result, more blocks of other segments are displaced. These blocks must be reloaded into the buffer later, which generally impairs database performance. Higher costs for the disk space used            Often, the disk space used incurs costs. These costs increase with the amount of disk space required. 5. How can I determine the scope of the different fragmentation types and the unused space? To determine the degree of fragmentation for the different fragmentation types, proceed as follows: Index fragmentation  You can determine the index fragmentation by determining the index storage quality or the deleted leaf rows as described in Note 771929. Table fragmentation  You can use the following statement to determine the tables with the - in absolute figures - largest unused space: SELECT * FROM (SELECT     SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,     NUM_ROWS,     AVG_ROW_LEN ROWLEN,     BLOCKS,     ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,     ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *       (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,     ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -       (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"   FROM DBA_TABLES   WHERE     NUM_ROWS IS NOT NULL AND     OWNER LIKE 'SAP%' AND     PARTITIONED = 'NO' AND     (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)   ORDER BY 7 DESC) WHERE ROWNUM <=20; The above query is based on statistics values such as BLOCKS or NUM_ROWS. For correct results, you must therefore ensure that the statistics are up-to-date. The system ignores tables without statistics. Statistics created with DBMS_STATS did not take LONG, LONG RAW, and LOB columns into account correctly. For this reason, these tables can be displayed as highly fragmented. This is incorrect. For segments in ASSM tablespaces, you can create a segment dump that displays the levels to which the individual segment blocks are filled (this level is defined in the bitmap blocks). To do this, proceed as follows: SELECT HEADER_FILE, HEADER_BLOCK FROM DBA_SEGMENTS   WHERE SEGMENT_NAME = '<segment_name>'; EXEC DBMS_SPACE_ADMIN.SEGMENT_DUMP('<tablespace_name>',   <header_file_from_above>, <header_block_from_above>); You can also use BRCONNECT to create statistics for space utilization. For more information, see Note 554031. As of Oracle 10g, the DBMS_SPACE function (described in Note 1295200) for determining table fragmentation is available. The Oracle Segment Advisor (Note 927813) is also based on this function and therefore can be used to determine fragmentation. LOB segment fragmentation You can use DBMS_LOB.GETLENGTH to determine the space used within an LOB segment. You can use the following script to determine the net allocation of the existing LOB segments: connect <sapuser>/<password> SET HEADING OFF SET FEEDBACK OFF SET ECHO OFF SET PAGESIZE 50000 SET SQLPROMPT '' SPOOL lob.sql SELECT   'SELECT     ''' || TABLE_NAME || ''' TABLE_NAME,     ''' || COLUMN_NAME || ''' COLUMN_NAME,     ROUND(SUM(DBMS_LOB.GETLENGTH("' || COLUMN_NAME || '"))       / 1024 / 1024) "NET (MB)"   FROM     "' || USER || '"."' || TABLE_NAME || '";' FROM   USER_LOBS; SPOOL OFF COLUMN TABLE_NAME FORMAT A30 COLUMN COLUMN_NAME FORMAT A25 SET HEADING ON @lob SET FEEDBACK ON SET SQLPROMPT 'SQL>' In the next step, you can compare the net demand determined in this way with the BYTES value from DBA_SEGMENTS, if you suspect the space is being utilized poorly. Tablespace fragmentation To determine the number of unused space fragments in a table space and their breakdown into different sizes, proceed as follows: SELECT BLOCKS, COUNT(*) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = '<tablespace_name>' GROUP BY BLOCKS ORDER BY BLOCKS; Provided that the statistics are up-to-date, you can determine the number of blocks on the other side  of the High Water Mark as follows: SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = '<table>'; You can use the following statement to determine all tables that have more than 1,000 unused blocks: SELECT TABLE_NAME FROM DBA_TABLES WHERE EMPTY_BLOCKS > 1000; You can use the DBMS_SPACE.UNUSED_SPACE function to determine the number of blocks on the  other side of the High Water Mark of an index: VARIABLE TBL NUMBER; VARIABLE TBY NUMBER; VARIABLE UBL NUMBER; VARIABLE UNUSED_BYTES NUMBER; VARIABLE LUEFI NUMBER; VARIABLE LUEBI NUMBER; VARIABLE LUB NUMBER; BEGIN   DBMS_SPACE.UNUSED_SPACE('<owner>', '<index_name>', 'INDEX',     :TBL, :TBY, :UBL, :UNUSED_BYTES, :LUEFI, :LUEBI, :LUB); END; / PRINT UNUSED_BYTES; The percentage of space in a tablespace used by extents can be determined as follows: SELECT TO_CHAR(   (1 - (SELECT SUM(BYTES) FROM DBA_FREE_SPACE   WHERE TABLESPACE_NAME = '<tablespace_name>') /   (SELECT SUM(BYTES) FROM DBA_DATA_FILES   WHERE TABLESPACE_NAME = '<tablespace_name>'))   * 100,   999999999999.99) "% (USED)" FROM DUAL; To determine the freespace in file systems, you can use operating system tools (for example, "df -m" in UNIX). 6. How can I reduce fragmentation and improve space utilization? You can reduce the different fragmentation types as follows: General segment fragmentation As of Oracle 10g, you can use the Oracle Segment Advisor for a general analysis of segments, such as tables, indexes, or LOBs. For more information, see Note 927813. Index fragmentation You can use REBUILD, COALESCE or DROP and CREATE to eliminate index fragmentation. If you want to use these functions, refer to Notes 771929 and 332677 for more information. Table fragmentation Allocated blocks are usually poorly utilized after you have deleted large volumes of data from the table (for example, while archiving). If you know that the size of the table will in future be similar to its size before you deleted the data, you do not have to intervene. However, if the table will permanently have less data, you can reconstruct it during a reorganization to reduce table fragmentation (see Note 541538). As of Oracle 10g,  you also have the option of using Segment Shrinking (Note 910389). Refer to Note 572060 for more information. If the fragmentation has LONG RAW fields due to the Oracle response described in Note 830965, refer to the solutions listed there. If an ASSM segment requires an unnecessary amount of space because the levels of filled space defined in the bitmap blocks do not reflect reality, and because they accept blocks that are far too full (for example, due to Oracle bugs), you can correct the inconsistencies as follows: EXEC DBMS_REPAIR.SEGMENT_FIX_STATUS('<owner>','<segment_name>'); After you have done this, all free areas can be used again productively. For more information, see Note 896717. LOB segment fragmentation You can clean up LOB segment fragmentation by carrying out a reorganization (of the relevant table) and, where necessary, by reducing PCTVERSION (recommended: <= 10). Tablespace fragmentation If tablespaces are fragmented in DMTS due to a large number of small areas of unused space, you can use COALESCE to combine adjacent areas of unused space: ALTER TABLESPACE <tablespace_name> COALESCE; Note that this operation may cause problems with the ST enqueue if there are a lot of areas with unused space (see Note 745639). If the number of areas with unused space remains high after a COALESCE, you can defragment the tablespace by reorganizing it (see Note 541538). To reduce tablespace fragmentation, we recommend that you use the "brconnect -f next" function to choose adequate sizes for the NEXT extent for growing segments. This way, you can keep fragmentation to a minimum. In the medium-term and the long-term, you can reduce tablespace fragmentation by changing to LMTS (see Note 214995). The space utilization of an index can be improved by decreasing the value of PCTFREE for the relevant index.  However, this may lead to an increased number of index block splits and should therefore only be used in  exceptional cases. To avoid space losses due to PCTUSED, consider switching to ASSM (Note 620803), where this  parameter is no longer relevant. Occasionally (see, for example, Note 70513), you can also adjust PCTFREE and PCTUSED of a table during a  reorganization for a better utilization of the blocks and to reduce the size of the table. In individual cases, the unused space on the other side of the High Water Mark of the segment can be released  by using DEALLOCATE UNUSED as described in Note 570832. In general, you do not have to intervene and remove  the currently unused areas. You should only use this option if too much space was inadvertently allocated to the  last extent, or as a temporary alternative to avoid carrying out a reorganization. If a tablespace requires a large amount of space because the data file is too large, you can reduce the  size of the data file by using RESIZE: ALTER DATABASE DATAFILE '<file>' RESIZE <new_size>; Alternatively, you can also reduce the size by reorganizing the tablespaces (with data files). To optimize space utilization in file systems with the Oracle data files, consider the following: The freespace in the file system should be specified so you can carry out expected AUTOEXTEND operations  or manual tablespace extensions without causing a file system overflow. The fewer file systems you use for the data files, the smaller the "safety margin" you require in total on file system  level. You can use one single file system for all data files. (You should distribute the file system over as  many disks as possible.) However, depending on the file system used, it is difficult to reduce an existing file system.  If a reduction makes sense to avoid unused space, but you cannot carry out this step for technical reasons,  you can move data files or reorganize tablespaces to move to a new, smaller tablespace.

Related Notes
1295200	Oracle 10g or higher: Space statistics based on DBMS_SPACE
1010237	Growth of DYNPSOURCE table after Unicode migration
948197	Merge fix for DBMS_STATS package on Oracle 9.2.x and 10.2.x
927813	Oracle 10g: Using Oracle Segment Advisor to optimize space
910389	FAQ: Oracle Segment Shrinking
896717	Problems with tables in ASSM tablespaces
832343	FAQ: Clustering factor
771929	FAQ: Index fragmentation
745639	FAQ: Oracle enqueues
666061	FAQ: Database objects, segments and extents