| This very issue (using cross joins) caused severe performance problems on Oracle 12.1.0.2 in various cases. It was possible to 'fix' the major ones by using an SQL profile (forced execution plan) at runtime, but that's a lot of manual work on each single system. It seems that the cross join is confusing the Oracle optimizer a lot, so we had to make sure to get the inner join behaviour. I found this flag that seems to help:
org.hibernate.hql.internal.ast.SqlGenerator.REGRESSION_STYLE_CROSS_JOINS
Extensive testing still needs to be done to see if this really solves the problem. |