Load Data Warehouse Job Fails On Table DWH_INV_INVESTMENT_CRV_FACTS Error ORA-04036: Due to PGA Memory Limit

Products

• Clarity PPM On Premise

Issue/Introduction

After upgrading to 16.4.1, the full Load Data Warehouse (DWH) job fails when populating the DWH_INV_INVESTMENT_CRV_FACTS table.

Steps To Reproduce:

1. Upgrade to 16.4.1

2. Go to Classic > Administration > General Settings > System Options > Data Warehouse Options >
a. Enable following option: "Include Sql Curve Data"
b. Click Save.
c. Run the full Load DWH to completion.

3. Go to Classic > Administration > General Settings > System Options > Data Warehouse Options >
a.Enable option: "Include Sql Curve Data"
b. Click Save.
c. Run full Load DWH job. 

Expected: Job should complete.

Actual: Job fails with BG-DWH log error:

ERROR MESSAGE: "ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_INVESTMENT_CRV_FACTS). SQLERRM: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT" 

Environment

16.4.1

Cause

The Load DWH job requires more PGA memory for large joins than currently allocated in the Oracle instance, triggering the PGA_AGGREGATE_LIMIT.

Resolution

Request the Oracle AWR report to review.
Request the DBA to increase the PGA aggregate limits.