[hibernate-issues] [JIRA] (HBX-2046) Startup is very slow for Oracle 18c XE - traced to metadata extract - sequences issue

Leslie Murphy ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate Tools ( https://hibernate.atlassian.net/browse/HBX?atlOrigin=eyJpIjoiYzlhNmQyZTJh... ) / Bug ( https://hibernate.atlassian.net/browse/HBX-2046?atlOrigin=eyJpIjoiYzlhNmQ... ) HBX-2046 ( https://hibernate.atlassian.net/browse/HBX-2046?atlOrigin=eyJpIjoiYzlhNmQ... ) Startup is very slow for Oracle 18c XE - traced to metadata extract - sequences issue ( https://hibernate.atlassian.net/browse/HBX-2046?atlOrigin=eyJpIjoiYzlhNmQ... ) Issue Type: Bug Affects Versions: 5.4.12.Final Assignee: Koen Aers ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Attachments: image-2020-06-19-16-42-09-645.png, image-2020-06-19-16-42-19-274.png Components: hbm2java Created: 19/Jun/2020 15:53 PM Environment: hibernate core 5.4.12.final, oracle jdbc 19.3 (odbc8.jar), java 11, Oracle Express 18.4 Priority: Minor Reporter: Leslie Murphy ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Startup was taking between 35-40 seconds between these two log lines HHH000400: Using dialect: org.hibernate.dialect.Oracle12cDialect and HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform] Turning on trace/debug logging offered no additional granular messaging. Traced the root cause into the SequenceInformationExtractorLegacyImpl code. This code to obtain the sequences is invoked twice during startup for some reason. I did not research why - but there is probably a performance optimization to be had. The query currently used is: select * from all_sequences; For a DBA role this takes about 20 seconds to run, and returned 109 rows. It runs a lot faster for a non DBA role but still takes a few seconds. I think this query should be changed to: select * from all_sequences where sequence_owner = ' {schemaName} '; By adding that predicate in,, the query then runs near instantaneously, even for the schema with DBA role. Also I think at the debug level of logging, the start and end timestamps for obtaining the sequences should be logged. It took quite a while for me to diagnose where my 40 seconds of startup was coming from! Is it possible through external config properties (I am using Spring Boot) I can override the query to further validate? final String lookupSql = extractionContext.getJdbcEnvironment().getDialect().getQuerySequencesString(); Thanks! ( https://hibernate.atlassian.net/browse/HBX-2046#add-comment?atlOrigin=eyJ... ) Add Comment ( https://hibernate.atlassian.net/browse/HBX-2046#add-comment?atlOrigin=eyJ... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100130- sha1:5f5b10c )