Oracle Database Fragmentation - How to reorg Tables / Indexes

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

Database fragmenmtation from 12.2 onwards is using alter table move ( the main of dbms_redefinition in 11g versions is too miuch to handle )

In simple terms to reorg a table

## For a small Table 
alter table move scott.hr ;

## For a small Index
alter index rebuild scott.idx1 ;

For Tables where you "know" which are the reorg candidates

But what happens in a scenario where you need to reorg a large table in a small downtime ( provided you have sufficient CPU's )

You could easily enable parallelism for the mobe/rebuild operations
and disable parallelism

## For a small Table 
alter table move scott.hr parallel 12;
alter table scott.hr noparallel;

## For a small Index
alter index rebuild scott.idx1 parallel 12;
alter index noparallel;

VLDB's - high fragmentation across schemas

The problem with large databases where they are fragmentation prone due to exessive DML's. This is where the issue comes for DBA's where you don't know how to come up with a reorg startegy.

A DBA is often faced with these for reorg

1. You have several schemas in several TB's
2. You are not in a position to decide which tables/index to reorg
3. You have a reasonably small downtime to do what you can
4. You want the reorg to be human error free
5. You also have the headache of having a standby database where the exessive archive logs need to apply.

When there is a problem SIMPLIFY

All DBA's need to do get a head start

1. Identify which tables/indexes are fragemented
2. Work with application teams to see which are important ones , try to understand the business need of the tables
3. Test the fragementation approach in Non Prod's

Use my reorg toolkit

https://github.com/abhilash-8/reorg

Database DeFragmentation > 12c - Use the scripts as needed for your environment . It's higly recommended to test the reorg scripts in Non Prod Environments.

Identify Fragmentation

Use the frag.sql to list the tables which are fragemented

for. e.g. to identify fragmentation > 70% , this script will also identify which partitions are fragmented.

SQL> @frag 70

Generate reorg scripts (for non partitioned tables)

Use the reorg_gen_nopart.sql , the script will spool the DDL reorg scripts for non partitioned tables

for e.g. to generate reorg scripts for SCOTT.HR table

SQL> @reorg_gen_nopart.sql SCOTT HR

Generate reorg scripts (for partitioned tables)

Use the reorg_gen_part.sql , the script will spool the DDL reorg scripts for partitioned tables
Use the reorg_gen_nonpart_ind_part_tab.sql.sql , the script will spool the DDL reorg scripts for global indexes for partitioned tables

for e.g. to generate reorg scripts for SALES.INVOICES table for partitions JAN2018 , FEB2018 , MAR2018

SQL> @reorg_gen_nopart.sql SALES INVOICES JAN2018
SQL> @reorg_gen_nopart.sql SALES INVOICES FEB2018
SQL> @reorg_gen_nopart.sql SALES INVOICES MAR2018

It's highly recommended to reorg global indexes when partitions are reorg'd

SQL> @reorg_gen_nonpart_ind_part_tab.sql SALES INVOICES

Statistics

All reorg DDL scripts come will auto generate the DBMS_STATS scripts , you can customise it to your needs

Finally the reorg

Use the Dynamic DDL scripts for reorg , you could use them manually or run them in batches , decide on what works best for your databases.