PL/SQL Compilation Hangs When PLSQL_OPTIMIZE_LEVEL Is 2 (Doc ID 2289596.1)

Last updated on JULY 28, 2017

Applies to:

PL/SQL - Version 11.2.0.1 and later
Information in this document applies to any platform.
*** Checked for relevance on 28th Jul 2017 ***

Symptoms

1) Compilation of a PL/SQL unit is hanging with PLSQL_OPTIMIZE_LEVEL=2 (which is default value) but compiles fine when PLSQL_OPTIMIZE_LEVEL is 0,1 or 3.

2) Attaching the process ID of the Oracle shadow process that corresponds to the hanging session, to the OS debugging tools like gdb,dbx etc shows various call stacks.

#0 0x000000000ce42f42  in pdzdM07_Next ()
#1 0x000000000ce491bc  in pdzeM19_Next ()
#2 0x00000000047758dd in pdx1F23_Fold_Branches ()
#3 0x000000000477542a in pdx1F04_Basic_Cleanup ()
----
#0 0x000000000ce419ff   in pdzdM03_Insert ()
#1 0x000000000ce47c19  in pdzeM03_Insert ()
#2 0x00000000045f9cd4  in pdz8Mf7_Eliminate_Dead_Blocks ()
#3 0x0000000004775447 in pdx1F04_Basic_Cleanup ()
----
#0 0x000000000ce4bc43 in pdzgM16_Copy ()
#1 0x000000000ce31320 in pdz4M37_IL_Lines ()
#2 0x0000000004775c7c in pdx1F23_Fold_Branches ()
#3 0x000000000477542a in pdx1F04_Basic_Cleanup ()
----
#0 0x000000000ce429c7 in pdzdM04_Is_Member ()
#1 0x000000000ce46e83 in pdzeM04_Is_Member ()
#2 0x00000000045f9d8f in pdz8Mf7_Eliminate_Dead_Blocks ()
#3 0x0000000004775447 in pdx1F04_Basic_Cleanup ()
----
#0 0x000000000ce44bae in pdzdM16_Take_One ()
#1 0x000000000ce476ef in pdzeM07_Take_One ()
#2 0x00000000045fa700 in pdz8Mf8_Coalesce_Branches ()
#3 0x0000000004775438 in pdx1F04_Basic_Cleanup ()
----
#0 0x000000000ce35a12 in pdz7M10_Operand ()
#1 0x0000000004775ddd in pdx1F23_Fold_Branches ()
#2 0x000000000477542a in pdx1F04_Basic_Cleanup ()
----
#0 0x000000000ce42921  in pdzdM04_Is_Member ()
#1 0x000000000ce46e83  in pdzeM04_Is_Member ()
#2 0x00000000045f9cfa   in pdz8Mf7_Eliminate_Dead_Blocks ()
#3 0x0000000004775447 in pdx1F04_Basic_Cleanup ()
----
#0 0x00000000110b719e in pdz4M71_Step_Iteration ()
#1 0x000000000499093d in pdz8Mf7_Eliminate_Dead_Blocks ()
#2 0x0000000004a9f588 in pdx1F04_Basic_Cleanup ()
----
#0 0x00000000110c0834 in pdzdM11_Free ()
#1 0x00000000110c3ba1 in pdzeM02_Free ()
#2 0x00000000049909f1 in pdz8Mf7_Eliminate_Dead_Blocks ()
----
#0 0x00000000110c415b in pdzeM14_Take_Least ()
#1 0x0000000004990955 in pdz8Mf7_Eliminate_Dead_Blocks ()

For example, the below code will hang with PLSQL_OPTIMIZE_LEVEL=2 setting.

Changes

None. 

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