April 18, 2008

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.


- No comments Not publicly viewable


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

Trackbacks

  1. JDBC driver and poor SQL execution plan

    I was asked to create a index for a simple query. it is using FULL table scan. The index works find on test server. On live server, it refuse to use the index. I checked the table stats, it is up-to-date. I manually execute the query, it was using the index. Wh&hellip;

    Oracle/Java/Others - 10 Dec 2008, 17:34

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 give nice information for health.. by Appointment Setting 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
Not signed in
Sign in

Powered by BlogBuilder
© MMXIV