All entries for Thursday 24 February 2011
February 24, 2011
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.