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 22.214.171.124.0 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