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

Bowse

Have you tried generating an explain plan for your query? – Chara

I can, how this can be helpful? I do not have control on this content. My point is: is there any maintenance step we are missing? This sw runs in our CI dozens of times per day. And now we are getting this strange issue. – Bowse

It will tell you WHY the query is slow. You can't decide what mainentance steps may or may not make a difference if you don't what's causing the slowness. – Chara

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.

Rauch

This seems to have fixed the issue, I was looking for something like that. Thanks for your time and for sharing this interesting piece of information. – Bowse

In #8793390 @a_horse_with_no_name also suggests: "My experience is that Oracle's system catalogs are extremely slow. The only thing that sometimes helped for me was to run a dbms_stats.gather_schema_stats() on the SYS schema." – Bonze

It must be noted that none of the mentioned ways helped to achive persistent speedup in our case. – Bonze

@Bonze Thanks. I added a few more options that I've used to solve data dictionary problems in the past. – Rauch

Answer 2

here is another more graceful solution.. I found that forcing the rule base optimizer with a sql patch also works.. 2 patches are necessary because sometimes the jdbc driver uses :1 and :3 as bind variables and sometimes it uses :2 & :4.. The SQL must match exactly for the patch to work.

run this in your database as sysdba..

    begin 
        dbms_sqldiag_internal.i_create_patch ( 
      sql_text =>'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' ,
      hint_text => 'RULE', 
      name => 'jdbcpatch');
    end;
    /

    begin 
        dbms_sqldiag_internal.i_create_patch ( 
      sql_text =>'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 = :2  AND p.owner = :4 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' ,
      hint_text => 'RULE', 
      name => 'jdbcpatch2');
    end;
    /

Corbett

That's an interesting approach, but it may be better to use the documented plan management features, like profiles, outlines, SPM, etc. – Rauch

Answer 3

The query in the question is generated by a call to java.sql.DatabaseMetaData.getExportedKeys() which delegates to oracle.jdbc.OracleDatabaseMetaData.getExportedKeys() to enumerate foreign keys referencing given table.

As stated in @Jon's answer, Oracle sometimes uses suboptimal plan for this query that may or may not be avoided by gathering statistics.

Other alternatives if the code can be changed:

• look at Easiest way to obtain database metadata in Java? for alternative implementation
• rewrite the query and call it directly

The second option was chosen by Liquibase project that used to call DatabaseMetaData in older versions. New versions use optimized query with proper joins from CORE-1844:

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_constraints p
INNER JOIN  all_cons_columns pc ON pc.owner = p.owner    
    AND pc.constraint_name = p.constraint_name    
    AND pc.table_name = p.table_name    
INNER JOIN all_constraints f ON p.owner = f.r_owner    
    AND p.constraint_name = f.r_constraint_name
INNER JOIN all_cons_columns fc ON fc.owner = f.owner    
    AND fc.constraint_name = f.constraint_name
    AND fc.table_name = f.table_name
    AND fc.position = pc.position
WHERE p.owner = :jdbcSchemaName
    AND p.constraint_type in ('P', 'U')    
    AND f.constraint_type = 'R'    
ORDER BY fktable_schem, fktable_name, key_seq

Bonze

Answer 4

I found this way to cheat.. run this as the user you connect with jdbc before you reverse engineer a schema..

CREATE TABLE all_constraints AS
  SELECT owner,
         constraint_name,
         constraint_type,
         table_name,
         r_owner,
         r_constraint_name,
         delete_rule,
         status,
         deferrable,
         deferred,
         validated,
         generated,
         bad,
         rely,
         last_change,
         index_owner,
         index_name,
         invalid,
         view_related
  FROM   all_constraints;

CREATE TABLE all_cons_columns AS
  SELECT *
  FROM   all_cons_columns;

CREATE INDEX ac1
  ON all_constraints (owner, constraint_name, table_name);

CREATE INDEX acc1
  ON all_cons_columns (owner, constraint_name, table_name);  

Then the query in question really screams.. the downside is you have to refresh it from time to time.. maybe make it a materialized view?

Corbett

Answer 5

1. Use oracle EXPLAIN PLAN http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm (use the result to find the bottleneck and re-write or change your query to run faster).
2. Make sure you are using indexes on your tables and your indexes are updated.
3. use partitioning.
4. clean up some of the data that you don't need anymore
5. use hibernate if you can (this may not be a easy thing to do if this is a legacy application) because it optimizes your query for you and you won't need to write JDBC query.
6. Lastly take a look at oracle performance tunning document http://docs.oracle.com/cd/E11882_01/server.112/e41573/perf_overview.htm#PFGRF02503

Foldaway

This is an internal query, I do not have control on these tables. BTW we use JPA. – Bowse

@NunzioVisciano not sure what you mean about internal query and not having control over the tables. If you want better performance you need to do the tunning on tables and your query. – Foldaway

What I mean is this: JDBC and JPA uses some internal data structure (tables as well) to perform their job. This is the case for this query that is not in our code base, it starts automatically. All that implies that, based on my current knowledge, we cannot alter any schema and the query itself. I hope I am clear now. – Bowse

Are you able to run the query directly on the database? Is it slow there too? – Chara

Guys I think the situation is not clear. The tables mentioned in this query are Oracle tables, I cannot touch them at all as our sw runs on dozens of different servers. IMHO something happened in the server that needs to be fixed by using some maintenance procedure. Did anybody faced something like that? – Bowse

@a_horse_with_no_name This query only uses data dictionary tables. – Rauch

The solution is to recreate the dictionary statistics as mentioned above, this is the maintenance step I was looking for. I honestly do not understand your idea to add indexes to this stuff. Anyway thanks for your time. – Bowse