JDBC automatical query turned to be very slow

Question

I am maintaining an application creating an Oracle DB via JDBC. Starting from today this query:

SELECT  NULL                                                   AS pktable_cat  ,
        p.owner                                                AS pktable_schem,
        p.table_name                                           AS pktable_name ,
        pc.column_name                                         AS pkcolumn_name,
        NULL                                                   AS fktable_cat  ,
        f.owner                                                AS fktable_schem,
        f.table_name                                           AS fktable_name ,
        fc.column_name                                         AS fkcolumn_name,
        fc.position                                            AS key_seq      ,
        NULL                                                   AS update_rule  ,
        DECODE (f.delete_rule, 'CASCADE', 0, 'SET NULL', 2, 1) AS delete_rule  ,
        f.constraint_name                                      AS fk_name      ,
        p.constraint_name                                      AS pk_name      ,
        DECODE(f.deferrable, 'DEFERRABLE',5 ,'NOT DEFERRABLE',7 , 'DEFERRED', 6 ) deferrability
FROM    all_cons_columns pc,
        all_constraints p  ,
        all_cons_columns fc,
        all_constraints f
WHERE   1                      = 1
        AND p.table_name       = :1
        AND p.owner            = :3
        AND f.constraint_type  = 'R'
        AND p.owner            = f.r_owner
        AND p.constraint_name  = f.r_constraint_name
        AND p.constraint_type  = 'P'
        AND pc.owner           = p.owner
        AND pc.constraint_name = p.constraint_name
        AND pc.table_name      = p.table_name
        AND fc.owner           = f.owner
        AND fc.constraint_name = f.constraint_name
        AND fc.table_name      = f.table_name
        AND fc.position        = pc.position
ORDER BY fktable_schem,
        fktable_name  ,
        key_seq

started becoming really slow due to some oracle internals as it seems to be the same for all my branches.

Does somebody know one possible reason and how to face this?

Regards, Nunzio

Answer 1

Data dictionary or fixed object statistics might be old, try re-gathering them:

exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
alter system flush shared_pool;

Even that does not necessarily gather statistics for all system objects. Some objects, like X$KFTBUE, must be gathered manually. Although that's a rare data dictionary problem that may not be relevant here.

If that doesn't work some next possible steps are looking at tools like SQL Tuning Advisor to create a profile, or using SQL Plan Management to force the optimizer to use a specific plan that has worked before. Tuning a data dictionary query can be very difficult since you don't have much control.