LiquiBase internal query taking 100 seconds

Hi,

I’m new here so please direct me to the right location if this question is old.

On our production environment we’re running liquibase 1.9.5 on Oracle 11g and it takes very long time, like an hour or more for each update. DBA stopped it a few times and it was executing the following 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 = :3
 AND p.owner = :1
 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

So I ran this query manually on the same DB and it takes like a 100 seconds to execute.

On my local DB ( Oracle 10g ) it takes like 4 seconds but my DB is empty with no data, while our production DB runs a lot of schemas there so that is why the amount is much bigger.

So I just wanted to know if it is a known bug or does anyone have solutions for this?

Thank you for any information in advance

Just to note that this query is generated by liquibase, not our code.

I’m not finding that particular query in our codebase, my guess is that it is being made by the oracle JDBC driver when we call it to get the database metadata. 

During a liquibase update, it should only be making that type of call if you are using preconditions, and it needs to make the calls for each precondition since the schema may have changed from one changeset to the next.

If you would work for you, you may want to try out the 2.0 RC release and see if that works better.  There are several optimizations, including not doing a full database snapshot for most preconditions, and only querying data related to the precondition in question.

Nathan

Oh thanks for reply, will try to use the never version…although we’d prefer to use the official released version.

Thank you & regards

I understand.  The official 2.0 release should be out soon.  If you find that it is still slow I can look into it more before the official release.

Nathan