Russell Waliszewski

Maintaining the database for optimal performance

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

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

 

For optimal performance of our database it will require periodic maintenance of database tables and indexes.  RSA not only supports this, but encourages this as it will help maintain acceptable performance levels. 

 

The tables and indexes that need to be maintained is dependent on:

  1. What features of the application are used,
  2. The amount of information maintained,
  3. How often data changes,
  4. How much data changes. 

 

Maintaining of the these tables can be accomplished in three primary ways.


#1: Manual

In this method the Customer, typically DBA staff, will identify tables or indexes that would benefit from maintenance. 

In this scenario the DBA would then specific run commands like, but not limited to:

ALTER TABLE <TABLE NAME> SHRINK SPACE CASCADE;

This will "defragment" the table and potentially free up space used by the table to the tablespace it resides in.

 

ALTER INDEX <INDEX NAME> REBUILD;

This will rebuild the index removing any empty leaf nodes that will occur when data is deleted and updated.

 

#2: Oracle Segment Advisor

The Oracle database has an internal utility called the Segment Advisor which can help reclaim unused space.  

Link here: Managing Space for Schema Objects 

 

Using this utility it will help identify tables and indexes that could benefit from maintenance.  It will also be able to perform the operations as well. 

 

The operations will be the executions of the statements identified in the manual method.

 

RSA Supplied package

The RSA Supplied package is just an interface to the Oracle Segment Advisor as we want to leverage Oracle's expertise as the best way to identify improvements in the database for optimal performance. 

 

Documentation on how to utilize this package can be found in the:

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

 

Regardless of the type of deployment (Appliance, RSA Supplied Database, Remote Database) you have all 3 options are available. 

 

Which one you use should depend on your internal database expertise and comfort level.  Also with any database maintenance it is always best to have scheduled downtime.  This would involve shutting down the RSA Identity Governance and Lifecycle system to avoid any resource contention problems.

Outcomes