ETL process hangs when using an Oracle 11g RAC database (Doc ID 1996100.1)

Last updated on OCTOBER 11, 2016

Applies to:

Primavera P6 Reporting Database - Version R3.2 and later
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Please note: Using an 11.2.0.3 or 11.2.0.4 Oracle RAC database.

Issue:

 

DIAGNOSING STEPS TAKEN TO ROOT CAUSE THIS ISSUE

1. Launch the staretl.sh(staretl.bat) in the etl_homes\staretl directory and note the actual behavior listed above.

2. From staretlprocess.log, we can find started script for W_ACTIVITYCODEASSIGNMENT_DS.sql, but no Finished statement for this script at step 23

3. Run linux command with user (who can run staretl)

   ps -ef | grep star

   oracle    3369  3316  0 Mar10 pts/6    00:00:00 sqlplus -s -L          @/u01/app/star_1/star/scripts/source_extract/W_ACTIVITYCODEASSIGNMENT_DS.sql
 
   Please note: This also confirmed that W_ACTIVITYCODEASSIGNMENT_DS.sql is in running status at step 23.


4. From AWR report of the second node, we noticed "BEGIN extract2staging('W_ACTIVITYCODEASSIGNMENT_DS')" in the top of "SQL ordered by Elapsed Time"  

per Exec (s)%Total%CPU%IOSQL IdSQL ModuleSQL Text
2,893.89 0   39.04 0.02 0.00 123 SQL*Plus BEGIN extract2staging('W_ACTIV..


5. From ASH report of the second node, we observed the W_ACTIVITYCODEASSIGNMENT_DS.sql in top SQL with Top Events with "gc current request". See below detail:

SQL IDPlanhashSampled # of Executions% ActivityEvent% EventTop Row Source% RwSrcSQL Text
123 0232 1 35.39 gc current request 34.39 INSERT STATEMENT 34.39 INSERT /*+ APPEND */ INTO W_AC...

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