Query from Hibernate App does not use DB indexes

Question

I try to solve a performance issue of my application. The query hibernate generates, is of the form:

select * 
from ( 
    select this.a, this.b, this.state, this.id 
    from view_user this 
    where this.state=:1 order by this.a asc, this.b
) 
where rownum <= :2

where

• id is the primary key
• there is a combined, unique index on (a, b, id).
• view_user has ~ 2 million entries
• view_user performs some further joins to other tables

Issue

The above query performs - fast from SQLDeveloper - fast from a small Java app with hibernate - extremely slow (>100x slower) from the application with hibernate - values for the bind variables are 2 respectively 30 (rownum origins from paging) - the hibernate query is "of the form" above. There are actually about 20 columns in the view.

Current state of analysis

• query plan shows that index is used when query comes from SQlDeveloper or "small java app".
• query plan shows that full table scans are performed if query comes from hibernate app
• DB tracing shows only two differences: NLS settings (from SQLDeveloper) and slightly different formatting (whitespaces). Everything else seems to be the same...

Versions

• hibernate: 2.1.8
• jdbc driver: used ojdbc14, 5 and 6. Makes no difference
• Oracle: 10.2 and 11. Makes no difference

=> I'm glad about every hint somebody might have concerning this issue. What troubles me is the fact that the DB tracing did not show any differences... Yes, it looks like it is something about hibernate. But what? How to detect?

---

For the sake of completeness, here the hibernate query (from the log):

Select * from ( 
    select this.USER_ID as USER_ID0_, this.CLIENT_ID as CLIENT_ID0_, 
    this.USER_NAME as USER_NAME0_, this.USER_FIRST_NAME as USER_FIR5_0_, this.USER_REMARKS as 
    USER_REM6_0_, this.USER_LOGIN_ID as USER_LOG7_0_, this.USER_TITLE as USER_TITLE0_, 
    this.user_language_code as user_lan9_0_, this.USER_SEX as USER_SEX0_, 
    this.USER_BIRTH_DATE as USER_BI11_0_, this.USER_TELEPHONE as USER_TE12_0_, 
    this.USER_TELEFAX as USER_TE13_0_, this.USER_MOBILE as USER_MO14_0_, 
    this.USER_EMAIL as USER_EMAIL0_, this.USER_ADDRESSLINE1 as USER_AD16_0_, 
    this.USER_ADDRESSLINE2 as USER_AD17_0_, this.USER_POSTALCODE as USER_PO18_0_, 
    this.USER_CITY as USER_CITY0_, this.USER_COUNTRY_CD as USER_CO20_0_, 
    this.USER_COUNTRY_NAME as USER_CO21_0_, this.USER_STATE_ID as USER_ST24_0_, 
    this.USER_STATE as USER_STATE0_, this.USER_TEMP_COLL_ID as USER_TE26_0_, 
    this.USER_TEMP_COLL_NAME as USER_TE27_0_, this.UNIT_ID as UNIT_ID0_, 
    this.CLIENT_NAME as CLIENT_38_0_, this.PROFILE_EXTID as PROFILE39_0_
    from VIEW_USER this
    where this.USER_STATE_ID=:1 order by this.USER_NAME asc, this.USER_FIRST_NAME asc
) 
where rownum <= :2

Unique index is over user_name, user_first_name, user_id.

Answer 1

I've come across a similar situation so this might be relevant to you.

The JDBC driver is changing your parameters to unicode so a varchar becomes a nvarchar when it hits the database. If you are lucky SQL 2008 SP1 will catch this and convert it back. But SQL 2000 and SQL 2005 will not and the query optimizer will do a full table scan ignoring your indexes.

You might be able to fix this at the JDBC layer by adding a connection parameter sendStringParametersAsUnicode=FALSE to your connection string.