Re: BLOB reading is slow

kuon_at_goyman.com wrote:
> Hello,
>
> I'm currently developing a JDBC program on Oracle and I have the
> following problem.
>
> I use the BLOB.getBinaryStream() method, it works fine, I got the
> stream and I can read it.
>
> My problem is slow performances, when I call stream.read():
>
> The first call takes 10 seconds.
> Then 32*1024 next calls are very fast.
> Then hang for 10 seconds
> Then 32ko readed fast, hang....
>
> Do you have any idea if this is Database or JDBC problem?
>
> The network is fine.
>
> I think it's a SQL Net problem, because when I SQL plus to the db on
> the local computer, the connection is immediate, but if I sqlPlus from
> remote computer, the connection hang for 5 seconds before showing me
> the sqlPlus prompt. And 32ko seems to be the net packet size, but I'm
> not sure of this.
>
> So, my questions are:
>
> - How can I use SQLPlus to select a blob (without printing it) with set
> autot on so I can benchmark and be sure it's not my JDBC? (I tried
> column binaryData noprint, select binaryData, id from akData where id =
> 1, but I still got the "SQLPlus cannot print this kind of data" error).
> - How can I tune the SQLNet configuration?
> - I use ASM, can this be a problem?
> - Should I move the BLOB to a different tablespace? If yes, how?
> - Any other settings to tune my db?
>
> I use oracle 10g on Solaris 10.
> Last JDBC (thin) driver.
>
> Best regards
>
> Kuon
>

In the oracle.sql.BLOB there is a getChunkSize() method, which I believe will return the chunk size that is used to store the BLOB on the database side. You can configure this as part of the tablespace for the BLOB, and you should also probably use the same value for the array size   on the client side for reading/writing.

You might also try testing out the "OCI" ("thick"), JDBC driver, which requires a full Oracle client install. IIRC the earlier thin drivers used DBMS_LOB to implement some of the LOB I/O; don't know if that is still the case...

You can also try tracing your session to see if there are any server side waits causing trouble.

--Peter Received on Mon Nov 14 2005 - 10:43:46 CST