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.
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.
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.
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