Liquibase is very slow on startup with spring boot

Environment

Liquibase Version: 3.8.5 Liquibase Integration & Version: spring boot Liquibase Extension(s) & Version: none Database Vendor & Version: oracle v12 Operating System Type & Version: unix rhel v7

Description

Hello Sometime Liquibase can take a lot of time to startup ~3 minutes without any changelog to apply. I don’t know why, and the problem didn’t occurred with the last version i used 3.6.3. I run Liquibase in spring boot applications.

Steps To Reproduce

I don’t know how to reproduce it. It appends randomly

Screenshots (if appropriate)

2020-08-09 06:40:16.956 [main] INFO  o.h.annotations.common.Version.<clinit> - HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2020-08-09 06:40:16.763 [main] INFO  org.hibernate.Version.logVersion - HHH000412: Hibernate Core {5.4.10.Final}
2020-08-09 06:40:16.677 [main] INFO  o.h.jpa.internal.util.LogHelper.logPersistenceUnitInformation - HHH000204: Processing PersistenceUnitInfo [name: default]
2020-08-09 06:40:16.558 [main] INFO  l.lockservice.StandardLockService.info - Successfully released change log lock
2020-08-09 06:40:16.493 [main] INFO  liquibase.executor.jvm.JdbcExecutor.info - SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2020-08-09 06:40:16.492 [main] INFO  l.c.StandardChangeLogHistoryService.info - Reading from DATABASECHANGELOG
2020-08-09 06:40:16.491 [main] INFO  liquibase.executor.jvm.JdbcExecutor.info - SELECT COUNT(*) FROM DATABASECHANGELOG
2020-08-09 06:40:16.483 [main] INFO  liquibase.executor.jvm.JdbcExecutor.info - SELECT MD5SUM FROM DATABASECHANGELOG WHERE MD5SUM IS NOT NULL AND ROWNUM=1
2020-08-09 06:35:51.224 [main] INFO  l.lockservice.StandardLockService.info - Successfully acquired change log lock
2020-08-09 06:35:51.207 [main] INFO  liquibase.executor.jvm.JdbcExecutor.info - SELECT LOCKED FROM DATABASECHANGELOGLOCK WHERE ID=1 FOR UPDATE
2020-08-09 06:35:51.205 [main] INFO  liquibase.executor.jvm.JdbcExecutor.info - SELECT COUNT(*) FROM DATABASECHANGELOGLOCK
2020-08-09 06:35:51.198 [main] INFO  liquibase.executor.jvm.JdbcExecutor.info - SELECT COUNT(*) FROM DATABASECHANGELOGLOCK
2020-08-09 06:35:50.684 [main] INFO  l.database.core.OracleDatabase.info - Could not set remarks reporting on OracleDatabase: com.zaxxer.hikari.pool.HikariProxyConnection.setRemarksReporting(boolean)

Issue Analytics

State:
Created 3 years ago
Reactions:10
Comments:30 (9 by maintainers)

Top GitHub Comments

2reactions

ggdncommented, Aug 17, 2020

After investigation with my DBAs, we found that after 3.6.3, liquibase executes this query 9 times:

SELECT  NULL AS TABLE_CAT, atc.OWNER AS TABLE_SCHEMA, atc.OWNER, 
  atc.TABLE_NAME, atc.COLUMN_NAME, NULLABLE, ac.VALIDATED as VALIDATED, 
  ac.SEARCH_CONDITION, ac.CONSTRAINT_NAME 
FROM
 ALL_TAB_COLS atc JOIN all_cons_columns acc ON atc.OWNER = acc.OWNER AND 
  atc.TABLE_NAME = acc.TABLE_NAME AND atc.COLUMN_NAME = acc.COLUMN_NAME JOIN 
  all_constraints ac ON atc.OWNER = ac.OWNER AND atc.TABLE_NAME = 
  ac.TABLE_NAME AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME  WHERE atc.OWNER=
  'MY_SCHEMA' AND atc.hidden_column='NO' AND ac.CONSTRAINT_TYPE='C'  and 
  ac.search_condition is not null  AND atc.TABLE_NAME='DATABASECHANGELOG'

When the parsing of this query is not in cache, Oracle will find 5 executions plans (18,5 seconds each plan = 1minutes 30 secondes) When the parsing is in cache, Oracle will use only one and unique execution plan. Results are immediates.

We use a standard edition without any customization

1reaction

kataggartcommented, Jul 8, 2022

@rs-dpatil I am so glad!!! There is a security fix in 4.8 so it’s good to start using at least that for that reason as well. I am going to close this issue, but please reach out if you need anything else.