We had a query that was running slow during a performance test, but extremely fast with my laptop based threaded java test. The SQL was exactly the same, so I was driven bonkers by why it wasn’t using an index. It finally occurred to me that not only could the bind values impact the execution plan, but the bind value underlying datatypes.
I used what is below and found that was exactly the case. In his JDBC PreparedStatement(), the developer was binding a TIMESTAMP datatype type on a table column with a DATE datatype. In my laptop test, I was binding to a DATE datatype, so all was well with the world. See below…
SQL> desc rsprod_arch.request
Name Null? Type
--------------------------- ---------- -------
REQUESTID NOT NULL NUMBER(19)
CLOSEDDATE DATE
…with the following binds used during our test…
SQL> select begin_interval_time,name,datatype_string
2 from dba_hist_sqlbind dhsb, dba_hist_snapshot dhs
3 where dhsb.snap_id = dhs.snap_id
4 and sql_id = 'gjnx2b8t0nz90'
5 and name = ':3'
6 and begin_interval_time
7 between to_date('2009_07_24 15','YYYY_MM_DD HH24')
8 and to_date('2009_07_24 18','YYYY_MM_DD HH24');
BEGIN_INTERVAL_TIME NAME DATATYPE_STRING
------------------------------ ---------- ---------------
24-JUL-09 03.00.26.490 PM :3 TIMESTAMP
24-JUL-09 03.00.26.536 PM :3 TIMESTAMP
24-JUL-09 04.00.06.856 PM :3 TIMESTAMP
24-JUL-09 04.00.06.799 PM :3 TIMESTAMP
24-JUL-09 03.30.48.078 PM :3 TIMESTAMP
24-JUL-09 03.30.48.122 PM :3 TIMESTAMP
24-JUL-09 04.30.11.656 PM :3 TIMESTAMP
24-JUL-09 04.30.11.601 PM :3 TIMESTAMP
24-JUL-09 05.00.18.933 PM :3 TIMESTAMP
24-JUL-09 05.00.19.009 PM :3 TIMESTAMP
10 rows selected.
SQL>
As I mentioned, the developer was using setTimestamp() on the PreparedStatement object, and oracle was treating the two differently (as they are, as one is an object and the other is not). As such, the closedDate index was not being used in the test query, but was in mine, as I bound it to a DATE datatype.
As soon as he changed it to a DATE, the problem went away 🙂