If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Slow Blob transfer

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?

It appears you know Java & little else about the OS, SQL*Net, or Oracle DB.
At the OS level you could use "snoop" to watch packets flowing between systems.
With SQL*Net could could enable ADMIN level tracing if you can RTFM & follow examples.
Within Oracle you could enable event 10046 at level 12 to obtain actions in the DB.
Google is your Friend, but only if you are willing to spend time with her.

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.

I suspect it would be the database as blobs are very prone to performance issues . You need to get your DBA to checkout the table definition of the table holding the blobs and get him to tune it if necessary. There are a number of settings on blob tables which can have make a big difference. Check out Oracle's Application Developer's Guide - Large Objects (LOBs) for lots more info.