Show Children Hide Children FtsRelease20TableFragmentationCern
Main FTS Pages
FtsRelease22
Install
Configuration
Administration
Procedures
Operations
Development
Previous FTSes
FtsRelease21
FtsRelease21
All FTS Pages
FtsWikiPages
Last Page Update
GavinMcCance
2007-07-18

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

Edit | Attach | Watch | Print version | History: r3 < r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions...
Topic revision: r1 - 2007-07-18 - GavinMcCance
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LCG All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback