HOW TO DEFRAGMENT TABLE FRAGMENTATION IN ORACLE

HOW TO DEFRAGMENT TABLE FRAGMENTATION IN ORACLE

Fragmentation is a uneven storage of data blocks in a table. A fragmented table is subjected to occur due to the DML transactions like delete (most often) and update statements.

Non-contiguous data blocks which means (not continuous) or row chaining lead to performance issues due to additional efforts needed by optimizer to scan unnecessary data blocks below the high water mark. These space cant be reuse for future inserts and wastage of space occur. Reclamation of the bloated space is tedious for large sparse tables and requires application downtime.

This situation is similar to a holes in a broken mirror after a gun fire (Delete statement) or bloating in human stomach (Update statement).

Example: If there is a requirement to scan 10 rows from a table and these rows are spread across multiple blocks in a million rows table, then consider if optimizer mistakenly choose a full table scan instead of index scan where multiple blocks need to be disposed.

Fragmented mirror which consist of broken layer with random holes which is how a table fragmentation occur.

Fragmented mirror illustration

Consider an example,

In below picture there are two sports men where one man run through hurdles and other on a plain surface. The man in plain surface can run faster in general compared to hurdle surface.

Similarly, optimizer has to scan through lot of hurdles to display the results. This makes the performance to slow down and make it hard to process and fetch the result sets from disk.

Sprint race

In below picture, oracle scans the fragmented area with difficulty and contiguous area easily. This will become problematic, when application is designed to throw full table scans a lot on large tables.

Fragmented table

Formula to identify the fragmentation mark at the tablespace level

Fragmentation mark = 100 * ( emax / ( e + e1 …. en ))1/2 / ( (et)1/4

Where emax – max extents ; ( e + e1 …. en ) – Sum of all extents ; et – Total number of extents

Fragmentations in the tablespace can be identified using the below query

set lines 200 pages 1000
col TABLESPACE_NAME for a20
col FRAGMENT_MARK for 999%
col FRAGMENT_GRAPH for a20
select TABLESPACE_NAME,
       ROUND(100 * (power(max(blocks) / sum(blocks),0.5) / ((power(count(blocks),.25))))) FRAGMENT_MARK,
	   case when 100 * (power(max(blocks) / sum(blocks),0.5) / ((power(count(blocks),.25)))) < 21
	   then 'highly fragmented'
	   when 100 * (power(max(blocks) / sum(blocks),0.5) / ((power(count(blocks),.25)))) <= 60 and 100 * (power(max(blocks) / sum(blocks),0.5) / ((power(count(blocks),.25)))) > 20
	   then 'Mild fragmented'
	   when 100 * (power(max(blocks) / sum(blocks),0.5) / ((power(count(blocks),.25)))) <= 99 and 100 * (power(max(blocks) / sum(blocks),0.5) / ((power(count(blocks),.25)))) > 60
	   then 'Less fragmented'
	   else 'Not fragmented'
	   end FRAGMENT_LEVEL,
	   rpad(' ',ROUND(100 * (power(max(blocks) / sum(blocks),0.5) / ((power(count(blocks),.25))))) / 10,'<>') FRAGMENT_GRAPH
from dba_free_space
group by tablespace_name
order by fragment_mark asc;

Output

TABLESPACE_NAME      FRAGMENT_MARK FRAGMENT_LEVEL    FRAGMENT_GRAPH
-------------------- ------------- ----------------- --------------------
USERS                            6 highly fragmented
UNDOTBS1                        59 Mild fragmented    <><>
EXAMPLE                         73 Less fragmented    <><><>
SYSTEM                          76 Less fragmented    <><><>
SYSAUX                          83 Less fragmented    <><><><
TBSPC_INDX                      84 Less fragmented    <><><><

6 rows selected.

In above output, USERS tablespace is highly fragmented due to delete statement and other DML operations.

This indication cause performance slowness on the processes which read and write the data using conventional path method on USERS tablespace files. Hence a defragmentation process should be deeply planned and to be implemented. In a production system, this activity will be a nightmare with downtime and data loss. Proper backup should be taken to be failsafe from corruption.

Fragmented can be simulated for a table by deleting rows in a random manner

SQL> delete from xtbl where salary between 1000 and 10000;

62890 rows deleted.

SQL> commit;

Commit complete.

SQL> delete from xtbl where salary between 1 and 300;

2171 rows deleted.

SQL> commit;

Commit complete.

Now that the tablespace which is fragmented is identified. Next move is to identify the tables which are fragmented under USERS tablespace

SQL> set lines 200 pages 1000
select * from (
select dt.table_name,
       dt.empty_blocks as "FBLOCKS",
       dt.blocks as "HWM",
       round((dt.num_rows * dt.avg_row_len/1048576),2) as "ASPACE",
           round(((dt.blocks * 8)/1024) - (dt.num_rows * dt.avg_row_len/1048576),2) as "DATABELOWHWM",
           dt.num_rows as "TOTAL_ROWS"
from dba_tables dt
where owner not in ('SYS','SYSTEM')
and
tablespace_name='USERS'
order by DATABELOWHWM desc)
where rownum < 6;
SQL>   2    3    4    5    6    7    8    9   10   11   12   13
TABLE_NAME              FBLOCKS        HWM     ASPACE DATABELOWHWM TOTAL_ROWS
-------------------- ---------- ---------- ---------- ------------ ----------
XTBL                          0      57903     353.81        98.55    7000000
MVSAL                         0      41148     252.72        68.75    5000000
DEPT                          0          5          0          .04          4
SUBCATEGORY_REF_LIST          0          5          0          .04         21
_NESTEDTAB
SALGRADE                      0          5          0          .04          5

XTBL and MVSAL are the top candidates for defragmentation. Check the DATABELOWHWM column for fragmented space.

Action Plan:

Before performing any physical changes in database, it is recommended to take DDL, physical and logical backup to ensure triple safety.

1)Take metadata definition backups of all the tables with the help of below link

