Performance Issues post a large DBPrune on Advanced Authentication tables

Products

• CA Rapid App Security
• CA Advanced Authentication
• CA API Gateway

Issue/Introduction

Currently the manual DBPrune tool and DBTrimming tool does have a Index Coalescing function built into these tools. Hence customer will have to run the command manually post a large delete on the associated tables.  DBPrune is not going to be enhanced at the time of publishing this article but the automatic DB Trimming (configured via initialization file arcotcommon.ini) will apply coalescing on the trimmed tables. This is planned for Advanced Authentication Service Pack version  8.2.2 onwards. This document provides the commands that need to be manually invoked against the large tables that are pruned.


 

At version 8.1.3, severe degradation in performance could be observed post a DBPrune when a large delete is performed on Advanced Authentication tables.  This  degradation is  attributed to fragmentation on the table indexes. This document guides one to run the coalesce command post large deletes on Advanced Authentication tables, This is a best practice suggestion. At version 8.2.2 and above the DB Trimming tool will initiate the coalesce command against the known indexes to facilitate optimal performance. 

Environment

All

Resolution

1) Need to coalesce the indexes after the delete operation to help reduce index contentions. After every long delete, following indexes need to be coalesced: - 

Alter INDEX PK_ARUDS_USERAUDITLOG coalesce 
Alter INDEX IDX_ARUDSUSERAUDITLOG_TXTS coalesce 
Alter INDEX IDX_ARRFSYSAUDITLOG_DL coalesce 
Alter INDEX IDX_ARWFAUTHAUDITLOG_DC coalesce 
Alter INDEX IDX_ARWFISSUANCEAUDITLOG_DC coalesce 


 

Additional Information

Manual DBPrune tool does not have Coalescing function built into the tool. Hence one has to run the command manually post a large delete on these tables. Post CA Strong Authentication version 8.2.2, only the DB Trimming capability will automatically coalesce post pruning.