Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input.

In case the user supplies very unselective arguments, the query takes a lot of time to complete. In case the query would return more than 1000 rows, the user should be supplied with an error message.

The query does not have an order by clause, but just joins three (large) tables.

The idea now was to use the query hint FIRST_ROWS(1000) to speed up. Tests in SQL developer showed the cost metric is considerably reduced (factor 10).

The query is executed through the Entitymanager using a native query.

However, the behavior within the application is as follows: duration of OraclePreparedStatement.executeQuery is reduced by 60%. However, now a lot more of time is spent in EJBQueryImpl.getResultList which before took less than 100ms creating a high performance degradation compared with leaving out the query hint.

Code is essentially:

Query query = em.createNativeQuery(sqlQuery); // sqlQuery has query hint "FIRST_ROWS(" + (maxResults + 1) + ")"

query.setMaxResults(maxResults + 1);

List<Object[]> resultList = query.getResultList();

if (maxResults != 0 && resultList.size() > maxResults) {
    resultSetTruncated = true;

Query is:

LEFT OUTER JOIN T2 on T1.pk1 = T2.pk1
        AND T1.pk2 = T2.pk2 
        AND T1.colx = T2.pk3
        ON T2.pk1 = T3.pk1
        AND T2.coly = T3.pk1
    T1.pk1 IN ('081111') 
            T1.PK1 = T4.PK1
            AND T2.PL2 = T4.PK2 
            AND T4.KIND = 'N' 
            AND T4.NORMALIZED LIKE 'DA%'

Table T3 is very large and the specified conditions in the case of interest are not very selective.

share|improve this question

migrated from stackoverflow.com Jan 13 '12 at 10:55

This question came from our site for professional and enthusiast programmers.

closed as off topic by jcolebrand Jan 15 '12 at 5:28

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

Trying to answer your questions: Oracle 11g Enterprise Edition Release maxResults is configurable but can be assumed to be constant over months. –  stracktracer Jan 13 '12 at 9:47
The main question I ask myself, is why the duration for getResultList() is so much slower, why the executeQuery() itself is faster just like I expected it. –  stracktracer Jan 13 '12 at 9:56
This question has been migrated into the database forum. I did not see a way to undo this, as the root cause of the problem seems to be JDBC or Eclipselink after I was able to investigate deeper. I reformulated my question in stackoverflow: stackoverflow.com/questions/8861332/… –  stracktracer Jan 14 '12 at 9:32
Doing what you did was the right thing, and it's a "stack exchange site" and not quite a forum ;-) altho the difference is pedantic at best, and most everyone here knows what you mean ;-) Since it's really a JDBC issue, I'm going to close this one. –  jcolebrand Jan 15 '12 at 5:28

3 Answers 3

Query optimization isn't an exact science. Oracle cannot always find the best execution plan and it certainly can't do any magic. So here are some points that might or might not help:

  • With the FIRST_ROWS hint, Oracle favors execution plans with low cost for the first few rows over execution plans with minimal cost for retrieving the whole result set. It can also mean that retrieving the whole set takes longer with the hint than without. So what you are observing might be the intended behavior.

  • I don't how whether maxResults is a constant. If it's not, then you create a lot of queries that are different from Oracle's view even though you just slightly vary the hint. As a consequence, Oracle will spend a lot of time parsing the query and finding an execution plan. The cached execution plans won't help because they are for a slightly differnt query. So you better use a fixed value for your hint, like FIRST_ROWS(200). That should be good enough.

  • Oracle execution plan is derived from cost estimations. The cost estimations are based on statistical data about your tables. Please check whether your table statistics are up-to-date. Otherwise Oracle is likely to construct bad execution plans.

Please provide more information about your query so that we can give you more specific help. What WHEREconditions does it contain? Does it contain a clause like WHERE ROWNUM <= 1000? How many numbers of rows do you normally expect?

share|improve this answer
Added the query in the question. In optimal case, I expect less than 20 results, but if the user's search criteria are not good, there can be > 50 000 results and here it's important to know that there are more than 1000 results as fast as possible. –  stracktracer Jan 13 '12 at 9:51
would it help to add an outer select with a WHERE ROWNUM <= 1000? –  stracktracer Jan 13 '12 at 12:48
+1, The answer is in the first bullet point. –  Leigh Riffel Jan 13 '12 at 14:16
I dont' think the 1st bullet point is the answer, as query.getResultList() actually returns the number of rows I specify in MAX_ROWS. –  stracktracer Jan 14 '12 at 8:47

This question appears to be misfiled. All the information available to us suggests that this is an Eclipse/Hibernate issue and not an Oracle one. You've supplied information that says the hint works when run from SQL and that the time is spend in a hibernate function that isn't executequery. I'd suggest this get migrated (back?) to an appropriate java development forum.

share|improve this answer
I did not move it to the database forum. And I don't know how to migrate back. –  stracktracer Jan 13 '12 at 14:15

Which version of Oracle Database do you use? You essentially want a different plan based on the contents of the arguments. Oracle tried to make that possible in 9i, 10g and finally got this working in 11g.

Use SQL Plan stability and Adaptive Cursor Sharing.

Adaptive cursor sharing will make the database search for a better plan when it detects that a certain argument causes a bad performance, so the next time it will perform better. SQL Plan Stability makes sure the database ends up with a collection of good/accepted plans that give a satisfactory performance.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.