Customers may experience poor performance with reconciling or looking at job history in the Reconciliation console (Atrium Core Console). 
After checking the RE archive and job tables we found ~50,000 records in form RE:Job_Runs and ~200,000 in RE:Job_Events.
Deleting these records enabled us to work again with RE console.


 

This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers.

As a work around you can create an AR Escalation that can run once a week that deletes all RE:Job_Runs and RE:Job_Events records that have a run status = success AND* are greater than a certain age

e.g  Your Run-If qualification can look similar to:

Run End Time > $ TIMESTAMP$ - 604800 (1 week in number of seconds) AND run status = success.
 

*The "AND" in the Run-If qualification above is important because Reconciliation uses the Modified Date of the CI or Relationship and compares it with the last successful Merge activity found in RE Job Events.

Run the escalation against  RE:Job_JobRuns_Events_Join form because this join form contains data that is also seen in both RE:Job_Runs, and  RE:Job_Events that can later be used with Application-Query-Delete-Entry Run Process Command line qualification.

Your two Run Process Actions will use the Application-Query-Delete-Entry Command line similar to:

• Application-Query-Delete-Entry "RE:Job_Runs" 'instanceId'= "$Job Run Instance ID$"
• Application-Query-Delete-Entry "RE:Job_Events" 'Job Run Instance ID' = "$Job Run Instance ID$"

If you delete all records then no delta can be performed by Reconciliation that uses the Merge Order of Together in one (or separate) transactions. With these records gone it will take longer to complete the next Job. 

Advanced admins only:
Clearing of these records can be done by a thick client like the legacy User Tool (midtier will only allow a limited number of records to be selected) or directly using a SQL query. 
However, it is not possible to delete from the dbo.RE_JOB_Runs or dbo.RE_JOB_Events. Tables are constructed from multiple tables and although that should be technically possible by having a view or join, in this case you will get this error when using this command in SQL:

SQL statement: delete from RE_JOB_Runs where Run_End_Time > (GETDATE()- 604800)
ERROR: View or function 'RE_JOB_Runs' is not updatable because the modification affects multiple base tables.

To overcome this you need to get the "SchemaID" for RE:Job_Runs and RE:Job_Events from the ARSCHEMA table. 

SQL statement: select name, schemaid from arschema where name like 'RE:Job_%'

The results will have a list of tables and their SchemaId as in the example below: 

Now you can delete data from these tables by pre-pending a "T" the SchemaId.

Warning: Please make sure you have full understanding of this action before performing it! It will delete RE job related data older than a month, but if there is any reason why you'd need to preserve this information then please backup the ARSCHEMA DB, or This Table, or create a report. This SQL query is destructive and will delete data!

Here is an example of such query for MSSQL based on the above results. 

select * from T954 where C301068500 <= (Select DateDiff(S, '19700101', dateadd(month, -1, GetDate())))
and
select * from T962 where C301068500 <= (Select DateDiff(S, '19700101', dateadd(month, -1, GetDate())))

 Also verify if De-Normalization is done or not if not please run it.
Later You may  tune the system from DB and AR side as per BMC performance published doc link, so that queries can run quickly.
 

https://docs.bmc.com/docs/ac2002/improving-performance-of-reconciliation-908214633.html