2)Take RMAN and export backup of all the tables or tablespaces which needs to be defragmented.

RMAN> backup as compressed backupset database plus archivelog delete input;

3)Logical backup

expdp \'/ as sysdba\' directory=fragment dumpfile=defragment_%U.dmp parallel=16 tablespaces=USERS

Two ways to reorganize the collapsed segments

  • Reorg (move table to same or different tablespace) or Shrink segments
  • Expdp / Impdp the table

1)Reorg (move table to same or different tablespace)

Enable row movement ==> Move table to different tablespace ==> Move back to source ==> Rebuild index

--Enable row movement
SQL> alter table xtbl enable row movement;

Table altered.

--Move the fragmented table to different tablespace having sufficient free space
SQL> alter table xtbl move tablespace EXAMPLE parallel(degree 32) nologging;

Table altered.
--Move back the table to original tablespace
SQL> alter table xtbl move tablespace USERS parallel(degree 32) nologging;

Table altered.
kish@x3z6zx9<^>select index_name,status from dba_indexes where table_name='XTBL';

INDEX_NAME                     STATUS
------------------------------ --------
XIDX                           UNUSABLE

kish@x3z6zx9<^>alter index XIDX rebuild online;

Index altered.
kish@x3z6zx9<^>select index_name,status from dba_indexes where table_name='XTBL';

INDEX_NAME                     STATUS
------------------------------ --------
XIDX                           VALID

After defragmenting XTBL table, there is a slight increase in fragment mark from 6 to 20 on users tablespace

TABLESPACE_NAME      FRAGMENT_MARK FRAGMENT_LEVEL    FRAGMENT_GRAPH
-------------------- ------------- ----------------- --------------------
USERS                           20 highly fragmented  <                 <=============
EXAMPLE                         50 Mild fragmented    <><>
SYSTEM                          76 Less fragmented    <><><>
SYSAUX                          84 Less fragmented    <><><><
UNDOTBS1                       100 Not fragmented     <><><><><
TBSPC_INDX                     100 Not fragmented     <><><><><

6 rows selected.

2) Expdp / Impdp

Export the fragmented table

kish@x3z6zx9<^>create directory fragment as '/home/oracle';

Directory created.

kish@x3z6zx9<^>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@x3dbzx36 ~]$ expdp \'/ as sysdba\' directory=fragment dumpfile=defragment_%U.dmp parallel=16 tables=kish.xtbl

Export: Release 11.2.0.4.0 - Production on Sun May 8 13:25:14 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=fragment dumpfile=defragment_%U.dmp parallel=16 tables=kish.xtbl
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 454.3 MB
. . exported "KISH"."XTBL"                               385.3 MB 6934939 rows
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/defragment_01.dmp
  /home/oracle/defragment_02.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun May 8 13:25:32 2022 elapsed 0 00:00:16

Import the fragmented table

[oracle@x3dbzx36 ~]$ impdp \'/ as sysdba\' directory=fragment dumpfile=defragment_%U.dmp parallel=16 table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Sun May 8 13:39:49 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=fragment dumpfile=defragment_%U.dmp parallel=16 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "KISH"."XTBL"                               385.3 MB 6934939 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sun May 8 13:40:29 2022 elapsed 0 00:00:40

3)Shrink space

Shrink space is online operation which don’t require downtime but DML operations cannot happen due to exclusive locks. But this operation is faster.

kish@x3z6zx9<^>alter table xtbl enable row movement;

Table altered.

kish@x3z6zx9<^>alter table xtbl shrink space;

Table altered.

or

