1

Consider the following query:

SELECT * 
  FROM (
    SELECT ARRM.*, ROWNUM 
    FROM CRS_ARRANGEMENTS ARRM 
    WHERE 
       CONCAT(ARRM.NBR_ARRANGEMENT, ARRM.TYP_PRODUCT_ARRANGEMENT) > 
       CONCAT('0000000000000000', '0000') 
    ORDER BY 
      ARRM.NBR_ARRANGEMENT, 
      ARRM.TYP_PRODUCT_ARRANGEMENT, 
      ARRM.COD_CURRENCY) 
WHERE ROWNUM < 1000;

This query runs on a table that has 10 000 000 entries. When running the query from Oracle SQL Developer or my application it takes 4 minutes to run! Unfortunately that is also the behaviour inside the application that I'm writing. Changing the value from 1000 to 10 has no impact at all, suggesting that it is doing a full table scan.

However when running from SQuirreL the query returns within a few milliseconds. How is that possible? Explain plan generated in SQuirreL gives:

Explain plan in SQuirreL

But a different explain plan is generated in Oracle SQL Developer, for the same query:

Explain plan in Oracle SQL Developer

Any idea how this difference in behaviour is possible? I can't get to understand it. I tried with JPA and raw JDBC. In the application I need to parse through all 10 000 000 records and this query is used for the paging, so waiting 4 minutes is not an option (that would take 27 days).

Note: I'm using the same Oracle jdbc driver in SQuirreL and my application so that is not the source of the problem.

Improve this question
4
  • Do a SELECT COUNT(*) .... and see if the performance gap is still there. I'm guessing that SQuirrel does not get all the data.
    – René Winkler
    Commented Mar 6, 2015 at 10:18
  • SQuirreL does get all data, that is not the difference. When only 100 000 entries are loaded in the database table there was no such difference.
    – Juru
    Commented Mar 6, 2015 at 10:20
  • Have you seen this post: community.oracle.com/thread/945873 Maybe its a problem with bind variable peeking.
    – René Winkler
    Commented Mar 6, 2015 at 10:26
  • It's not bind variable peeking, as the query performs different with the same variables.
    – Juru
    Commented Mar 6, 2015 at 11:33

1 Answer 1

Reset to default
1

Apparently the National Language Support or NLS parameters had something to do with it. Oracle SQL Developer had them set to "Dutch", default setting based on your Locale, while SQuirreL has it set to BINARY. This difference made the optimizer use different paths to solve the query. In order to use the correct NLS_SORT parameter in the jdbc session the following command needs to be used:

ALTER SESSION SET NLS_SORT=BINARY

Then the correct indexes will be used on the query.

Improve this answer
2
  • These issues are easier to find if the text-based explain plans are used. explain plan for select ... and then select * from table(dbms_xplan.display); will show a difference in the Notes section. For some reason every IDE I've ever used has tried to make the explain plans "pretty" but fails to include crucial information.
    – Jon Heller
    Commented Mar 7, 2015 at 5:59
  • I did that as well @JonHeller but the information about ns_sort is not included either. Spent 12 hours with the guys of the oracle community forums to find the issue so it was not something straightforward.
    – Juru
    Commented Mar 7, 2015 at 13:43

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