hi - i'm posting because i'm having major performance issues using the hibernate tools to reverse engineer a very modest oracle schema.

my basic configuration info:
JDK 1.5.0_06
Eclipse 3.1.1
JBoss-IDE 1.5.0RC2
Hibernate Tools 3.1.0 beta2
Oracle JDBC thin client 10.2.0.1.0

i'm trying to reverse engineer an oracle schema with 37 tables/~400 columns. the good news is that the reverse engineering process actually does work, but the bad news is that it takes over 45 minutes to do the reverse engineering, which is performance that i find prohibitively slow.

at no point in the process do i encounter any meaningful errors/exceptions propagated up to the eclipse error log - all i see is one warning related to a missing primary key in one of my tables, but that is clearly my problem.

i have used the Hibernate Configuration File Wizard to create a config file, and i believe i have properly specified the relevant schema that i am interested in.

hibernate.cfg.xml:


the user is the schema owner and has all appropriate permissions assigned to it.

i have also created a console configuration using the Hibernate Console Configuration Wizard wihout incident according to these slightly out-of-date instructions (http://www.hibernate.org/hib_docs/tools/eclipse/index.html).

i have searched the forums and read posts (http://forum.hibernate.org/viewtopic.php?t=953527) that have attributed slow oracle reverse engineering performance to calls to the oracle JDBC implementation of Connection.getMetaData(). in my case, i find that difficult to believe for two reasons:

1. i can use other JDBC-driven reverse engineering tools (DBVisualizer, CodeFutures FireStorm) with the same host, driver, and JVM without these performance issues.
2. i have tested out this call directly in a separate application and fetching the metadata for a single table takes about 0.2 seconds, which is roughly in line with performance in those other reverse-engineering applications.

i've seen this oracle reverse engineering performance issue mentioned before in the forums, but on the order of hundreds or thousands of tables, and never to this degree. i'm stumped, mostly because the reverse engineering actually does work, eventually, so i've clearly configured something correctly. if i had to guess, i would assume that the reverse engineering process is iterating over every table in every schema in the database despite my specifying the schema it should use in the hibernate.cfg.xml file - i'm not sure how i can go about logging the verbose output from the reverse engineering layer so i haven't been able to verify that hypothesis.

any input or suggestions would be most appreciated!

I have the same problem.
My configuration is:

I've only 10 tables, about 90 columns and it took me about 10 minute s to list the tables while creating the Reverse Engineering Configuration.
Here is my hibernate.cfg.xml:

Is there any trick to boost the performance for this problem?
Thank you.

3 issues here:

1. Oracle's performance for getIndex and getExportedForeignKeys is hilariously expensive. Most likely the other tools does not read the getIndex information....maybe i should include and option for excluding it.

2. Don't set it up to read the whole database. Use default_schema and reveng.xml to limit that. (in a later version we will have explicit listing of what schemas should be attempted)

3. I know oracle is slow, but not *that* slow. If you could then please try and profile this stuff on your system to find out which method is taking the time - the unit test in hibernateext/tools/test should give you a good place to do that.

i don't agree - oracle isn't slow, but we can make it slow
oracle can be little slower for simpe query (one query, one user), but oracle performance in multiuser, oltp or warehouse environment is great

i try reverse eng. with > 20 table (with reveng.xml) in schema with much objects and it work fine - rev. eng do work with less than one minute

snpesnpe - we do not talk about oracle in multiuser concurrent scenarios.

we are talking about their performance when querying via databasemetadata in a single user setup - there it is surprisingly slow.

So Oracle is slow in this aspect, but 40 minutes is waay to much and much be caused by either:

a) scanning waay too many schemas/tables (use reveng to limit that, and future version will make this even more efficient)

b) some bottleneck is exposed when running on oracle which i can only find find if users try and profile it to figure out what it is so i can possibly work around it. (one of these is their getIndex behavior which is slow and cannot handle exceptions properly)

/max

max - thank you very much for your prompt and informative response. i will try that test and do a little profiling of my own and see if i can narrow down where the slowdown may be happening.

if the getExportedForeignKeys call(s) are disabled, will this mean that hibernate will no longer be able to reverse engineer relational information, flattening out the schema?

yes.

but getExportedKeys are fast "enough" in its self; but getIndexInfo can be tough but you will need a profiler or debug it to find out which one is causing it (or try and look at the debug output when using ant - you just need to put a log4j.properties in there)

My best guess is that your db contains a ton of tables that we don't need to scan and as such should just be skipped.

How many tables does the database have in *total*, not just in your schema ?

this particular database has roughly 2000 tables in over 100 schemas. however, i am using the "hibernate.default_schema" property to limit the scope of the reverse engineering so it won't read the whole db. or at least, it shouldn't be trying to read the whole db.

then maybe the shear size of the metadata in the database is hogging you down because of missing indexes or similar...don't know...as others have reported i don't see so much "slowness"

max, can you tell me what method is slow - i have big database schema and i can check

JDBCMetaDialect.getIndexInfo() calls getIndexInfo() which i see as very slow compared to others.

sorry for the delay - i am completely failing to get ant/log4j to log debug output at all, so i quit trying and ran some simpler, more direct performance tests on those calls.

calls to Connection.getMetaData() and Connection.getMetaData().getIndexInfo() execute very quickly for me, under a second in all cases. calls to Connection.getMetaData().getImportedKeys() take a bit longer, but no more than a couple of seconds. calls to Connection.getMetaData().getExportedKeys() take anywhere from 30 to 90 seconds per table for me. although the schema i'm using isn't exactly relationally "intensive", clearly something needs to be optimized on my database relating to whatever server function is being called by the JDBC driver.

but i have to say that i'm still left a bit puzzled as to why other reverse engineering tools such as DBVisualizer don't suffer from this slowdown while still (apparently) retaining the ability to extract all relational information. i realize this is a bit of a rhetorical question as you can't tell me what DBVisualizer is doing, but there has to be some strategy employed there that speeds up that process.

thanks, max, i appreciate your help!

DBVisualizer doesnt use the jdbc api to get them AND they probably don't get them all in one process...are you making sure to not include unneeded tables ?

If someone have the time to contribute a more optimized version of how to get exportedKeys then please submit a OracleMetaDataDialect extending JDBCMetaDataDialect that does it better....

i promise you i'm only accessing the tables in the schema i am interested in... DBVisualizer doesn't require the use of JDBC drivers, but it does support them and those are the only drivers i have configured for DBVisualizer database access - so in my case i am certainly accessing database metadata through the JDBC api unless it is doing something sneaky. i can use DBVisualizer to reverse engineer all relational information from the same schema, using JDBC, in about 30 seconds (37 tables, 45 references). it might be multithreaded, but i doubt it as it appears that metadata for each table is accessed sequentially.

the reverseengineering you are talking about is "just" the ER diagram in Dbvisualizer or ?

In any case, the way they most likely do it is to execute oracle specific queries into the oracle metadata to get it much faster than what the jdbc api can get it...(the jdbc api is for some reason hard for oracle to fullfill efficiently)