ResultSet getObject PERFORMANCE PROBLEM. HELP ME !!!!!
Hi to All,
I'm using Oracle 9.2.0.4.0 and JVM 1.4....
We have a strange performance behavior using ResultSet getObject method (with column name or position, it's the same) for retrieve a "NUMBER" fields from Oracle table.
More in details:
getObject(int col_pos) on VARCHAR or CHAR fields, take less than 1 ms->>> then no problem !!!
getObject(int col_pos) on NUMBER fields take at least 100 ms ->>> PROBLEM !!!
but also.... getInt(int col_pos) or getLong(int col_pos) or whatever you want on NUMBER fields take at least 100 ms !!!!!!!
I get this performance times ONLY on the first record of ResultSet.
I don't catch on !! Seems that any method that I use for reading NUMBER fields take at least 100 ms....but only on the first record.
I tried with small table (two fields ...VARCHAR and NUMBER (9)) and with bigger tables. The behavior it's the same.
Help me please, I don't understand where can be the problem.
Thanks to all,
Mauro.
PS.
ResultSet is FORWARD_ONLY.
[1086 byte] By [
ripara] at [2007-9-23]
And what version of the driver are you using?
And what type of driver - thin or oci.
And when you "measure" the performance you are...
1. Preloading everything by doing an untimed call.
2. Deriving the time using multiple calls and then dividing.
3. Using different field orders both in the table and sql call.
Hi,
I'm using thin driver, downloaded form Oracle site.
It's the suggested driver version for my Oracle DB and JVM versions. I'm sure that the version it' rigth.
I get time in this matter:
long a= System.currentTimeMillis()
Object o1= oRS.getObject(iCol);
long b= System.currentTimeMillis()
System.out.println("GETOBJECT TIME :"+(b-a))
I tried with differents columns and rows. Low performance are only with columns of type Oracle NUMBER filelds. VARCHAR and CHAR doon't have any problems.
Moreover, the problem it's only in the first line !!!!!!!!! Then, for big result set (more then 2000, 3000 rows) then problem repeat random each 500 ore 800 rows....but ALWAYS in NUMBER fields.
Really, I don't know what to do.
Ripar.
This might have something to do with garbage collection. There is a command line option to display a message when garbage collection is happening. Something along the lines of-gc verbosebut you'll have to look it up yourself.It's just a guess.
Thanks, but I don't think it's gc. I'm using 1 Gb RAM P3 Intel machine with Linux RH As 2.1 and this problem happens in the first request too. No other users are doingrequest.Anyway, I'll try lo look.Ripar.
Garbage collection in Java doesn't really depend on how huch physical memory you have. My guess would be that the JVM tries to free memory by collecting garbage before it allocates more space from the system. Because the driver (which iis java also) may have allocated memory because of a transfer from the database (to fill a buffer) the JVM decides to try collecting garbage before allocating stringbuffers for translating the NUMBER format (probably a string) into a Java Object (probably a BigDecimal).
My other thought was that the NUMBER data type was transferred asynchonously when the element was accessed. (kind of like a BLOB) I'm not all that familiar with Oracle and the way it's elements are stored, but I consider this unlikely.
> Hi,
>
> I'm using thin driver, downloaded form Oracle site.
> .
>
> It's the suggested driver version for my Oracle DB
> B and JVM versions. I'm sure that the version it'
> rigth.
>
>I get time in this matter:
>
>long a= System.currentTimeMillis()
>Object o1= oRS.getObject(iCol);
>long b= System.currentTimeMillis()
>System.out.println("GETOBJECT TIME :"+(b-a))
That doesn't do a preload but seeing that it happens at other places that probably doesn't matter.
>
> I tried with differents columns and rows. Low
> Low performance are only with columns of type Oracle
> NUMBER filelds. VARCHAR and CHAR doon't have any
> problems.
>
> Moreover, the problem it's only in the first line
> line !!!!!!!!! Then, for big result set (more then
> 2000, 3000 rows) then problem repeat random each 500
> ore 800 rows....but ALWAYS in NUMBER fields.
>
>Really, I don't know what to do.
Try constructing the select like the following (check the syntax because I didn't)...
select to_text(myNumField) from mytable
See if that changes the results.
If it doesn't then it means that it has to do with Oracle and probably the way it does cursors.
If it does then it is something in java. Perhaps the driver.
