Load Data Warehouse fails with ORA-04036 on _CRV_FACTS tables as DWH_DIM_LOAD loads curve tables unbatched

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

With Include SQL Curve Data enabled, the Load Data Warehouse job fails with ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT on  %CRV_FACTS tables. Any %CRV_FACTS table can have this issue

 

STEPS TO REPRODUCE:

1. Set up a Clarity with Oracle database, set pga_aggregate_limit to 8GB
2. Administration - System Options - Data Warehouse - check Include SQL Curve Data.
3. Save and wait for the Load SQL Curves job to complete.
4. Use a dataset with a lot of resource/ assignment data 
5. Run Load Data Warehouse - Full Load
6. The DWH job issues one INSERT SELECT over the ETL link for the whole CRV table and the job fails.

Expected Results: The job to be able to batch the Curve records 

Actual Results: The whole table is loaded in one statement with one commit and nothing caps it below pga_aggregate_limit. The job fails with:

• ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_ASSIGNMENT_CRV_FACTS).
• SQLERRM : ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
• ORA-06512: at line 41

Environment

Clarity 16.3.3, 16.4.1, 16.4.2

Cause

DE210025

Resolution

In Review by Engineering 

Workaround:

Possible Options:

• Increase pga_agregate_limit on the database server
• Reduce the amount of SQL Curve data by adjusting the slices
• Ensure the Load DWH jobs do not run in parallel with other environments if shared database with other DWH instances