Skip to main content
📓 Tibero Online Manual Click here 📓

Search

Welcome to Tibero GTS!

Data File Fragmentation Issue Guide

Document TypeㅣTroubleshooting

CategoryㅣMonitoring/Inspection

Applicable Product VersionㅣTibero 7.2.3

Document NumberㅣTMOTS027

Issue

When Extent Allocation is set to autoalloc and data additions and drop/truncate operations are repeatedly performed, a problem of increasing fragmentation within the data file space may occur.

Due to this fragmentation, over time it becomes increasingly difficult to allocate larger extents, and the system retries by reducing the extent size progressively. This process can cause performance degradation.
 
For example, this issue can appear in tasks such as storing transaction logs and periodically deleting them.
 
 

Cause

  1. Extent allocation method is set to autoalloc (default setting)
  2. When segment create/insert and drop/truncate operations are repeatedly performed periodically
    At this time, small extents allocated by the autoalloc method become fragmented within the space, and this fragmentation gradually accumulates, causing problems.
Note
  • If truncate or drop rarely occurs and a segment is continuously expanded after being created once, this issue does not occur.
  • In fact, the same issue has occurred in undo tablespaces, and there is a history of changing the undo segment allocation method to uniform to enforce this.
sys.log
[2025-09-10T21:45:44.285286] [TXT-209] [I] can't extend tablespace TS #3(USR)
[2025-09-10T21:45:44.285294] [FRM-205] [I] THROW. ec=ERROR_TX_CANT_ALLOC_EXT(-21004) [ No more extent available in tablespace 'USR'. ] (sql_id:99sjcprmyz8sv, sub_sql_id:99sjcprmyz8sv, csr_id:108702, user:TIBERO, ap_module:(null), program:conn_DPI, host:ys) [tx_ts.c:1672:ts_extend]
[2025-09-10T21:45:44.285301] [FRM-209] [I] THROW. ec=ERROR_TX_CANT_ALLOC_EXT(-21004) [ No more extent available in tablespace 'USR'. ] (sql_id:99sjcprmyz8sv, sub_sql_id:99sjcprmyz8sv, csr_id:108973, user:TIBERO, ap_module:(null), program:conn_DPI, host:ys) [tx_ts.c:1672:ts_extend]
[2025-09-10T21:45:44.285390] [FRM-194] [I] THROW. ec=ERROR_TX_CANT_ALLOC_EXT(-21004) [ No more extent available in tablespace 'USR'. ] (sql_id:99sjcprmyz8sv, sub_sql_id:99sjcprmyz8sv, csr_id:109139, user:TIBERO, ap_module:(null), program:conn_DPI, host:ys) [tx_ts.c:1672:ts_extend]
[2025-09-10T21:45:44.285440] [FRM-215] [I] THROW. ec=ERROR_TX_CANT_ALLOC_EXT(-21004) [ No more extent available in tablespace 'USR'. ] (sql_id:99sjcprmyz8sv, sub_sql_id:99sjcprmyz8sv, csr_id:122137, user:TIBERO, ap_module:(null), program:conn_DPI, host:ys) [tx_ts.c:1672:ts_extend]
[2025-09-10T21:45:44.285704] [FRM-204] [I] THROW. ec=ERROR_TX_CANT_ALLOC_EXT(-21004) [ No more extent available in tablespace 'USR'. ] (sql_id:99sjcprmyz8sv, sub_sql_id:99sjcprmyz8sv, csr_id:108837, user:TIBERO, ap_module:(null), program:conn_DPI, host:ys) [tx_ts.c:1672:ts_extend]
[2025-09-10T21:45:44.285720] [FRM-198] [I] THROW. ec=ERROR_TX_CANT_ALLOC_EXT(-21004) [ No more extent available in tablespace 'USR'. ] (sql_id:99sjcprmyz8sv, sub_sql_id:99sjcprmyz8sv, csr_id:108531, user:TIBERO, ap_module:(null), program:conn_DPI, host:ys) [tx_ts.c:1672:ts_extend]
 

Tablespace Query

SEGMENT_NAME    SEGMENT_TYPE      MB
------------    ------------      ----
DPI_TEST        TABLE             3088

1 row selected.

TABLESPACE_NAME    TOTAL_GB    USED_GB    FREE_GB    USED_PCT
---------------    --------    -------    -------    --------
UNDO               .13         .13        0          99.9
SYSTEM             .1          .09        0          97.19
SYSSUB             .06         .05        .01        78.98
USR                31.96       3.02       28.94      9.44

 

Solution

Prevention

  • If the task is likely to cause fragmentation, it is recommended to set the tablespace extent management to uniform 64M or similar from the beginning.
  • Alternatively, consider creating a new tablespace set to uniform and gradually migrating to it.
 

After-the-fact Measures

  • Once fragmentation has progressed, there is no appropriate solution other than dropping all data and reloading it.
Note
Diagnosis Method
  • In TPR, if the sgmt alloc ext time in Workload Stats (Time-based) occupies 2-30% or more, up to over 90% compared to insert time
  • When querying the dba_free_space view, if the number of rows is very large and the block count and size bytes per row are small
     (mostly 16 blocks, 128K)
  • If the ratio of sgmt alloc ext from datafile time in Workload Stats (Time-based) is high (2-30% or more, up to over 90%) and the size (alloc fail count) value approaches num (number of allocation attempts while scanning datafiles)
  • Check for contiguous blocks by querying the DBA_FREE_SPACE view
# There are contiguous blocks from BLOCK_ID for BLOCKS number of blocks, and the total size is BYTES #
SQL> select * from dba_free_space;

TABLESPACE_NAME         FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
-------------------- ---------- ---------- ---------- ---------- ------------
SYSTEM                        0      12775     262144         32            0
USR                           2          7    1048576        128            2
SYSSUB                        3      22567    7864320        960            3
SYSSUB                        3      23751    2883584        352            3
SYSSUB                        3      24279    1835008        224            3
SYSSUB                        3      27415    3276800        400            3
SYSSUB                        3      27879    3801088        464            3
SYSSUB                        3      30103    4718592        576            3
SYSSUB                        3      31127    4587520        560            3
... truncated ...
32 rows selected.

SQL>
 
 
 

Related to