1

In web-app hibernate criteria taking too long against oracle db. I enable the log4j.logger.org.hibernate.SQL=debug SQL and run the sql query with same bind variable in sql plus the result is instance. Enabling hibernate logging and going through the logs. What cause hibernate to take too long rub a query? Any suggestions?

Update 1:

It appears that oracle use different execution plan when i run through the SQLPlus the same query which hibernate generates. SQL Query From Hibernate:

select count(*) as y0_ from SUMMARY_VIEW this_ where this_.ser_id like :1 and this_.TYPE=:2 and this_.TIME_LOCAL>=:3 and this_.TIME_LOCAL<=:4

SQL Query Run on SQLPlus:

select count(*) as y0_ from SUMMARY_VIEW this_ where this_.ser_id like :ser_id and this_.TYPE=:type and this_.TIME_LOCAL>=:startdate and this_.TIME_LOCAL<=:enddate

Update 2: Further Investigation reveled that that startdate and endate bind variable passed as varchar2 from sqlplus but these passed as timestamp from app ( :) ). Due to this execution plans are different.

select sql_text, v.sql_id, name, value_string, datatype_string from v$sql_bind_capture vbc  join v$sql v using (hash_value) where v.sql_id in (?)

Does bind variable type affect execution plan? if so, Is there any other tools to pass date variable as a bind parameter to query?

Update 3: The performance issue due to incompatible data types. It appears that column data type(DATE) and hibernate data type(TIMESTAMP) mismatch causes implicit data type conversion. Oracle uses INTERNAL_FUNCTION to transfer date column to match the passed bind variable hibernate data type TimeStamp.

Similar issues :

Non-negligible execution plan difference with Oracle when using jdbc Timestamp or Date

Why is Oracle so slow when I pass a java.sql.Timestamp for a DATE column?

Community
  • 1
  • 1
nayakam
  • 4,149
  • 7
  • 42
  • 62
  • Maybe what is taking long is the mapping the object tree. Have you tried a native query (returning `Object[]`) to see if performance improves? – Anthony Accioly Oct 17 '13 at 02:08
  • Issue occurred in prod. Can not verify this now and fields in model class are primitive data types and String. So I believe it does not construct object tree. – nayakam Oct 17 '13 at 02:35
  • Well, sorry, can't help you without more information. Go ahead and profile your code / environment. When you have more info, and if you still need help, come back and update the question and I will surely take a look (it can be several things: you may be exhausting your connection pool / using wrong transaction scopes which may make your query wait / locking / having environment issues / etc, etc, etc, there is no way to help by guessing). – Anthony Accioly Oct 17 '13 at 02:50
  • When you same "with the same bind variables" in SQLPlus, did you actually use bind variables (e.g. :b1, :b2) in SQLPlus, or did you fill in the values into the SQL statement that you can in SQLPlus? – WW. Oct 17 '13 at 04:31
  • I did use the the bind variables as :b1 and :b2. thanks @WW. – nayakam Oct 17 '13 at 05:40
  • 1
    There are many reasons why the same query might run differently in different sessions. First you should probably find the queries and their plans, by using something like `select * from gv$sql;` and `select * from table(dbms_xplan.display_cursor(sql_id => '[sql id]', cursor_child_no => '[cursor child no]'));`. If there's only one child cursor, then the database is executing the query the same and it's a client issue. If there's a plan difference then you can look into different session settings, such as NLS parameters, or perhaps different values were used for the bind variables? – Jon Heller Oct 18 '13 at 05:23

1 Answers1

1

If you're using Hibernate, you can solve this problem with the solution that is indicated here:

http://blog.jooq.org/2014/12/29/leaky-abstractions-or-how-to-bind-oracle-date-correctly-with-hibernate/

Essentially, you'll need to use a UserType:

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Objects;

import oracle.sql.DATE;

import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

public class OracleDate implements UserType {

    @Override
    public int[] sqlTypes() {
        return new int[] { Types.TIMESTAMP };
    }

    @Override
    public Class<?> returnedClass() {
        return Timestamp.class;
    }

    @Override
    public Object nullSafeGet(
        ResultSet rs, 
        String[] names, 
        SessionImplementor session, 
        Object owner
    )
    throws SQLException {
        return rs.getTimestamp(names[0]);
    }

    @Override
    public void nullSafeSet(
        PreparedStatement st, 
        Object value, 
        int index, 
        SessionImplementor session
    )
    throws SQLException {
        // The magic is here: oracle.sql.DATE!
        st.setObject(index, new DATE(value));
    }

    // The other method implementations are omitted
}

And annotate all your entities with that type:

@Entity
@TypeDefs(
    value = @TypeDef(
        name = "oracle_date", 
        typeClass = OracleDate.class
    )
)
public class Rental {

    @Id
    @Column(name = "rental_id")
    public Long rentalId;

    @Column(name = "rental_date")
    @Type(type = "oracle_date")
    public Timestamp rentalDate;
}

A lot of repetitive boilerplate, I'm afraid, but at least you can get the execution plan up to speed again.

JDBC and other APIs

For the record, a similar article presents how to solve this problem on the JDBC layer, or with jOOQ in particular:

http://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent/

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509