Slow oracle perforamce due to ALL_TABLES and ALL_TAB_COMMENTS query
Description
Environment
Oracle
Attachments
- 29 May 2014, 09:33 PM
- 16 May 2014, 06:47 AM
Activity
Nathan Voxland June 22, 2015 at 6:19 PM
I made some performance improvements in 3.4, but am keeing this issue open for the larger snapshot refactoring in 4.0
john jonathan June 28, 2014 at 2:03 PM
Hi Nathan,
I've made some adjustments to increase the performance of liquibase in our environment. As our database has a large amount of data and users, the select in ALL_* tables was taking about 6 to 8 hours. I've changed
The queries to use the USER_* tables instead of ALL_* tables. The execution time went from 8 hours to less than an hour.
Best regards
John
Nathan Voxland May 29, 2014 at 11:25 PM
Good. 3.2.0 has an updated StandardChangeLogHistoryService class that does similar logic and so should help for everyone.
Vishal Kala May 29, 2014 at 9:33 PM
Hi Nathan,
I was able to solve the performance issue by implementing a
custom StandardChangeLogHistoryService which caches
the hasDatabaseChangeLogTable and ranChangeSetList information. It seems
that the snapshot was happening for each change set that is being executed
because the check whether DatabaseChangeLogTable exists or not happens for
each change set which is unnecessary. By implementing the cache, I ensured
that the snapshot happens only once and then the information is used from
the cache for rest of the change set executions. This reduced the time
taken from 6 hours to 3-4 minutes for my approx 1700 change sets.
Thanks,
Vishal Kala
On Thu, May 29, 2014 at 4:09 PM, Nathan Voxland (JIRA) <
Nathan Voxland May 29, 2014 at 9:08 PM
The ALL_TAB_COMMENTS is there so we are able to get any comments on tables as part of the snapshot process as part of diff/diffChangeLog/generateChangeLog logic.
It sounds like the best option performance wise is to use DBA_* views instead of ALL_* views, but taht is only going to work if you have DBA security which isn't always the case. I can add a check for whether those views are accessible and pick DBA_* if available, but that won't solve it for everyone.
It is going to take more work to refactor how the snapshot logic works than available for 3.2, so it will have to be pushed to 3.3, unfortunately.
From http://forum.liquibase.org/topic/slow-query-performance-on-oracle-using-all-tables-and-all-tab-comments#49382000001069012
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 = '<username>' AND a.TABLE_NAME not in (select mv.name from all_registered_mviews mv where mvn.owner = '<username>')The explain plan for this query is quite large. If I change it to select "count:yellow_star:", 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:yellow_star: time to less than a second. I don't know why Liquibase needs the comments other than generateChangeLog and dbDoc .
I'm using Liquibase 3.1.1 and Oracle 11.2.0.3 on RHEL 6 with 16 cores, 96 GB of RAM and a RAID-5 storage tray. The Oracle database has many schemas for different configurations and developers.