Wednesday, February 7, 2007

Identify and fix table fragmentation in Oracle 10g - how?

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.

Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.

SQL> create table test as select * from dba_tables; -- Create a table

Table created.

SQL> analyze table test compute statistics; -- Analyze it

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- The number of blocks used/free

Ever Used Never Used Total rows
---------- ---------- ----------
49 6 1680

SQL> delete from test where owner='SYS'; --- Im deleting almost half the number of rows.

764 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics; -- Analyze it again

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"

2 from user_tables where table_name='TEST'; -- No difference in blocks usage

Ever Used Never Used Total rows
---------- ---------- ----------
49 6 916

PL/SQL procedure successfully completed.

Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.

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.

Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to reorganize?

Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table
a) Export, drop, and import.
b) Alter table move (to another tablespace, or same tablespace).

Oracle 10g provides us a new way of reorganizing the data.

Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.

Before using this command, you should have row movement enabled.

SQL> alter table test enable row movement;

Table altered.

There are 2 ways of using this command.

1. Break in two parts: In first part rearrange rows and in second part reset the HWM.

Part 1: Rearrange (All DML's can happen during this time)

SQL> alter table test shrink space compact;

Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table sa shrink space;

Table altered.

2. Do it in one go:

SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)

Table altered.

Few advantages over the conventional methods

1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.

2. Its an online operation, So you dont need downtime to do this reorg.

3. It doesnot require any extra space for the process to complete.


Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.


Kapil said...

Hey Sachin: It would be more helpful if you can provide some more info about: "enable row movement".
Like as this operation results in change of ROWIDs for rows that get moved due to compact operation, so there is need to perform complete refresh of ROWID based Mviews.

Sachin said...

Good question!

Enable rowid movement is for making sure that you can move the rows.

I feel, if you do shrink operation after a fast refresh (of rowid based mview), and make sure that there are no rows in mlog$_table, then there should not be any impact post shrink.

But if there were any rows in mlog$_table and then you perform shrink , you must complete refresh the rowid based mview.

The point is rowid/pk are used to track changes, and if there are no pending changes, we can simply do shrink.

Note: there is no impact on PK based mviews with this operation.

Konstantinos said...

in ASSM there is also the LVM. I still don't understand the meaning of it in full table scan, process will read from the start up to HWM.
Your blog is excellent and your articles up to the point and consise

Thank you,

Anonymous said...


I have a table which has a CLOB. The data older than 60 days is deleted twice in a week. This leads to fragmentation for this table. Will shrink help to reclaim space even for LOBs? How can I reduce fragmentation for this table as well reclaim the space freed by deletes.

Nitin said...

Hi Sachin,

It'll be more beneficial and value add if you can show how to not have fragmentation in first place. Something like having your extent size multiple of block size.

Raghav said...

Nice Document... Just one thing to mention here. If the table is huge and fragmentation is also large, then shrinking the table will cause quite some archivelog generation. The reason :
Oracle scans for free blocks from the start of the segment....if free block found, it will delete the row from the end and insert into the free block. Due to this DML activity archivelogs are bound to increase. This DML is also the reason why indexes do not go invalid.

Raghav Bhatia

Oracle Applications Techno Functional said...

Awesone!!! This is a very good article Sachin. Great work and thanks for sharing.

Neeraj Shrivastava.