I recently upgraded my Java Liquibase version from 3.5.3 to 3.6.3
I have a very heavy environment where there are lots of databases and tables (I am using Oracle). On this environment, I am trying to execute a huge changelog file where I create tables and indices.
Find below a small part of the changelog.
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.2.xsd">
...
...
...
<changeSet author="me" id="tableCreation78">
<preConditions onFail="MARK_RAN">
<not>
<tableExists tableName="MY_TABLE_NAME" />
</not>
</preConditions>
<comment>Creating table MY_TABLE_NAME</comment>
<createTable tableName="MY_TABLE_NAME">
<column name="M_ID" type="bigint">
<constraints nullable="false" primaryKey="true" primaryKeyName="PK_MY_TABLE_NAME_190" />
</column>
<column name="M_FORMAT" type="int" />
</createTable>
</changeSet>
...
...
...
<changeSet author="me" id="indexCreation121">
<preConditions onFail="MARK_RAN">
<tableExists tableName="MY_TABLE_NAME"/>
<not>
<indexExists tableName="MY_TABLE_NAME" columnNames="M_FEEDER_ID"/>
</not>
</preConditions>
<comment>Creating index for MY_TABLE_NAME</comment>
<createIndex tableName="MY_TABLE_NAME" indexName="MY_INDEX_NAME">
<column name="M_ID_INDEX"/>
</createIndex>
</changeSet>
...
...
...
</databaseChangeLog>
On the Liquibase 3.5.3, creating the index used to be quick. When I migrated to Liquibase 3.6.3 , I had a severe regression in performance.
What used to run in 1-2 minutes, now takes up to 20 minutes to complete.
The changelog does not define Unique Constraints. While debugging, I noticed one of the many differences between the two versions. In the 3.5.3 , the listConstraints and listColumns methods from UniqueConstraintSnapshotGenerator are not called. In the 3.6.3 version, these methods are called a lot, even though no unique constraints are defined in the changelog. I am guessing that they are here from the previously defined tables of the environment.
Some of these queries (see below) are called multiple times with the exact same parameters. I don’t know if it’s a maintenance step that was added in the 3.6.3 .
2020-08-13 17:03:52,270 INFO [main] select ucc.owner as constraint_container, ucc.constraint_name as constraint_name, ucc.column_name, f.validated as constraint_validate from all_cons_columns ucc INNER JOIN all_constraints f ON ucc.owner = f.owner AND ucc.constraint_name = f.constraint_name where ucc.constraint_name=‘UC’ and ucc.owner=‘DB’ and ucc.table_name not like ‘BIN$%’ order by ucc.position
I am not sure if this is the cause of the regression but honestly, I am out of ideas. Does anybody know if this might be the cause of this regression? Did they add new maintenance steps in Liquibase 3.6.3 that might be causing this big performance degradation?
Thank you so much!