I have a huge query (10 A4 sheets if you print it) which returns 3 rows. If you open it with SQLPlus (Oracle) the first time you run it takes ~3 minutes to run. The 2nd time, just 8 miliseconds. My DBA is tracing it and he thinks the problem is not the query itself (few data, right use of indexes and so on) but the parsing and execution plan calculation. If you do a trivial change at the query it will take 3 minutes again, so that's likely to be the reason.

How can I deal with this?

10 A4 sheets is quiet a huge query, most of the time when there is use of multiple joins on the same table the cost of the query starts increasing steeply, try breaking up the query/where clauses in to chunks, one way is by using projections on your PK column and use these ids in where condition to get the list of ids, repeat this till all your conditions are executed...

In fact there's no where clause. The joins are done with LEFT OUTER JOIN due to *ToOne eager associations. Maybe the only actual sollution will be reducing the number of EAGER relationships.

I have a quite big application (+100 tables) and this is the first time I face this problem. I think the most conflictive part is a polimorphic association. I have an A class with has a @OneToOne relationship to a AbstractB class. AbstractB inheritance tree is implemented with join classes strategy (in order to support polimorphism). I have around 6 classes (let's call them B1, B2 .. B6) which extends AbtractB, and when you do a.getB() at the code it implies 6 left outer joins (one for each concrete table) plus the joins of every eager relationship B* classes have.

It seems not being expensive tn terms of data (there're not much rows) or table joining (every join can use an index, so cost is constant) but of query complexity. Oracle takes too much time analyzing the way he'll run the query...