How to insert an "Optimizer hint" to Hibernate criteria api query

Question

i have a hibernate query that is dynamically put together using the criteria api. it generates queries that are unbearably slow, if executed as-is.

but i have noted they are about 1000% faster if I prepend /*+ FIRST_ROWS(10) */ to the query. how can i do this with the criteria api?

i tried criteria.setComment(..), but this seems to be ignored.

in the hibernate docs, 3.4.1.7. Query hints are mentioned, but it clearly states: "Note that these are not SQL query hints"

the result of the query will be paginated, so in 99% of the cases i will display the results 1-10.

Answer 1

The problem is that the hint syntax is not a comment, it just looks a bit like one. It really has to go between the SELECT and the selected columns, whereas setComment() prepends the comment before the SELECT.

Beyond that, there are no silver bullets. FIRST_ROWS is not a performance enhancing tool. It may end up taking longer to get all the rows back. Of course, in a user-facing program retrieving the first ten rows may be all we need to do.

But, which ever way you bounce it, if you want to use Oracle's hint syntax you'll need to go down the Native SQL route.

What else can you do? I don't (yet) have much experience tuning Hibernate. The one time I have under such a task the query was grabbing rows from a whole bunch of tables to instantiate an object with lots of sub-types. Each sub-type was a separate table. The query generated by Hibernate had many OUTER JOINs, which confused the heck out the optimizer. Breaking that monster into several focused queries (one per sub-type) which used only INNER JOINs produced a two hundredfold reduction in retrieval time.

This may not be of any immediate use to you. But the principle is, look at the Hibernate query and see whether it can be implemented in a different, more efficient way.

Answer 2

Hi Andreas,

You could modify the optimizer mode at the session level:

ALTER SESSION SET optimizer_mode = FIRST_ROWS;

Either just before your query and then putting it back to its default value (ALL_ROWS) or in your case since 99% of the queries would benefit from it you could modify it at the schema level (with an ON LOGON trigger for exemple) or even at the instance level (modify the init parameter).