FtsRelease20TableFragmentation < LCG

%INCLUDE{"FtsMenu"}%

%TOC%
---+ FTS table fragmentation on FTS version 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.

---++ Notice

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 <OWNER>.<TABLE_NAME> enable row movement; alter table <OWNER>.<TABLE_NAME> shrink space; exec dbms_stats.gather_table_stats('<OWNER>','<TABLE_NAME>');
-----

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.




-- Main.GavinMcCance - 18 Jul 2007

This topic: LCG > FtsWlcg > FtsRelease20 > FtsServerUpgrade15to20 > FtsRelease20TableFragmentation
Topic revision: r1 - 2007-07-18 - GavinMcCance

Copyright &© 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