All entries for Thursday 24 February 2011

February 24, 2011

Bind variable of java.sql.Timestamp type

The following query (SQL ID : 2avwvgg1qp025 ) took long time & lots of CPU to run.


select count(*) as y0_ from eventlog this_ where this_.event_type='Login'
 and this_.providerId like :1 and this_.success=:2 and this_.userName<>:3 
and this_.eventdate>=:4 and this_.eventdate<=:5

The cost in execution plan is not too big. IO cost is 2 and CPU cost is 7,121.

I queried v$sql_bind_capture view and find the values of bind variable eventdate were null in both positions (:4 and :5).

We then find out the values passed in were one year ago and now. So that explained the cost. The query fetched one year’s data, not our supposed 3 days data.

I wrote a Java program to check why the values of bind variable eventdate were null. If you pass a java.sql.Date variable, then Oracle can record the value correctly. If you pass a java.sql.Timestamp value, however, Oracle will record it null. Then it turned out it is a bug (Note ID 444551.1) and is fixed in 11.2. Our db is 10.2.


Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXXII