I have a query which takes 5 minutes when run through the java app which uses hibernate. I've cut and pasted the SQL directly from hiberate trace file and run it in sqlplus/sqldeveloper and it runs instantly (0.01 seconds)(uses the index all ok and explain plan looks good.)

I don't know how to get the explain plan when it's running through the app or why it should be any different anyway as the query is identical. I'm guessing when run through hibernate it's not using the index on the second sub select.

Please can anyone help?

Am using:

hibernate-jpa-2.0-api version 1.0.0.Final
hibernate-core version 3.3.1.GA
hibernate-annotations version 3.4.0.GA
hibernate-entitymanager version 3.4.0.GA
JDBC database driver *Oracle Driver (Thin XA) for Service connections, Versions 9.0.1 and later