My Oracle Support Banner

Job Using Parallelism Hangs After Upgrade To 19c (Doc ID 2861895.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


ETL jobs which load data into reporting database hang after upgrade from 12.2 to 19.11 .

The ETL job generates dynamic sql (Merge statement for each table to copy data) from the data dictionary of the source (thru a DB link). The job can never finishes and following top waits, PX Deq: Parse Reply, PX Deq: Execute Msg, cursor: pin S wait on X and library cache.

The combination of below worked fine.

-- alter session force parallel query;
-- putting /*+ parallel */ hint in select statements which generate the dynamic sql (MERGE stmts)

But with PARALLEL_DEGREE_POLICY is set to AUTO not getting parallelism.




To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.