Performance problem with ojdbc14.jar
Hi,
We are having performance problem with ojdbc14.jar in selecting and updating (batch updates) entries in a table. The queries are taking minutes to execute.The same java code works fine with classes12.zip ans queries taking sub seconds to execute.
We have Oracle 9.2.0.5 Database Server and I have downloaded the ojdbc14.jar from Oracle site for the same. Tried executing the java code from windows 2000, Sun Solaris and Opteron machines and having the same problem.
Does any one know a solution to this problem? I also tried ojdbc14.jar meant for Oracle 10g, that did not help.
Please help.
Thanks
Yuva
[657 byte] By [
cyuva] at [2007-9-22]
Which version of Java do you use?
I beleive that JDBC drivers for the version of java are: ojdbc14.jar ... JRE/JDK 1.4classes12.jar ... JRE/JDK 1.2 , 1.3classes11.jar ... JRE/JDK 1.1
Yes, that is correct. We are using classes12.zip so far.
We are upgrading to Oracle 9.2.0.5 and wanted to shift to ojdbc14.jar which should work fine with JDK1.4.2_03. We got ojdbc14.jar meant for 9.2.0.5 from Oracle site.
Even now the code works well soon after I change class path to use classes12.zip (with JDK1.4.2_03, Oracle 9.2.0.5)... surprisingly. I am checking Oracle forums and did not get any clue so far :(.
Is there possibility running more than 2 version of java run time (1.2 and 1.4)?and ojdbc14 is trying to run under 1.2?
Are you setting the batch size? The default batch size could be different. Experiment with different values. Unset auto commit before starting the batch.
Create a simple table: table junk (n integer); Write a small program that inserts a bunch of stuff into it. Is that slow?
Modify the "junk" table to have all the columns of the table you are really inserting to. Is that slow? If it is, remove one column at a time to find out if there is a column that makes it slow.
I vaguely recall seeing an ODBC/MSSQL program that stored a floating point column using setString() or something - it worked, but it was slow; presumably the driver did a sloooow conversion.
I have checked to make sure that and I am using JDK1.4.2_03. Fine there.
I am using batch mode (batch size=1000 and with auto commit set to off). The tables I work only have NUMBER and DATE columns. And the tables in production are partitioned by date and have around 1 million rows per day. I will try doing the temp table and see if it helps finding out the cause for slowness.
You mean ojdbc14.jar doing a slow conversion compared to classes12.zip? As I mentioned same code works fine with casses12.zip.
I don't think the driver is the problem. I'm betting it's something in your code.I'm using the ojdbc14.jar now, and I don't notice any such performance hit.%
My code is doing some thing which might be working well with classes12.zip and which does not work well with ojdbc14.jar? Any general suggestions to make the code better, especially for batch updates.
But for selecting a row from the table, I am using index columns in the where cluase. In the code using PreparedStatement, setting all the reuired fields. Here is the code. We have a huge index with 14 fields!!. All the parameters are for where clause.
if(longCallPStmt == null) {
longCallPStmt = conn.prepareStatement(longCallQuery);
}
log(Level.FINE, "CdrAggLoader: Loading tcdragg entry for "
+GeneralUtility.formatDate(cdrAgg.time_hour, "MM/dd/yy HH"));
longCallPStmt.clearParameters();
longCallPStmt.setInt(1, cdrAgg.iintrunkgroupid);
longCallPStmt.setInt(2, cdrAgg.iouttrunkgroupid);
longCallPStmt.setInt(3, cdrAgg.iintrunkgroupnumber);
longCallPStmt.setInt(4, cdrAgg.iouttrunkgroupnumber);
longCallPStmt.setInt(5, cdrAgg.istateregionid);
longCallPStmt.setTimestamp(6, cdrAgg.time_hour);
longCallPStmt.setInt(7, cdrAgg.icalltreatmentcode);
longCallPStmt.setInt(8, cdrAgg.icompletioncode);
longCallPStmt.setInt(9, cdrAgg.bcallcompleted);
longCallPStmt.setInt(10, cdrAgg.itodid);
longCallPStmt.setInt(11, cdrAgg.iasktodid);
longCallPStmt.setInt(12, cdrAgg.ibidtodid);
longCallPStmt.setInt(13, cdrAgg.iaskzoneid);
longCallPStmt.setInt(14, cdrAgg.ibidzoneid);
rs = longCallPStmt.executeQuery();
if(rs.next()) {
cdr_agg = new CdrAgg(
rs.getInt(1),
rs.getInt(2),
rs.getInt(3),
rs.getInt(4),
rs.getInt(5),
rs.getTimestamp(6),
rs.getInt(7),
rs.getInt(8),
rs.getInt(9),
rs.getInt(10),
rs.getInt(11),
rs.getInt(12),
rs.getInt(13),
rs.getInt(14),
rs.getInt(15),
rs.getInt(16)
);
}//if
end_time = System.currentTimeMillis();
log(Level.INFO, "CdrAggLoader: Loaded "+((cdr_agg==null)?0:1) + " "
+ GeneralUtility.formatDate(cdrAgg.time_hour, "MM/dd/yy HH")
+" tcdragg entry in "+(end_time - start_time)+" msecs");
} finally {
GeneralUtility.closeResultSet(rs);
GeneralUtility.closeStatement(pstmt);
}
Why that code works well for classes12.zip (comes back in around 10 msec) and not for ojdbc14.jar (comes back in 6-7 minutes)?
Please advise.
Where is the code that creates the connection and prepared statement instances you're using?%
Also, please post the SQL for your long query.%
SELECT /*+ RULE */
iintrunkgroupid, iouttrunkgroupid, iintrunkgroupnumber,
iouttrunkgroupnumber, istateregionid, time_hour,
icalltreatmentcode, icompletioncode, bcallcompleted,
num_calls, combined_duration,
itodid, iasktodid, ibidtodid, iaskzoneid, ibidzoneid
FROM TCDRAGG
WHERE
iintrunkgroupid=?
and iouttrunkgroupid=?
and iintrunkgroupnumber=?
and iouttrunkgroupnumber=?
and istateregionid=?
and time_hour=?
and icalltreatmentcode=?
and icompletioncode=?
and bcallcompleted=?
and itodid=?
and iasktodid=?
and ibidtodid=?
and iaskzoneid=?
and ibidzoneid=?
So the 14 fields in your WHERE clause are all part of an index you've set up in Oracle? Do these comprise a compound primary key, or is there a separate PK, too?If you ask Oracle to EXPLAIN PLAN you don't see any table scans for this query?%
Columns in the where cluase is PK. And the explain plan looks pretty good using the index (DBAs too are working in this issue and they did not figure out any clue and likely to talk to Oracle soon). Explain plan is here
Operation Object Name RowsBytesCost Object Node In/OutPStart PStop
SELECT STATEMENT Optimizer Mode=HINT: RULE13
PARTITION RANGE SINGLEKEYKEY
TABLE ACCESS BY LOCAL INDEX ROWID TCDRAGG1673 KEYKEY
INDEX RANGE SCANPK_TCDRAGG12 KEYKEY
Copy paste the lines into notepad, they would align properly.
Looks okay to me.What about a tool like P6Spy to help figure out what's going wrong? You can run with both JDBC drivers and see what detail P6Spy can ferret out.%
Can we use p6Spy with stand alone applications?
Yes. I went to its website (you could do this too) and looked in the Documentation link where it says
"Unspecified Application Server
The following installation instructions are intended for use with application servers not listed above and applications that do not use application servers."
We figured out the problem. Looks like there is a bug in Oracles implementation of PreparedStatement.setTimeStamp() in ojdbc14.jar. Our DBAs posted the info to Oracle. Waiting on reply from Oracle.
Has any body experienced problems with setTimestamp? Please let me know any good work around/solution you used. We tried but can not use setDate and setTime for our purpose.
Thanks
Yuva
> We figured out the problem. Looks like there is a
> bug in Oracles implementation of
> PreparedStatement.setTimeStamp() in ojdbc14.jar.
> Our DBAs posted the info to Oracle. Waiting on reply
> from Oracle.
>
> Has any body experienced problems with setTimestamp?
> Please let me know any good work around/solution you
> used. We tried but can not use setDate and setTime
> for our purpose.
>
I was told, although I never found the link that the driver had a problem where writing a timestamp overwrote the following column (perhaps this was the OCI version.)
Their solution was to use a different driver (either an earlier version or thin rather than OCI.)
It is possible that using setString along with to_date (or whatever it is called in Oracle) would get around the problem as well.
Forgot to mention in my previous post, we already tried setString with to_date in the query and it works. We decided to wait until Oracle says something.Iam giving the duke dollars to you(jschell) any ways.Thanks everybody for the help.
Please ignore.Message was edited by: leftymiata
mis post, sorryMessage was edited by: leftymiata
> longCallPStmt.setTimestamp(6, cdrAgg.time_hour);
This is tickling some memories here..
Oracle's DATE type (is that what your column is?) is not completely compatible with the Timestamp type, with the result that your query gets transformed internally to something that invokes an internal function on the date column to match your bind parameter.
You can see this if you run the oracle performance analysis tools (tkprof, etc.) This, of course, prevents the index from being used.
Strangely enough, the workaround suggested was to write the query as follows:
...
WHERE ...
AND tab.datecolumn = to_date('YYYYY/MM/DD HH24:MI:SS', ?)
AND ...
and then bind the value using a formatted date
SimpleDateFormat fmt = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
String strval = fmt.format(dateval);
...
pstmt.setString(##, strval);
Weird, I know, but it worked here.
Another (cleaner, but non-portable) workaround would be to get at the underlying OraclePreparedStatement, and use OraclePreparedStatement.setDATE(), which tells the DB layer how to handle the date value.
