How to Check Fragmentation in an Oracle Database

Database fragmentation can significantly impact performance, leading to slower queries and inefficient data retrieval. In Oracle databases, fragmentation typically occurs in the tablespace and can affect the efficiency of data storage and retrieval. Understanding how to check for fragmentation is essential for maintaining optimal database performance. This article provides a detailed guide on how to check for fragmentation in an Oracle database, including useful scripts and SQL queries to analyze fragmentation.

What is Fragmentation?

Fragmentation in a database refers to the inefficient use of disk space due to scattered data. This can occur at two primary levels:

Internal Fragmentation: Within a single database segment, where space is allocated but not used efficiently.

External Fragmentation: Across the database, where free space is fragmented across multiple segments.

Both types can affect database performance, making it crucial to regularly check and address fragmentation issues.

How to Check Fragmentation in an Oracle Database

1. Analyze Tables and Indexes

Fragmentation can occur in both tables and indexes. Oracle provides several methods to analyze and report on fragmentation.

Using DBMS_REDEFINITION

The DBMS_REDEFINITION package can help identify fragmentation in tables and indexes. Here's how you can use it:

Identify Fragmented Tables:

BEGIN     
  DBMS_REDEFINITION.CHECK_TABLE (table_name => 'YOUR_TABLE_NAME'); 
END;

This procedure checks the specified table for fragmentation and provides recommendations for redefinition.

Analyze Table Fragmentation:

SELECT 
    table_name,        
    num_rows,        
    blocks,        
    empty_blocks,        
    avg_row_len   
FROM dba_tables  
WHERE owner = 'YOUR_SCHEMA'    
AND table_name = 'YOUR_TABLE_NAME';

This query provides details about table size and space utilization.

Using DBMS_SPACE

The DBMS_SPACE package offers additional insights into fragmentation:

Check Table Fragmentation:

EXEC DBMS_SPACE.SPACE_USAGE (table_name => 'YOUR_TABLE_NAME');

This procedure gives detailed information about space usage in the specified table.

Analyze Segment Space:

SELECT 
  segment_name,        
  segment_type,        
  tablespace_name,        
  bytes,        
  extents,        
  blocks   
FROM dba_segments  
WHERE owner = 'YOUR_SCHEMA'    
AND segment_name = 'YOUR_SEGMENT_NAME';

This query provides an overview of space allocated to segments.

2. Monitor Fragmentation with SQL Queries

You can use SQL queries to assess fragmentation within your tables and indexes. Here are some useful queries:

Check for Free Space in Tablespaces:

SELECT 
  tablespace_name,        
  SUM(bytes_free) / 1024 / 1024 AS free_space_mb   
FROM dba_free_space  
GROUP BY tablespace_name;

This query shows the free space available in each tablespace, which can indicate fragmentation.

Analyze Index Fragmentation:

SELECT 
  index_name,        
  table_name,        
  NUM_ROWS,        
  LEAF_BLOCKS,        
  DISTINCT_KEYS,        
  AVG_LEAF_BLOCKS   
FROM dba_indexes  
WHERE owner = 'YOUR_SCHEMA'    
AND index_name = 'YOUR_INDEX_NAME';

This query provides details about index fragmentation.

Calculate Fragmented Space in Tables

To get a detailed analysis of fragmentation within tables, you can use the following query. This query calculates the total size of a table, the actual size used, the fragmented space, and the percentage of fragmentation:

SELECT      
  table_name,     
  avg_row_len,     
  ROUND(((blocks*16/1024)),2) || 'MB' AS "TOTAL_SIZE",     
  ROUND((num_rows*avg_row_len/1024/1024),2) || 'MB' AS "ACTUAL_SIZE",     
  ROUND(((blocks*16/1024) - (num_rows*avg_row_len/1024/1024)),2) || 'MB' AS "FRAGMENTED_SPACE",     
  (ROUND(((blocks*16/1024) - (num_rows*avg_row_len/1024/1024)),2) / ROUND(((blocks*16/1024)),2)) * 100 AS "PERCENTAGE" 
FROM all_tables  
WHERE ROUND(((blocks*16/1024)),2) > 0 
ORDER BY 6 DESC;

This query provides:

Total Size: The total size of the table.
Actual Size: The actual space used by the data in the table.
Fragmented Space: The amount of fragmented space.
Percentage: The percentage of fragmented space relative to the total size.

3. Use Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager provides graphical tools for monitoring and managing database performance. It includes features for analyzing fragmentation:

Navigate to the Performance Tab:

• Go to the Performance tab within OEM.
• Check the Tables and Indexes sections for fragmentation reports.

Use the Segment Advisor:

Access the Segment Advisor in OEM to get recommendations for reorganization and analysis of fragmentation.

4. Using the Fragmentation Analysis Script

To automate the process of checking fragmentation, you can use the following script. This script performs an analysis of fragmentation in tables and indexes and generates a report that can be used for further actions.

-- Define the schema and table names to check
DECLARE
    v_schema_name   VARCHAR2(30) := 'YOUR_SCHEMA';  -- Replace with your schema name
    v_table_name    VARCHAR2(30) := 'YOUR_TABLE_NAME'; -- Replace with your table name
    v_index_name    VARCHAR2(30) := 'YOUR_INDEX_NAME'; -- Replace with your index name
BEGIN
    -- Analyze table fragmentation
    DBMS_OUTPUT.PUT_LINE('Analyzing Table Fragmentation...');
    FOR rec IN (
        SELECT table_name, 
               num_rows, 
               blocks, 
               empty_blocks, 
               avg_row_len
          FROM dba_tables
         WHERE owner = v_schema_name
           AND table_name = v_table_name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Table: ' || rec.table_name);
        DBMS_OUTPUT.PUT_LINE('Rows: ' || rec.num_rows);
        DBMS_OUTPUT.PUT_LINE('Blocks: ' || rec.blocks);
        DBMS_OUTPUT.PUT_LINE('Empty Blocks: ' || rec.empty_blocks);
        DBMS_OUTPUT.PUT_LINE('Average Row Length: ' || rec.avg_row_len);
    END LOOP;

-- Analyze index fragmentation
    DBMS_OUTPUT.PUT_LINE('Analyzing Index Fragmentation...');
    FOR rec IN (
        SELECT index_name,
               table_name,
               NUM_ROWS,
               LEAF_BLOCKS,
               DISTINCT_KEYS,
               AVG_LEAF_BLOCKS
          FROM dba_indexes
         WHERE owner = v_schema_name
           AND index_name = v_index_name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Index: ' || rec.index_name);
        DBMS_OUTPUT.PUT_LINE('Table: ' || rec.table_name);
        DBMS_OUTPUT.PUT_LINE('Rows: ' || rec.NUM_ROWS);
        DBMS_OUTPUT.PUT_LINE('Leaf Blocks: ' || rec.LEAF_BLOCKS);
        DBMS_OUTPUT.PUT_LINE('Distinct Keys: ' || rec.DISTINCT_KEYS);
        DBMS_OUTPUT.PUT_LINE('Average Leaf Blocks: ' || rec.AVG_LEAF_BLOCKS);
    END LOOP;
END;
/

This script performs the following actions:

Analyzes Table Fragmentation: Retrieves and displays details about table size and space utilization.

Analyzes Index Fragmentation: Retrieves and displays details about index fragmentation.

Addressing Fragmentation

Once you’ve identified fragmentation, consider the following methods to address it:

1. Rebuild Indexes

Rebuilding indexes can help address fragmentation:

ALTER INDEX YOUR_INDEX_NAME REBUILD;

This command reorganizes the index structure and can improve performance.

2. Reorganize Tables

Reorganizing tables can help reclaim fragmented space:

ALTER TABLE YOUR_TABLE_NAME MOVE;

This command moves the table to a new segment, effectively defragmenting it.

3. Use Online Redefinition

For large tables, consider using online redefinition to minimize downtime:

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE (
      uname => 'YOUR_SCHEMA',
      orig_table => 'YOUR_TABLE_NAME',
      int_table => 'YOUR_TEMP_TABLE_NAME');
END;

This procedure allows you to reorganize tables while they remain accessible.

Conclusion

Checking and addressing fragmentation in an Oracle database is essential for maintaining performance and efficiency. By using the tools and methods outlined in this article, including the provided SQL queries and script, you can identify fragmented areas and take steps to reorganize and optimize your database. Regular monitoring and maintenance will help ensure that your Oracle database runs smoothly and efficiently, providing a better experience for users and applications relying on it.