Liquibase Slowness When Creating Indexes

You may need to perform maintenance on your Oracle data dictionary. Databases that use Liquibase tend to drop and create more objects than the average Oracle database, which can cause performance problems with metadata queries.

First, gather optimizer statistics for fixed objects (V$ objects) and the data dictionary (ALL_ objects). This information helps Oracle build good execution plans for metadata queries. The below statement will take a few minutes but may only need to be run once a year:

begin    dbms_stats.gather_fixed_objects_stats;    dbms_stats.gather_dictionary_stats;end;/

Another somewhat-common reason for data dictionary query problems is a large number of objects in the recycle bin. The recycle bin is great on production systems, where it lets you instantly recover from dropping the wrong table. But on a development environment, if thousands of objects are constantly dropped but not purged, those old objects can slow down some metadata queries.

--Count the number of objects in the recycle bin.select count(*) from dba_recyclebin;--Purge all of them if you don't need them. Must be run as SYS.purge dba_recyclebin;

Those are two quick and painless solutions to some data dictionary problems. If that doesn't help, you may need to tune specific SQL statements, which may require a lot of information. For example - exactly how long does it take your system to run that query against ALL_CONS_COLUMNS? (On my database, it runs in much less than a second.)

Run Liquibase and then use a query like the one below to find the slowest metadata queries:

select elapsed_time/1000000 seconds, executions, sql_id, sql_fulltext, gv$sql.*from gv$sqlorder by elapsed_time desc;