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<\/B> 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<\/b>.<\/p>\n
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…<\/p>\n
\r\nSQL> desc rsprod_arch.request\r\n Name Null? Type\r\n --------------------------- ---------- ------- \r\n REQUESTID NOT NULL NUMBER(19)\r\n CLOSEDDATE DATE\r\n<\/pre>\n\u2026with the following binds used during our test\u2026<\/p>\n
\r\nSQL> select begin_interval_time,name,datatype_string\r\n 2 from dba_hist_sqlbind dhsb, dba_hist_snapshot dhs\r\n 3 where dhsb.snap_id = dhs.snap_id\r\n 4 and sql_id = 'gjnx2b8t0nz90'\r\n 5 and name = ':3'\r\n 6 and begin_interval_time\r\n 7 between to_date('2009_07_24 15','YYYY_MM_DD HH24')\r\n 8 and to_date('2009_07_24 18','YYYY_MM_DD HH24');\r\n\r\nBEGIN_INTERVAL_TIME NAME DATATYPE_STRING\r\n------------------------------ ---------- ---------------\r\n24-JUL-09 03.00.26.490 PM :3 TIMESTAMP\r\n24-JUL-09 03.00.26.536 PM :3 TIMESTAMP\r\n24-JUL-09 04.00.06.856 PM :3 TIMESTAMP\r\n24-JUL-09 04.00.06.799 PM :3 TIMESTAMP\r\n24-JUL-09 03.30.48.078 PM :3 TIMESTAMP\r\n24-JUL-09 03.30.48.122 PM :3 TIMESTAMP\r\n24-JUL-09 04.30.11.656 PM :3 TIMESTAMP\r\n24-JUL-09 04.30.11.601 PM :3 TIMESTAMP\r\n24-JUL-09 05.00.18.933 PM :3 TIMESTAMP\r\n24-JUL-09 05.00.19.009 PM :3 TIMESTAMP\r\n\r\n10 rows selected.\r\n\r\nSQL>\r\n<\/pre>\nAs 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.<\/p>\n
As soon as he changed it to a DATE, the problem went away \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
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.…<\/p>\n