Form Compilation Against a 11g Database Hangs or Takes a Very Long Time [ID 880660.1]

When trying to compile some forms against an 11g Database using 'frmcmp.sh' and 'frmcmp_batch.sh' compilers, it seems to hang or it takes too much time (10 min +)
Compile command is like:
frmcmp_batch.sh module=test.fmb module_type=FORM compile_all=YES userid=scott/tiger@orcl

If we compile the same source with the same command line against the 9i/10g database there is no problem.

Cause

The cause of this problem has been identified and verified in an unpublished Forms Bug: 8557019 - APPSPERF: ONE SQL USING ALL_OBJECTS HAS PERFORMANCE ISSUE UNDER 11G INSTANCE. and the underlying unpublished Database (Dictionary) Bug: 8560951 - PERFORMANCE ISSUE WHEN RUNNING A QUERY IN 11G

These Bugs explain that there is a performance issue when executing a similar 'problematic' SQL Query against the 11g database like in the following example:

SELECT COUNT (*)
FROM ALL_OBJECTS
WHERE ( OWNER = 'SYS'
AND OBJECT_NAME = 'DBMS_JAVA'
AND OBJECT_TYPE = 'PACKAGE'
AND ALL_OBJECTS.STATUS = 'VALID' )
OR ( OWNER = 'SYSTEM'
AND OBJECT_NAME = 'ORA_DE_REFLECTION'
AND OBJECT_TYPE = 'PACKAGE'
AND ALL_OBJECTS.STATUS = 'VALID' )
OR ( OWNER = 'SYSTEM'
AND OBJECT_NAME = 'oracle/opb/Reflection'
AND OBJECT_TYPE = 'JAVA CLASS'
AND ALL_OBJECTS.STATUS = 'VALID' )
OR ( OWNER = 'SYSTEM'
AND OBJECT_NAME = 'oracle/opb/SchemaClassLoader'
AND OBJECT_TYPE = 'JAVA CLASS'
AND ALL_OBJECTS.STATUS = 'VALID' ) ;

Solution

To implement the solution, you can either:

A). First, apply the database Patch 8560951 on top of your Database. 

Important Notes:

• The Patch 8560951 brings modifications in a sensible area and it is needed to use _FIX_CONTROL to enable the fix. This can be done usually either at the DB or session level, but here, as the compiler will launch its own session, it is needed to activate the fix at the DB level.
• This patch is already included in higher database versions (e.g. 10.2.0.5, 11.2.0.2). For these it's not necessary to install the patch.
  But, "_FIX_CONTROL"='8560951:ON' still needs to be set as the fix is disabled by default.

Second, execute an additional step on your database to activate this fix:

SQL> ALTER SYSTEM SET "_FIX_CONTROL"='8560951:ON''

This will enable the fix in memory. If needed to restore things as they were, you can similarly turn the fix off with:

SQL> ALTER SYSTEM SET "_FIX_CONTROL"='8560951:OFF''

More info about _FIX_CONTROL in:
NOTE 782926.1 Info About Hidden Parameter _fix_control
(You can add scope=spfile and restart the DB to have the fix remain active in the future.)


OR

B) Use the following workaround:

1 Connect to the DB with SQL*Plus as the user who compiles the Forms application

2. Use the following command to create a synonym all_objects with:

SQL> create synonym all_objects for sys.dba_objects;

Note. If it's not working, grant SELECT privelege on sys.dba_objects to the user who will be compiling the form.

3. Compile again your Form.

If you want at the end, you can drop this synonym with:

SQL> drop synonym all_objects;