Index fragmentation rebuild / reorg best practices on RT Table

book

Article ID: 208015

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

Index fragmentation jobs ( rebuild or reorg of indexes) can take a long time, especially on the RT table.  Does Broadcom have any best practices for speeding this up?

Environment

Release : Automic Workload Automation - all releases

Component : AUTOMATION ENGINE

Resolution

A DBA is the best resource to talk to about index fragmentation on the database.  Broadcom does not provide scripts to rebuild/reorg indexes as we do not have the in-depth knowledge needed for specific environments and tuning that a DBA familiar with those should have.

As index reorg/rebuild is the best way to resolve high fragmentation; there are some ways that a DBA may be able to suggest in keeping fragmentation down to begin with.  Some best practices that may keep fragmentation down:

  • Keep less reports in the database by storing reports on the file system instead (reports are what makes up the RT table).  The setting for this can be seen on the OS tab (like Windows or UNIX tab) for OS jobs and on the RA type tab (WebServices, FTP, etc...) for RA type jobs.
  • Retain a smaller number of reports in the database via the archive/reorg/unload utilities - many companies keep 30 days of records, but if it is viable, keeping 21 days could theoretically cut down on around 1/3 of the records in the RT table.  This is especially true for client 0 where the reports stored in the database will be things like agent, user, and WP/CP logs.
  • Other than that, a DBA must be consulted to determine if there is a faster way to run the reorg/rebuild of the indexes and data management within the database.