Query table fragmentation

In a database, table fragmentation refers to the disk space that has been allocated but is not actually storing any data. Table fragmentation is primarily caused by the following operations:

• Frequent DELETE or UPDATE operations: After data is deleted, the original space is marked as "reusable" but is not immediately reclaimed, creating gaps or fragments.

• Page split: When data updates cause a page to become full, the storage engine splits the page into multiple non-contiguous blocks, leading to fragmentation.

In OceanBase Database V4.x, you can query table fragmentation using the CDB_OB_TABLE_SPACE_USAGE view (for the sys tenant) or the DBA_OB_TABLE_SPACE_USAGE view (for user tenants).

• Sys tenant

  In the sys tenant, you can use the following statement to query:

  obclient> SELECT DATABASE_NAME,TABLE_NAME,
    ROUND(OCCUPY_SIZE / 1024 / 1024 / 1024, 2) AS DATA_GB,
    ROUND(REQUIRED_SIZE / 1024 / 1024 / 1024, 2) AS ALLOCATED_GB,
    ROUND((REQUIRED_SIZE - OCCUPY_SIZE) / 1024 / 1024 / 1024, 2) AS EMPTY_GB
  FROM oceanbase.CDB_OB_TABLE_SPACE_USAGE
  WHERE TENANT_NAME = 'tenant_name'
  ORDER BY EMPTY_GB DESC;
  

• User tenant

  In a MySQL-compatible user tenant, you can use the following statement to query:

  obclient> SELECT DATABASE_NAME,TABLE_NAME,
    ROUND(OCCUPY_SIZE / 1024 / 1024 / 1024, 2) AS DATA_GB,
    ROUND(REQUIRED_SIZE / 1024 / 1024 / 1024, 2) AS ALLOCATED_GB,
    ROUND((REQUIRED_SIZE - OCCUPY_SIZE) / 1024 / 1024 / 1024, 2) AS EMPTY_GB
  FROM oceanbase.DBA_OB_TABLE_SPACE_USAGE
  ORDER BY EMPTY_GB DESC;
  

  In an Oracle-compatible user tenant, you can use the following statement to query:

  obclient> SELECT DATABASE_NAME,TABLE_NAME,
    ROUND(OCCUPY_SIZE / 1024 / 1024 / 1024, 2) AS DATA_GB,
    ROUND(REQUIRED_SIZE / 1024 / 1024 / 1024, 2) AS ALLOCATED_GB,
    ROUND((REQUIRED_SIZE - OCCUPY_SIZE) / 1024 / 1024 / 1024, 2) AS EMPTY_GB
  FROM SYS.DBA_OB_TABLE_SPACE_USAGE
  ORDER BY EMPTY_GB DESC;
  

In the statements:

• DATA_GB: The actual data storage size after table compression.

• ALLOCATED_GB: The actual disk space occupied by the data after table compression.

  OceanBase Database allocates storage in macroblocks (default size is 2 MB). This value represents the total number of allocated macroblocks, even if they are not fully filled.

• EMPTY_GB: The fragment space, calculated as ALLOCATED_GB - DATA_GB. This value represents the storage space that is not effectively utilized.