My Oracle Support Banner

ETL Process Hangs When Using an Oracle 11g RAC Database (Doc ID 1996100.1)

Last updated on AUGUST 04, 2018

Applies to:

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


Please note: Using an or Oracle RAC database.



1. Launch the 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 %IO SQL Id SQL Module SQL 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 ID Planhash Sampled # of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text
123 0232 1 35.39 gc current request 34.39 INSERT STATEMENT 34.39 INSERT /*+ APPEND */ INTO W_AC...




To view full details, 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 a vibrant support community of peers and Oracle experts.