Startup is very slow for Oracle 18c XE - traced to metadata extract - sequences issue

Description

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!

Environment

hibernate core 5.4.12.final, oracle jdbc 19.3 (odbc8.jar), java 11, Oracle Express 18.4

Assignee

Koen Aers

Reporter

Leslie Murphy

Labels

None

Feedback Requested

None

Feedback Requested By

None

backPortable

None

Community Help Wanted

None

Suitable for new contributors

None

Pull Request

None

backportDecision

None

backportReEvaluate

None

Components

Affects versions

Priority

Minor
Configure