My Oracle Support Banner

Parallel Query Hangs In Parsing for a very large partition table (Doc ID 2621300.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 to 19.5.0.0.0 [Release 12.2 to 19]
Information in this document applies to any platform.

Symptoms

The parallel query hangs while parsing on a very large partitioned table. If we run the same query without parallelism it returns in a short period of time by comparison. 

PX Trace file shows continuous messages like this ... more than 35M lines

afn:10 block:683032577 size:15 coalescable:1 pruneable:0
isinmem:0 instid:65535 numaid:65535 CU#:4294967295
afn:10 block:683032593 size:15 coalescable:1 pruneable:0
isinmem:0 instid:65535 numaid:65535 CU#:4294967295

..
..
..
And finally the workers get allocated after 1 hour or so
2019-07-03 12:14:12.666*:PX_Messaging:kxfp.c@12241:kxfpg1sg(): Got It. 1 so
far.
2019-07-03 12:14:12.666*:PX_Messaging:kxfp.c@12241:kxfpg1sg(): Got It. 2 so
far.
2019-07-03 12:14:12.666*:PX_Messaging:kxfp.c@12241:kxfpg1sg(): Got It. 3 so
far.
2019-07-03 12:14:12.666*:PX_Messaging:kxfp.c@12241:kxfpg1sg(): Got It. 4 so
far.

 

Changes

None 

Cause

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
Symptoms
Changes
Cause
Solution
References


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