ETL Process Hangs At Step 43, Part 1 with No Error in Log (Doc ID 2223502.1)

Last updated on JANUARY 26, 2017

Applies to:

Primavera Data Warehouse - Version 16.1.0.0 and later
Information in this document applies to any platform.

Symptoms

Issue:

When running the setup.sh(setup.bat) to initiate STAR ETL process for Primavera Data Warehouse, the ETL process hangs at Step 43, Part 1 and does not complete ETL process for many hours.  When terminating the ETL process, there is no error information in the staretlprocess.log

 

DIAGNOSING STEPS TAKEN TO ROOT CAUSE THIS ISSUE

1. From AWR report section SQL ordered by Elapsed Time, noticed Top 6 sessions are from P6WebApp of SQL Module.

Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module PDB Name SQL Text
515,491.06 2 257,745.53 94.61 77.46 10.54 9pf8f5kpa8rtu P6WebApp EPPM_STG DECLARE vproj_id number; msg v...
515,491.06 2 257,745.53 94.61 77.46 10.54 9pf8f5kpa8rtu P6WebApp EPPM_STG DECLARE vproj_id number; msg v...
515,491.06 2 257,745.53 94.61 77.46 10.54 9pf8f5kpa8rtu P6WebApp EPPM_STG DECLARE vproj_id number; msg v...
515,269.37 2,162 238.33 94.57 77.35 9.94 5n1zan5dzybhx P6WebApp EPPM_STG INSERT INTO WBSSPREAD (OBJECTI...
515,269.37 2,162 238.33 94.57 77.35 9.94 5n1zan5dzybhx P6WebApp EPPM_STG INSERT INTO WBSSPREAD (OBJECTI...
515,269.37 2,162 238.33 94.57 77.35 9.94 5n1zan5dzybhx P6WebApp EPPM_STG INSERT INTO WBSSPREAD (OBJECTI...

 

2.  From ADDM Report, it stated the below analysis items

    a. Finding 1: Undersized SGA


Impact is 12.21 active sessions, 100% of total activity.
--------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "20480 M" during the analysis period.

 

    b. Finding 2: Top SQL Statements

 
       Impact is 11.58 active sessions, 94.77% of total activity.

       SQL_ID "5n1zan5dzybhx" for P6WebApp, the SQL spent 99% of its database time on CPU, I/O and Cluster waits.

 

   c. Finding 3: CPU Usage

Host CPU was a bottleneck and the instance was consuming 96% of the host CPU.
All wait times will be inflated by wait for CPU.
Host CPU consumption was 96%.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms