Performance Issues post a large DBPrune on Advanced Authentication tables
book
Article ID: 74847
calendar_today
Updated On:
Products
CA Rapid App SecurityCA Advanced AuthenticationCA 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.