Russell Waliszewski

Maintain database footprint

Blog Post created by Russell Waliszewski Employee on Oct 30, 2020

There are 3 things that you can do to help maintain the performance of the database.  There are SQL statements which are influenced by the amount of data in the database.  By removing data that is not actively needed by the system or the business this will help maintain consistent performance with the database.


More info can be found on this webinar, with a video discussion on these topics:

 

Part 1: Data Purging

Introduced in 7.0.1

It removes data that is no longer required for any processing by the system. 

The run will appear in the Monitoring page and will have a log of the information purged. It will also highlight if it was able to complete all the steps of the purge.

 

Action: Confirm this job is scheduled

This can be done by checking:

  • Login as a user with Admin privileges,
  • Go to Admin / System menu
  • Go to the Data Management tab
  • Confirm Purging is enabled, as per the screen shot below

 

Once the job is running confirm that it can complete all steps before hitting the time limit.  It may take multiple runs to complete all steps with older databases.

 

 

 

Part 2: Archive Purge

Introduced in 7.1.0

This is a process that is manually initiated and should be planned by the business to be able restore data in the event of an audit.  It removes objects from the system which has reached an End of Life in the timeframe specified. 

 

An archive run will appear in the Monitoring page and will have a log of the steps required for it to be completed.

 

Once the archive has been successfully completed it will no longer appear in the UI, but it may still be in the database.  The Data Purging process will then start to remove the data during its weekly run.

 

How to apply Archiving:

  • Login as a user with Admin privileges,
  • Go to Admin / System menu
  • Go to the Data Management tab
  • Press the "+Create" button as shown in the image below.

 

 

Part 3: Database Maintenance

Purging of data from the database will result in Oracle tables and indexes to become fragmented.

The Oracle database has a feature called Segment Advisor.  This can be used to schedule a job within the database that will analyze all the Tables/Indexes in the database and identify which ones would benefit most from a rebuild which will coalesce the data.

 

We have provided a simplified access to the tool and is documented in:

  • "Database Setup and Management Guide",
    • Chapter 3. "Maintaining the Database",
      • Section "Database Segment Maintenance".

 

Action: Confirm with the DBA group maintaining the database what activity is needed.

 

For additional information see the blog: Maintaining the database for optimal performance 

Outcomes