All entries for Friday 18 April 2008

April 18, 2008

performance impact of java.sql.TimeStamp

On big table, partitioned by time. A compsite index contains column (URL, logdate)
A Java app will fetch some data through JDBC connection.


With classes12.jar it executed quickly. With ojdbc14.jar it runs ten times slower.

I checked the JDBC driver version. The good one is 9.0.1 and all others (, are poor.

Examed the exeucte plan carefully, I discovered that though both SQLs use same index, the good one use composition index( url and logdate) properly and thus can do partition pruning. The bad one seems only use the first column (url) of the index and ignore the logdate column. So it was iterating all the partitions and generated huge amount IO reads.


The Java app is using java.sql.Timestamp. It triggered me thinking that the bad jdbc driver might pass the value of java timestamp in a way that Oracle has to do some internal transformation, thus the logdate column is omitted.
We can see the clue from the exuction plan

   5 - access("URL" LIKE :1)

Because we just need the date, not the time, so I tried use the java.sql.Date when setting the bind variable.
The change had an immediate impact and the SQL is executed properly now.

Interesting observation (To DBAs)

Good sql disappear from v$sql after less than 1 hour. The other bad still there.
When re-run it, good one re-appeared. but the last_active_time of the bad one changed as well. They have similar last_active_time.

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(‘atfwcg8anrykp’))
Display the
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(‘atfwcg8anrykp’)) display nothing.

Search this blog

Search Warwick Blogs



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