How To Purge FA_BALANCES_REPORTS_ITF Table With Millions of Rows Affecting Performance of the reports


Journal Entry Reserve Ledger program was taking hue temp space and failing. Or we needed to add huge space in temporary tablespace.

Issue:


We identified a insert was happening in the FA_BALANCES_REPORTS_ITF table which has 40 million records already and based on the AWR it was taking 2.5 hrs to complete.

Solution:

Any report that uses FABAL uses the table FA_BALANCES_REPORTS_ITF.  The main ones are:

1)  Cost Summary Report
2)  Cost Detail Report
3)  Reserve Summary Report
4)  Reserve Detail Report
5)  Journal Entry Reserve Ledger

There is no problem with purging the table after the requests have completed, if there are no custom objects that rely on that data.  Data is inserted in the table each time the concurrent process is run only for reporting purpose.

 Run the script in TEST first (which is an exact clone of Production).

delete from FA_BALANCES_REPORTS_ITF w
where not exists (select 1
from fnd_concurrent_requests c
where c.request_id=w.request_id
and c.status_code='R');

Commit:

WorkAround:

Run gather stats for the optimizer to pick latest statistics

References:
How To Purge FA_BALANCES_REPORTS_ITF Table With Millions of Rows Affecting Performance? (Doc ID 1267397.1)




If you like please follow and comment