Shrink space compact clause is an online operation where DML operations can happen but slow compared to previous option.

kish@x3z6zx9<^>alter table xtbl shrink space compact;

Table altered.

Above options clear the empty spaces below HWM and reset the HWM to its original seat. Indexes are updated automatically after this activity.

Take schema stats relevant to the tablespace after defragmentation to update the optimizer statistics for good execution plan

kish@x3z6zx9<^>EXEC DBMS_STATS.GATHER_SCHEMA_STATS('KISH');

PL/SQL procedure successfully completed.

Here is a script for you to get an idea of the number of holes in the tablespace

In below example, there is a hole before XTBL table. The empty space or hole needs to be filled with some data to avoid space wastage.

col file_id for 999
col block_id for 99999999
col end_block for 99999999 
col blocks for 9999999999
col segment_name for a20
col partition_name for a20
col segment_type for a20
col ro for 999999
WITH FH as (
  select file_id,
      block_id,
      (block_id + blocks - 1) lblk,
      blocks,
      segment_name,
      partition_name,
      segment_type
    from dba_extents de
    where tablespace_name = 'USERS'
  UNION ALL
  select file_id,
     block_id,
     (block_id + blocks - 1) lblk,
     blocks,
    'hole' segment_name,
    'NULL' partition_name,
    'NULL' segment_type
   from dba_free_space dfs
   where tablespace_name = 'USERS'
   order by block_id,block_id)
  -- select * from(
select file_id,
       segment_name,
       partition_name,
	   segment_type,
	   case when segment_name = 'hole' 
       then 'OOOOOOOOOOO'
	   else '***********'
	   end Fragmented_blocks,
	   row_number () over (order by null) ro
from FH;

FILE_ID SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE         FRAGMENTED_      RO
-
      4 hole                 NULL                 NULL                 OOOOOOOOOOO     147
      4 XTBL                                      TABLE                ***********     148
      4 XTBL                                      TABLE                ***********     149
      4 XTBL                                      TABLE                ***********     150
      4 XTBL                                      TABLE                ***********     151
      4 hole                 NULL                 NULL                 OOOOOOOOOOO     152
      4 MVSAL                                     TABLE                ***********     153
      4 XPK_XTBL1                                 INDEX                ***********     154
      4 XPK_XTBL1                                 INDEX                ***********     155
      4 XPK_XTBL1                                 INDEX                ***********     156
      4 MVSAL                                     TABLE                ***********     157
      4 hole                 NULL                 NULL                 OOOOOOOOOOO     158
      4 XPK_XTBL1                                 INDEX                ***********     159
      4 XPK_XTBL1                                 INDEX                ***********     160
      4 XPK_XTBL1                                 INDEX                ***********     161
      4 XPK_XTBL1                                 INDEX                ***********     162
      4 XPK_XTBL1                                 INDEX                ***********     163
      4 XPK_XTBL1                                 INDEX                ***********     164
      4 XPK_XTBL1                                 INDEX                ***********     165
      4 XPK_XTBL1                                 INDEX                ***********     166
      4 XTBL                                      TABLE                ***********     167
      4 XTBL                                      TABLE                ***********     168
      4 XTBL                                      TABLE                ***********     169
      4 XTBL                                      TABLE                ***********     170
      4 hole                 NULL                 NULL                 OOOOOOOOOOO     171
      4 XPK_XTBL1                                 INDEX                ***********     172
      4 XPK_XTBL1                                 INDEX                ***********     173
      4 XPK_XTBL1                                 INDEX                ***********     174
      4 XPK_XTBL1                                 INDEX                ***********     175
      4 XPK_XTBL1                                 INDEX                ***********     176
      4 hole                 NULL                 NULL                 OOOOOOOOOOO     177
      4 MVSAL                                     TABLE                ***********     178
      4 XTBL                                      TABLE                ***********     179
      4 XTBL                                      TABLE                ***********     180
      4 XTBL                                      TABLE                ***********     181
      4 XTBL                                      TABLE                ***********     182
      4 hole                 NULL                 NULL                 OOOOOOOOOOO     183
      4 MVSAL                                     TABLE                ***********     184
      4 MVSAL                                     TABLE                ***********     185
      4 hole                 NULL                 NULL                 OOOOOOOOOOO     186
      4 MVSAL                                     TABLE                ***********     187
      4 hole                 NULL                 NULL                 OOOOOOOOOOO     188

Summary:

  • Fragmentation is injurious to database
  • It affects performance very badly since the storage become sparse from years or decades of deletion and updating.
  • Optimizer plans can be suboptimal in a fragmented table.
  • It is the responsibility of DBA to defragment at regular intervals.
  • It is also tedious in a production system to defragment in a large sparse storage.
  • Only way to improve performance is to create right indexes

Leave a Reply

%d bloggers like this: