Table fragmentation & how to avoid
same
January 20, 2011 by samadhan
When a lot of DML operations are applied on a table, the table will become fragmented
because DML does not release free space from the table below the HWM.
HWM is an indicator of USED BLOCKS in the database. Blocks below the high water
mark (used blocks) have at least once contained data. This data might have been deleted.
Since Oracle knows that blocks beyond the high water mark don’t have data, it only reads
blocks up to the high water mark when doing a full table scan.
DDL statement always resets the HWM.
Small example to find the table fragmentation.
SQL> select count(*) from big1;
1000000 rows selected.
SQL> delete from big1 where rownum <= 300000;
300000 rows deleted.
SQL> commit;
Commit complete.
SQL> update big1 set object_id = 0 where rownum <=350000;
342226 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'BIG1′);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables where table_name=’BIG1′;
TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1 72952 30604.2
Note = 72952 – 30604 = 42348 Kb is wasted space in table
The difference between two values is 60% and Pctfree 10% (default) – so, the table has
50% extra space which is wasted because there is no data.
How to reset HWM / remove fragemenation?
For that we need to reorganize the fragmented table.
We have four options to reorganize fragmented tables:
1. alter table … move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition
Option: 1 “alter table … move + rebuild indexes”
SQL> alter table BIG1 move;
Table altered.
SQL> select status,index_name from user_indexes
2 where table_name = ‘BIG1′;
STATUS INDEX_NAME
——– ——————————
UNUSABLE BIGIDX
SQL> alter index bigidx rebuild;
Index altered.
SQL> select status,index_name from user_indexes
2 where table_name = ‘BIG1′;
STATUS INDEX_NAME
——– ——————————
VALID BIGIDX
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'BIG1′);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables where table_name=’BIG1′;
TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1 38224 30727.37
Option: 2 “Create table as select”
SQL> create table big2 as select * from big1;
Table created.
SQL> drop table big1 purge;
Table dropped.
SQL> rename big2 to big1;
Table renamed.
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'BIG1′);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables where table_name=’BIG1′;
TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1 85536 68986.97
SQL> select status from user_indexes
2 where table_name = ‘BIG1′;
no rows selected
SQL> –Note we need to create all indexes.
Option: 3 “export / truncate / import”
SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 85536kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 42535.54kb
SQL> select status from user_indexes where table_name = ‘BIG1′;
STATUS
——–
VALID
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr
oduction
With the Partitioning, OLAP and Data Mining options
C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1
Export: Release 10.1.0.5.0 – Production on…..
Export terminated successfully without warnings.
SQL> truncate table big1;
Table truncated.
imp scott/tiger@Orcl file=c:\big1.dmp ignore=y
Import: Release 10.1.0.5.0 – Production on….
Import terminated successfully without warnings.
SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 85536kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 42535.54kb
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'BIG1′);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 51840kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 42542.27kb
SQL> select status from user_indexes where table_name = ‘BIG1′;
STATUS
——–
VALID
SQL> exec dbms_redefinition.can_redef_table(‘SCOTT’,'BIG1′,-
> dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
Option: 4 “dbms_redefinition”….Which we will disscus afterwords….
Expert are always welcome for their valuable comment or suggestion for the above
post.
Share this:
Email
Facebook
Posted in Performance Tunning | Tagged Commit (data management), Database,
Fragmentation, Oracle Database, sql, table fragmentation | 3 Comments
Like
Be the first to like this post.
3 Responses
1. on February 10, 2011 at 7:58 am | Reply Vineet Arya
Hi,
If one is working on oracle 10g and above. Then i don’t think there will be much
table space fragmentation. There is a new feature in 10g called automatic segment
space management which prevents fragmentation. However One may need to
reorganize the table because of some High number of chained (actually migrated)
rows.
Apart from the techniques that Samadhan already told. There is one more good
feature in oracle 10g called shrink space command. One can refer it on oracle
documentation.
o on February 10, 2011 at 8:35 am | Reply samadhan
Thnks Vineet….Keep replying….
o on February 17, 2011 at 3:35 pm | Reply samadhan
Hi Vineet,
As disscused please check the post for 10gR1 New Feature SEGMENT
SHRINKING.
http://samadhandba.wordpress.com/2011/02/17/how-to-adjust-the-high-
watermark-in-oracle-10g-%e2%80%93-alter-table-shrink/
Thx….
