I’m experiencing slow Liquibase updates on Oracle with a change log containing 800+ change sets in it. On MySQL and PostgreSQL, these changes sets run in under ten minutes. However on Oracle it’s taking a little over 20 minutes on a new schema. I have a one-time per database scenario where I would run this change log on an existing schema with over 1500 tables. In that scenario, it took more than 12 hours to run.
Looking at Top Activity in OEM, this (hand-typed) query is consistently the long pole in the tent:
- SELECT null as TABLE_CAT, a.OWNER as TABLE_SCHEM, a.TABLE_NAME as TABLE_NAME, 'TABLE' as TABLE_TYPE, c.COMMENTS as REMARKS
- from ALL_TABLES a join ALL_TAB_COMMENTS c on a.TABLE_NAME = c.table_name and a.owner = c.owner
- WHERE a.OWNER = '' AND a.TABLE_NAME not in
- (select mv.name from all_registered_mviews mv where mvn.owner = '')
I’m not sure under what circumstances Liquibase performs this query but the inclusion of ALL_TAB_COMMENTS seemed unnecessary to me in most cases so I removed that from the query and it reduced the count(*) time to less than a second. I don’t know why Liquibase needs the comments other than generateChangeLog and dbDoc
Has anyone else experienced problems with a query like this?