Re-Organizing Fragmented Table in Oracle

In this post we'll see about fragmentation and methods to re-organize a fragmented table in oracle.

What is HWM?

HWM is an acronym stands for the word High Water Mark. HWM is nothing but a point shows the tables point at which it consumed the most extents.

What is Fragmentation?

Fragmentation is nothing but the holes left in a table below the HWM. Holes is nothing but a free block is left unclaimed below the HWM.

When Fragmentation Happen?

When an insert happens in the table, the HWM moves forward to accommodate new blocks in the table. As per the logic when a delete happens, the HWM should move backward to release and claim that space, but this doesn't happens in oracle and left a free block below the HWM. Now after the hole is created, if you insert into the same table, HMW will move forward to make room for the new block of data leaving the hole as it is. Now the fragmentation occurs in that table.

During Insert:

During Delete:

Fragmentation Effects

Causes slow scan activity and wasted disk space. From performance perspective, SQL query looks for a single row will not face any performance issue, whereas a full table scan will face performance issues.

How to Re-Organize a table

The fragmented table can be optimized for a better performance through table re-organization.

Re-Organizing Techniques

1. CTAS
2. Datapump
3. Move the table
4. Shrink the segment

Here I'll show the fist two techniques with example.

CTAS

High-Level steps are

1. Gather stats for the table
2. Check the table for Fragmentation
3. Re-Organize the table

Gather stats for the table:

Use the below command to gather status for the table you want to check fragmentation.

exec dbms_stats.gather_table_stats('&SCHEMA_NAME','&TABLE_NAME');

Check the table for Fragmentation:

Here we'll check various things on a table which we identified for re-org operation.

Use the below query to check the table size and the amount of actual data present in that table.
select owner,table_name,round((blocks*8),2)||' kb' "TABLE SIZE",round((num_rows*avg_row_len/1024),2)||' kb' "ACTUAL DATA" from dba_tables where table_name='&TABLE_NAME';

Use the below script to check the table for fragmentation.
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&OWNER', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/

Check the table for any LOB segments.
desc OWNER.TABLE_NAME;

Here no LOB segments are found

Check the table for Partitions.
col TABLE_OWNER for a5
col TABLE_NAME for a10
col PARTITION_NAME for a20
col COMPOSITE for a10
set lines 500
set pages 100
select TABLE_OWNER,TABLE_NAME,COMPOSITE,PARTITION_NAME from DBA_TAB_PARTITIONS where TABLE_NAME='&TABLE_NAME' order by PARTITION_NAME asc;

Here no partitions found in the table


Re-Organize the table:
Export the table.
nohup expdp system/********** tables=JA.JAI_REPORTING_ASSOCIATIONS directory=DUMP_DIR dumpfile=EXP_JAI_REPORTING_ASSOCIATIONS.dmp logfile=EXP_TABLE.log &

Here we are exporting the table for re-building the indexes after the table has been re-organized.

Create CTAS Backup.
create table JA.JAI_REPORTING_ASSOCIATIONS_BKP as select * from JA.JAI_REPORTING_ASSOCIATIONS;

Drop the table include indexes.
drop table JA.JAI_REPORTING_ASSOCIATIONS cascade constraints;

Rename the backup table to original name
alter table JA.JAI_REPORTING_ASSOCIATIONS_BKP rename to JA.JAI_REPORTING_ASSOCIATIONS;

Here facing issue, since the SYS user is not the owner of this table. So connect as the respective owner and then drop the table.

 

alter table JAI_REPORTING_ASSOCIATIONS_BKP rename to JAI_REPORTING_ASSOCIATIONS;

Import the table to rebuild the indexes.
nohup impdp system/********** tables=JA.JAI_REPORTING_ASSOCIATIONS directory=DUMP_DIR dumpfile=EXP_JAI_REPORTING_ASSOCIATIONS.dmp logfile=IMP_TABLE.log &

Now re-run the queries to check the re-org has been done properly.

Datapump

High-Level steps are:

1. Gather stats for the table
2. Check the table for Fragmentation
3. Re-organize the table

Gather stats for the table

Just a day before we gathered stats on this table, so no need to run stats now.

Check table for Fragmentation

Use the following queries to check the details of fragmentation on the identified table.

col OWNER for a10
col TABLE_NAME for a30
col TABLE_SIZE for a15
col ACTUAL_DATA for a15
select owner,table_name,round((blocks*8),2)||' kb' "TABLE_SIZE",round((num_rows*avg_row_len/1024),2)||' kb' "ACTUAL_DATA" from dba_tables where table_name='&TABLE_NAME';

set lines 300
col TOTAL_SIZE for a15
col ACTUAL_SIZE for a15
col FRAGMENTED_SPACE for a30
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';

col OWNER for a10

col Expected_W/O_Fragmentation for a30
col Fragmented_Size for a20
col Actual_Size for a15
select owner, table_name, round((blocks * 8), 2) || 'kb' "Fragmented_Size",
round((num_rows * avg_row_len / 1024), 2) || 'kb' "Actual_Size",
round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2) || 'kb' "Expected_W/O_Fragmentation",
((round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) /
round((blocks * 8), 2)) * 100 - 10 "reclaimable space % "
from dba_tables
where table_name = '&TABLE_NAME'
AND OWNER LIKE '&SCHEMA_NAME';

col owner format a10
col TABLE_NAME for a30
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks'      just c
column hgwtr heading 'High|Water'         just c
break on owner skip page
set verify off
select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
order by 1,2;

set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&OWNER', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/

desc OWNER.TABLE_NAME;

select TABLE_OWNER,TABLE_NAME,COMPOSITE,PARTITION_NAME from DBA_TAB_PARTITIONS where TABLE_NAME='&TABLE_NAME' order by PARTITION_NAME asc;

nohup expdp system/********** tables=APPLSYS.WF_NOTIFICATION_ATTRIBUTES directory=DUMP_DIR dumpfile=EXP_TABLE.dmp logfile=EXP_TABLE.log &

drop table APPLSYS.WF_NOTIFICATION_ATTRIBUTES cascade constraints;


nohup impdp system/********** tables=APPLSYS.WF_NOTIFICATION_ATTRIBUTES directory=DUMP_DIR dumpfile=EXP_TABLE.dmp logfile=IMP_TABLE.log &

Now re-check all the queries to check the re-org has been done successfully.

Here in the above picture water mark is not changed which seems the re-org is not happened as expected, but in the below picture all the blocks are cleared and only the Full blocks is having data which means the re-org is done properly.

So to make sure the HWM is drawn back to the rightful posiotion in the table, run gather stats for that particular table and check once again.

exec dbms_stats.gather_table_stats('&SCHEMA_NAME','&TABLE_NAME');

Now we've seen re-org the fragmented table in two ways. Hope this will be helpful for you.

Ref : How to Reorganize a Table (Doc ID 151588.1)         How to Find Fragmentation for Tables and LOBs (Doc ID 2132004.1)

                        ****************************நன்றி****************************