Hi all

we have in our application a long running SQL statement generated by Hibernate. In the DB session we can see that the statement use an awkward execution plan.
If the SQL statement is executed with Spring SimpleJDBCTemplate then the optimal execution plan is taken.

What do Hibernate which can affect the execution plan of the statement?

What we do:
Looking after DB session parameters, but no eye-catching parameters found:
select * from V$SES_OPTIMIZER_ENV where sid = '581'

One solution could be an Oracle Hint, but Hibernate does not yet support this, right? What are the workarounds?
http://opensource.atlassian.com/project ... e/HHH-2736)

Found in this forum without an solution for me:
- FirebirdySQL viewtopic.php?f=1&t=993778&p=2403277&hilit=oracle+execution+plan#p2403277
- SQL Profiles viewtopic.php?f=1&t=982072&hilit=oracle+execution+plan
Thx for suggestions.


Best regards
Tobi

Ideas for this topic?

Thanks and Best regards
Tobi

You can use a direct JDBC query via Session#doWork.
Or you can write a subclass of OracleXxxDialect that can handle hints.

You'll introduce a very tight dependency on Oracle that way, of course.

Another approach would be to inspect the query generated by Hibernate and find out why it's triggering a bad execution plan. Maybe your tables lack an index, or haven't been analyzed lately (often an issue with 9i, 11g can be scheduled to automatically run analysis on a regular basis but this might still be off if the table is usually empty or has atypical data while the analysis runs).
Googling for "oracle" and "execution plan" will give you tons of advice on what you can do to make Oracle choose better execution plans.

Relying on hints incurs a high maintenance cost, since any hint can get ignored for various reasons, and Oracle won't be telling you why, it will just happily choose a different execution plan than what you expected. The first sign of anything amiss is performance degradation, and at that point, you'll be systematically checking all possible reasons why the hint might be getting ignored. (This is particularly nasty after a version upgrade, since the rules tend to change at that point.)

I usually give up and do separate queries that mimick the execution plan that I wanted.
Also, I tend to break up long-running queries. Oracle doesn't like these anyway, they run a higher risk of running out of rollback segments, and the only way to reduce the risk is retrying the operation - if the whole operation took an hour, this is about the last thing you actually want to do, you want to keep the work that was successfully done and set up your other software so that it can live with a state where the long-running update is half-done.

This problem with long-running updates is actually not even specific to Oracle. Long-running updates either tie up lots of table rows via locks, or lots of disk space in log segments, the first hampering 24/7 availability and the latter forcing your installation into buying larger disks.

I resorted to breaking up long-running queries into shorter ones, and committing every few minutes or so.
Also, I'm doing these mostly through JDBC (inside Session#doWork), which means I'm bypassing the session cache. That's a good thing in my book, since Hibernate tends to slow down as its session cache fills up (probably a performance bug but I can't pin it on concrete operations, I just find that rows-per-minute throughput is inversely proportional to session cache size in my software).
Since I'm doing JDBC anyway, I can easily insert hints, but I refrain from doing so for the reasons listed above, and also because I'll have to support more databases later on the project's roadmap. YMMV.