We have had a couple of customers hit this in 8.5 so I put together the following KB article with details of the issue and the fix for both SQL Server and Oracle environments if encountered:
For those who might not have KB access, the article text is also pasted below and the fixed stored procedures for Oracle and SQL Server are attached.
After installing or upgrading to BDSSA 8.5 , the following step of ETL may run for hours and use a very large amount of tempdb (SQL Server) or TEMP Tablespace (Oracle):
3_PATCH -> LOAD_PATCH_ANLY_JOB_RUN_RSLT
The amount of space used may be upwards of over 100GB depending on the environment so it is not a legitimate sizing issue.
LP: BMC Decision Support - Server Automation (5 Viewer, 1 Query License) 8.5.00
DR: BMC BladeLogic Decision Support for Server Automation 8.5.00
This problem has been observed in a few SQL Server environments in BDSSA 8.5 but could also occur in Oracle environments.
The problem is caused by a Cartesian join during the step of the ETL process which results in slow performance and very high tempdb (SQL Server) or TEMP Tablespace (Oracle) consumption.
The issue has been fixed with a modification to the stored procedure involved. The fixed stored procedure sql scripts for SQL Server and Oracle are attached to this article. These should only be applied to BDSSA 8.5. This issue will also be resolved in BDSSA 8.5 SP1.
1) Run oracle_load_patch_anly_job_run_rslt.sql as the BSARA_DW DB user to recreate the modified stored procedure
2) Rerun ETL
3) Verify 3_PATCH step of ETL now completes quicker without consuming an excess amount of TEMP Tablespace
On SQL Server:
1) Run sqlserver_load_patch_anly_job_run_rslt.sql on the BSARA_DW_DB Database to recreate the modified stored procedure
2) Rerun ETL
3) Verify 3_PATCH step of ETL now completes quicker without consuming an excess amount of tempdb
You can join the Customer Support Community to learn about and provide feedback on ways Customer Support can enable your success.