performance impact of java.sql.TimeStamp

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

Symptom:

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 (10.2.0.2, 10.2.0.3) 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.

Solution:

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)
       filter(("URL" LIKE :1 AND INTERNAL_FUNCTION("LOG_DATETIME")>=:2 AND INTERNAL_FUNCTION("LOG_DATETIME")<=:3))

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.

Hongfeng Sun : 18 Apr 2008 13:53 |  Comments (0) |  Report a problem