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

[Leslie Murphy (JIRA)]

Leslie Murphy ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%3A5d3f069d-3bd9-4e64-bdc7-4f8887e4fc5d ) *created* an issue

Hibernate Tools ( https://hibernate.atlassian.net/browse/HBX?atlOrigin=eyJpIjoiYzlhNmQyZTJhZmEyNDliNjk0ZWJhNGFjMTVlZDAzOWIiLCJwIjoiaiJ9 ) / Bug ( https://hibernate.atlassian.net/browse/HBX-2046?atlOrigin=eyJpIjoiYzlhNmQyZTJhZmEyNDliNjk0ZWJhNGFjMTVlZDAzOWIiLCJwIjoiaiJ9 ) HBX-2046 ( https://hibernate.atlassian.net/browse/HBX-2046?atlOrigin=eyJpIjoiYzlhNmQyZTJhZmEyNDliNjk0ZWJhNGFjMTVlZDAzOWIiLCJwIjoiaiJ9 ) Startup is very slow for Oracle 18c XE - traced to metadata extract - sequences issue ( https://hibernate.atlassian.net/browse/HBX-2046?atlOrigin=eyJpIjoiYzlhNmQyZTJhZmEyNDliNjk0ZWJhNGFjMTVlZDAzOWIiLCJwIjoiaiJ9 )

Issue Type: Bug Affects Versions: 5.4.12.Final Assignee: Koen Aers ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%3A926ec7e6-0515-4337-a65b-32fd943f79ca ) 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%3A5d3f069d-3bd9-4e64-bdc7-4f8887e4fc5d )

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=eyJpIjoiYzlhNmQyZTJhZmEyNDliNjk0ZWJhNGFjMTVlZDAzOWIiLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/HBX-2046#add-comment?atlOrigin=eyJpIjoiYzlhNmQyZTJhZmEyNDliNjk0ZWJhNGFjMTVlZDAzOWIiLCJwIjoiaiJ9 )

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.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100130- sha1:5f5b10c )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200619/c5b8938d/attachment.html