Performance Degradation for a Query Involving ALL_OBJECTS View in 11.1.0.7 and Up (Doc ID 880057.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

When compiling Oracle Forms or Reports, the compilation takes very long. The compiler is running
this statement:

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') 


This query completes in around 26.41 seconds in 11.1.0.7 & in less than a single second in 11.1.0.6.

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