What is fragmentation in Oracle and Methods to Reorganization?

 

What is fragmentation in Oracle and Methods to Reorganization?

The rows are split into more than one block is called fragmentation. It decreases the block accesses and it slow the database performance. 

Fragmentation are two type one is table fragmentation and second is index fragmentation

How to find Table Fragmentation?

In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)).

This all is due to fragmentation in the table or stats for table are not updated into dba_tables.

Table fragmentation – when?

If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.

“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.

As we deleted 1000 records, the rows are removed, yet the high water mark stays high.

High Water Mark : Is  a pointer where oracle stops writing in the blocks and put a mark where it ends. OR  we can say its an Marker between Used and Unused Blocks.

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.

 

Reasons to Reorganization:-

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.

 

 We Can Reset HWM By Using These Methods.

 

1.  Alter Table Move.

2. Expdp / Impdp

3. Shrink Command.

4. CTAS

5. DBMS Redefinition Package.