Slow query performance on Oracle using ALL_TABLES and ALL_TAB_COMMENTS

General Discussion
1

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:

  1. SELECT null as TABLE_CAT, a.OWNER as TABLE_SCHEM, a.TABLE_NAME as TABLE_NAME, 'TABLE' as TABLE_TYPE, c.COMMENTS as REMARKS
  2. from ALL_TABLES a join ALL_TAB_COMMENTS c on a.TABLE_NAME = c.table_name and a.owner = c.owner
  3. WHERE a.OWNER = '' AND a.TABLE_NAME not in
  4. (select mv.name from all_registered_mviews mv where mvn.owner = '')
The explain plan for this query is quite large.  If I change it to select "count(*)", it takes between 30 and 60 seconds to run and returns 1892 results.  


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?

2

I created https://liquibase.jira.com/browse/CORE-1858 to track the fix. 


If you pull off the  "AND a.TABLE_name NOT IN (SELECT MV.NAME)… " portion does it run faster?


Nathan

3

Yes it is much faster.  Omitting that portion of the query runs in about a second.


Thank you for creating the ticket as well.


Lonny