Show Children Hide Children

FTS table fragmentation on FTS 1.5 and FTS 2.0

Scope:

This has been noted on FTS versions 1.5 and 2.0 when running that FTS 'history' cleanup tool described in FtsAdminTools15.

Impact:

• Standard service operation is not affected (since FTS uses indices to find the blocks).
• The table takes up more space than it should (since the fragmented blocks have a large unused portion).
• Schema upgrades (in particular new index builds) take much longer than they should, since they require a full table scan which requires reading all the blocks into the DB buffer cache.

Resolution:

• Before schema upgrade, the tables can be defragmented. The actual cause of the fragmentation is being understood with Oracle support.

Notice

Please do this in collaboration with your DBA. It is likely that most of these operations will require DBA priviliges.

Please do this in collaboration with the WLCG 3D project. Any question should be sumitted to grid-service-databases@cern.ch.

How to check for fragmentation

Check if any table is highly fragmented (>60%)

select a.owner, table_name, mb_used, mb_allocated,
round(100-((mb_used*100)/mb_allocated)) PCT_FRAGMENTED from 
  (select round((num_rows * avg_row_len)/1024/1024) MB_used, table_name, owner from dba_tables) a, 
  (select round(sum(bytes)/1024/1024) MB_allocated, segment_name, 
     owner from dba_segments group by segment_name, owner) b
   where a.table_name=b.segment_name 
      and a.owner=b.owner and mb_allocated>100 and a.owner not in 'SYS'
   order by 5 desc, 4 desc;

the likely candidates for high levels of fragmentation are t_file, t_job and t_transfer.

How to defragment

Check with grid-service-databases@cern.ch first!

Notes:

1. Stop all daemons from accessing the database
2. The SHRINK procedure requires ASSM tablespaces
3. You will need to drop the timestamp function-based indices and recreate them afterwards

Basic procedure per table you want to fragment:

1. Activate row movement on this table: alter table OWNER.TABLE_NAME enable row movement;
2. Shrink it: alter table OWNER.TABLE_NAME shrink space;
3. Re-gather stats: exec dbms_stats.gather_table_stats('OWNER','TABLE_NAME');

-- GavinMcCance - 18 Jul 2007