When rows are not stored contiguously, or if rows are split onto more than one page, performance decreases because these rows require additional page accesses.
Table fragmentation is distinct from file fragmentation.
When lots of DML operation apply on tables then tables is fragmented.
because DML is not release free space from table below HWM.
Hint: HWM is indicator for USED BLOCKS in 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 in a full table scan.
DDL statement always reset HWM.
How to find 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.
Table Size ( with fragmented)
SQL>
select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb
Actual data in tableSQL>
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 30604.2kb
Note= 72952 – 30604 = 42348 Kb is wasted space in table
The difference between two values is 60% and Pctfree 10% (default) so table is 50% extra space which is wasted because there is no data.
How to reset HWM / remove fragemenation?
For that we need to reorganize 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)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 38224kb
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 30727.37kb
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)||'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 68986.97kb
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 Sat Jul 28 16:30:44 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table BIG1 468904 rows exported
Export terminated successfully without warnings.
C:>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> truncate table big1;
Table truncated.
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:>imp scott/tiger@Orcl file=c:big1.dmp ignore=y
Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "BIG1" 468904 rows imported
Import terminated successfully without warnings.
C:>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
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”SQL> create table TABLE1 (
2 no number,
3 name varchar2(20) default 'NONE',
4 ddate date default SYSDATE);
Table created.
SQL> alter table table1 add constraint pk_no primary key(no);
Table altered.
SQL> begin
2 for x in 1..100000 loop
3 insert into table1 ( no , name, ddate)
4 values ( x , default, default);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create or replace trigger tri_table1
2 after insert on table1
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL> select count(*) from table1;
COUNT(*)
----------
100000
SQL> delete table1 where rownum <= 50000;
50000 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 2960kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 822.69kb
SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg
SQL> --First check table is condidate for redefinition.
SQL>
SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-
> 'TABLE1',-
> sys.dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
SQL> --After verifying that the table can be redefined online, you manually crea
te an empty interim table (in the same schema as the table to be redefined)
SQL>
SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;
Table created.
SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');
PL/SQL procedure successfully completed.
SQL> --This procedure keeps the interim table synchronized with the original tab
le.
SQL>
SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-
> 'TABLE1',-
> 'TABLE2');
PL/SQL procedure successfully completed.
SQL> --Create PRIMARY KEY on interim table(TABLE2)
SQL> alter table TABLE2
2 add constraint pk_no1 primary key (no);
Table altered.
SQL> create trigger tri_table2
2 after insert on table2
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL> --Disable foreign key on original table if exists before finish this proces
s.
SQL>
SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 1376kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';
TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 841.4kb
SQL> select status,constraint_name
2 from user_constraints
3 where table_name = 'TABLE1';
STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1
SQL> select status ,trigger_name
2 from user_triggers
3 where table_name = 'TABLE1';
STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_TABLE2
SQL> drop table TABLE2 PURGE;
Table dropped.
