Show Children Hide Children FtsRelease20TableFragmentationCern
Main FTS Pages
Previous FTSes
All FTS Pages
Last Page Update

FTS table fragmentation on FTS version 1.5 and FTS 2.0


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


  • 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.


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

How to check for fragmentation

1- Check if any table is highly fragmented (>60%) -- Query 1: list fragmented tables using more than 100MB ----- 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;

2- I asked you to stop the FTS service and then I defragmented the necessary tables -- Query 2: defragment table after activating row movement ----- alter table . enable row movement; alter table . shrink space; exec dbms_stats.gather_table_stats('','');

2.1- For some tables there was a TIMESTAMP WITH TIMEZONE columns with index on it, which is a Function Based Index. I have dropped those indexes and recreated at the end of the shrink operation.

-- GavinMcCance - 18 Jul 2007

This topic: LCG > FtsWlcg > FtsRelease20 > FtsServerUpgrade15to20 > FtsRelease20TableFragmentation
Topic revision: r1 - 2007-07-18 - GavinMcCance
This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2022 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback