Performance Issues With Parallel Queries And TEMP TABLE TRANSFORMATION (Doc ID 1633759.1)

Last updated on JANUARY 18, 2017

Applies to:

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

Symptoms

Statements running in parallel can take significant time compare to the run in serial.
Parallel run does not complete for 20 minutes whereas the same statements when run after disabling parallel
will complete in 20-30 seconds.

The Plan shows usage of Temp table transformation during load

 SELECT STATEMENT                 
  TEMP TABLE TRANSFORMATION
    . LOAD AS SELECT           
    .. VIEW           
    ... INTERSECTION
    .... SORT UNIQUE
    ....+ SORT GROUP BY
    ....+. VIEW VW_DAG_4
    ....+.. SORT GROUP BY

10046 shows waits are mainly on :

reliable message      
PX Deq: Parse Reply  
PX Deq: Execute Reply


Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  reliable message                             6725        1.00        234.55
  PX Deq: Parse Reply                           808        1.82         33.97
  PX Deq: Execute Reply                        9896        0.13         26.48
  enq: RO - fast object reuse                 12819        0.40        192.76


pstack of the parallel process shows the below kcbrbo and qertbFetch

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