dropAll really slow Oracle

General Discussion
1

Is there a known reason why dropAll is so slow on Oracle (or in general)?

As an example it takes anything from 30 seconds to 5 minutes for the dropAll to complete on a smallish database. No more than 50 objects in total to drop. Some (3-4) tables are fairly big in terms of rows (2-6 million).

Any debugging/tracing I can turn on to help troubleshoot the issue?

2

You can pass the logLevel=DEBUG flag, but I’m not sure if there is a lot. I wouldn’t expect it to be based on table size. 


The best option woudl be to run liquibase through a profiler like is available in netbeans.


Nathan

3

It looks like the slow part is this call in AbstractDatabase.dropDatabaseObjects:

  1. DatabaseSnapshot snapshot = DatabaseSnapshotGeneratorFactory.getInstance().createSnapshot(this, schema, new HashSet());


Specifically, reading the PKs and columns takes a long time:

  1. 2012-05-05 23:58:26,927 [main] DEBUG liquibase - Reading tables for EXAMPLE @ jdbc:oracle:thin:@localhost:1521:bla ...
  2. 2012-05-05 23:58:26,972 [main] DEBUG liquibase - Reading views for EXAMPLE @ jdbc:oracle:thin:@localhost:1521:bla ...
  3. 2012-05-05 23:58:27,020 [main] DEBUG liquibase - Reading foreign keys for EXAMPLE @ jdbc:oracle:thin:@localhost:1521:bla ...
  4. 2012-05-05 23:59:17,114 [main] DEBUG liquibase - Reading unique constraints for EXAMPLE @ jdbc:oracle:thin:@localhost:1521:bla ...
  5. 2012-05-05 23:59:17,375 [main] DEBUG liquibase - Reading indexes for EXAMPLE @ jdbc:oracle:thin:@localhost:1521:bla ...
  6. 2012-05-05 23:59:17,545 [main] DEBUG liquibase - Reading sequences for EXAMPLE @ jdbc:oracle:thin:@localhost:1521:bla ...


From this sample log, reading the PKs and columns took 50 sec :frowning:


Any ideas whether this is a Liquibase issue or an Oracle driver/db issue?


Nikita

4

Purge your recycle bin.  Liquibase cannot tell the difference between a dropped object and one that has not been dropped thus it try to follow constraints for objects that have already been dropped.  This problem gradually causes the process to run slower and slower based on the number of dropped objects in the recycle bin.