Fragmentation in Oracle(Re-org)

What is Oracle Table Fragmentation?

• If a table is only subject to inserts, there will not be any fragmentation.
• Fragmentation comes with when we update/delete data in table.
• When rows are not stored contiguously, or if rows are split onto more than one block,                           performance decreases because these rows require additional block accesses.

Many Environment call Fragmentation names like "honeycomb fragmentation" and "bubble fragmentation".  

DML operation happen fragment the table will become fragmented because
DML does not release free space from the table below the HWM.(high Water Mark).


What are the reasons to reorganization of table?

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.


To avoid performance issues with extent management, you can do the following:

• Use locally managed uniform-extent tablespaces when you know how big a segment will grow or the rate at which it will grow.
• Use extent sizes that are multiples of the database block size.
• Move tables to tablespaces with an appropriate extent size when they grow too large.
• Avoid row chaining by using Automatic Segment Space Management (ASSM).

ROW MIGRATION:-
We will migrate a row when an update to that row would cause it to not fit on the block anymore
 (with all of the other data that exists there currently).


ROW CHAINING:-
A row is too large,cannot fit into a single database block. that is row chaining.
 For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it,
 Oracle will use 3 blocks and store the row in pieces.
         

 EXAMPLE :-(easy to see chain count in dba_tables).
CREATE TABLE row_mig_chain_demo (x int PRIMARY KEY,a CHAR(1000),b CHAR(1000),c CHAR(1000),d CHAR(1000),e CHAR(1000));
INSERT INTO row_mig_chain_demo (x) VALUES (1);
INSERT INTO row_mig_chain_demo (x) VALUES (2);
INSERT INTO row_mig_chain_demo (x) VALUES (3);
COMMIT;



UPDATE row_mig_chain_demo SET a = 'z1', b = 'z2', c = 'z3' WHERE x = 3;
COMMIT;
UPDATE row_mig_chain_demo SET a = 'y1', b = 'y2', c = 'y3' WHERE x = 2;
COMMIT;
UPDATE row_mig_chain_demo SET a = 'w1', b = 'w2', c = 'w3' WHERE x = 1;
COMMIT;


UPDATE row_mig_chain_demo SET d = 'z4', e = 'z5' WHERE x = 3;
COMMIT;

Analyze option:s-

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
ANALYZE TABLE row_mig_chain_demo LIST CHAINED ROWS;

exec dbms_stats.gather_table_stats('TABLE','T1');
exec dbms_stats.gather_schema_stats('SCOTT');
exec dbms_stats.gather_database_stats();

create table CHAINED_ROWS (
  owner_name         varchar2(30),
  table_name         varchar2(30),
  cluster_name       varchar2(30),
  partition_name     varchar2(30),
  subpartition_name  varchar2(30),
  head_rowid         rowid,
  analyze_timestamp  date);

SELECT chain_cnt
  FROM user_tables
 WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 SELECT name,value FROM v$sysstat WHERE name like '%table%';

DEFRAGMENTATION:-
we can de-fragment five method(re-org)
1.CTAS
2.MOVE TABLEPSACe
3.EXP/imp
4.ONLINE-REDEFINITION
5.SHRINK TABLE and TABLESPACE

1.CTAS
create table t1 as select * from t2;
  - then drop the old table
  -and rename the new one to old name.
2.MOVE TABLEPSACE
alter table t1 move; or alter table t1 move tablespace tbs1;
   - then rebuild the index.
3.exp/imp backup method.

4.REDEFINITION
Step 1:
Grant the user to execute redefinition function.
grant execute on dbms_redefinition to user1;
grant dba to user1;

Step 2:-
WE need to create primary key of one columns of fragmented tables.
  alter table a1 add primary key(id
  Then start defragment work.
exec dbms_redefinition.can_redef_table('USER1', 'A2', DBMS_REDEFINITION.CONS_USE_PK);

Step 3:-
Create an interim table with new structure:
  create table a3 as select * from a2 where id=62387234723; -flase conditon to create a structure only.
Note:- dont insert any data newly created table.
Avoid adding constraints and indexes at this stage (for best performance).

exec dbms_redefinition.start_redef_table('USER1','A2','A3','id id',DBMS_REDEFINITION.CONS_USE_PK);

Note:- mention all columns of a table..

Step 4:-
exec dbms_redefinition.sync_interim_table('USER1','A2','A3');

Step 5:-
During this step Oracle will lock both tables in exclusive mode.
 exec dbms_redefinition.finish_redef_table('USER1','A2','A3');
enable all foreign key constraints. Remember to drop the original table afterwards.

5.SHRINK
  Alter table a1 shrink;
  alter tablespace tbs1 shrink;