BLOB reading is slow
ku...@goyman.com
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
Mladen Gogala
> 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.
On the oracle-l, where you cross posted the problem, I asked you
about the wait in V$SESSION_WAIT and V$SESSION_EVENT. Any news or
are you cross posting just to annoy the heck out of me?
IANAL_VISTA
news:pan.2005.11.13....@sbcglobal.net:
Also posted to http://www.dbforums.com/f4/
kuon
First sorry for cross posting, this is the only place where I'll
continue this thread.
I wasn't very clear about what I did.
Network level:
- Enabling debuging on my cisco switch, looking at packets delay, I saw
absolutely no wait time from the switch on the server port on the
client and the switch port on the server.
- Network configuration: DNS, MTU, are fine.
- TCP Stack of solaris was debugged to see for eventual wait time /
timeout.
OS Level:
- Memory usage and CPU usage has been carefully monitored on both
Server (solaris) and Client (macosx).
- IO of disk has been watched, and they are nearly idle, but when
running test SQL scripts, I see higher activity, so I think there is no
bottleneck here.
DB Level:
- I sent you an email with the output of v$session_wait as you told me.
So I don't want to annoy you. But again sorry for double posting.
- I found out this: "SQL*Net more data to client", is the wait event
causing the problem.
Regards
Kuon
kuon
-bash-3.00$ cat nadesico_ora_25481.trc
/opt/Oracle/oracle/product/10.2.0/db_1/admin/nadesico/udump/nadesico_ora_25481.trc
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
ORACLE_HOME = /opt/Oracle/oracle/product/10.2.0/db_1/
System name: SunOS
Node name: hermes
Release: 5.10
Version: Generic
Machine: sun4u
Instance name: nadesico
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 25481, image: oraclenadesico@hermes
*** 2005-11-13 16:16:28.828
*** SERVICE NAME:(SYS$USERS) 2005-11-13 16:16:28.827
*** SESSION ID:(150.62) 2005-11-13 16:16:28.827
=====================
PARSING IN CURSOR #9 len=51 dep=0 uid=52 oct=3 lid=52 tim=649035954915
hv=516230619 ad='c9190d80'
SELECT large_binary_data FROM ak_data WHERE id = 45
END OF STMT
PARSE #9:c=0,e=3249,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=649035954850
BINDS #9:
EXEC #9:c=0,e=245,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=649035956128
WAIT #9: nam='SQL*Net message to client' ela= 5 driver id=1952673792
#bytes=1 p3=0 obj#=49584 tim=649035956247
WAIT #9: nam='SQL*Net message from client' ela= 1189 driver
id=1952673792 #bytes=1 p3=0 obj#=49584 tim=649035957670
WAIT #9: nam='SQL*Net message to client' ela= 2 driver id=1952673792
#bytes=1 p3=0 obj#=49584 tim=649035957968
FETCH #9:c=0,e=166,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=649035958028
... (many many many of those wait)
I'm sorry I provided a little informations on my first post, but I
though:
Yea this is a very common problem, just do that and that.
Again, I apologies for this bad start.
Regards
Kuon
Sybrand Bakker
>- I found out this: "SQL*Net more data to client", is the wait event
>causing the problem.
>
As was to be expected, as you are sending multiple packets, and you'll
have one roundtrip (read handshake) per packet.
--
Sybrand Bakker, Senior Oracle DBA
kuon
Sybrand Bakker wrote:
>
> As was to be expected, as you are sending multiple packets, and you'll
> have one roundtrip (read handshake) per packet.
>
Hi,
so what can I do?
Regards
Kuon
Sybrand Bakker
Either make sure the BLOB fits in one packet, and/or try to improve
the speed of your network.
kuon
> Either make sure the BLOB fits in one packet, and/or try to improve
> the speed of your network.
So, on a gigabit ethernet network, it's normal to takes 40 seconds for
a 10ko blob?
The read hang every 32ko. I don't think this is normal, isn't it?
Regards
Kuon
Mladen Gogala
I think that the next step should be to get the network engineer to
attach a sniffer and see what's going on here.
kuon
Sybrand Bakker
>As I wrote before, the network was checked, and no bottleneck was found.
Doesn't necessarily mean there is no problem.
At the database side, apart from increasing SDU (and making sure the
SDU is a multiple of the system's MTU) nothing can be done anymore.
You can safely stop for the silver bullet, it doesn't exist.
If sqlnet doesn't fragment your packets and the network driver doesn't
fragment your packets, it is really up to the network guys.
kuon
What I still don't understand is what the DB do every 32ko of data to
hang like that.
And while reading the first byte is so slow. (about 10 seconds for
first byte, instant for next 32ko, then hang again)
I tried a direct connection between server and client computer with no
changes.
Regards
Kuon
DA Morgan
> What I still don't understand is what the DB do every 32ko of data to
> hang like that.
When you find out please share it with us.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)
Peter Sylvester
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
StefanKapitza
- what does tnsping give ?
- have you checked dns ?
regards
s.kapitza
xho...@gmail.com
> As I wrote before, the network was checked, and no bottleneck was found.
You also checked the DB, and no bottleneck was found there, either.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
xho...@gmail.com
...
> PARSE #9:c=0,e=3249,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=649035954850
> BINDS #9:
> EXEC #9:c=0,e=245,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=649035956128
> WAIT #9: nam='SQL*Net message to client' ela= 5 driver id=1952673792
> #bytes=1 p3=0 obj#=49584 tim=649035956247
> WAIT #9: nam='SQL*Net message from client' ela= 1189 driver
> id=1952673792 #bytes=1 p3=0 obj#=49584 tim=649035957670
> WAIT #9: nam='SQL*Net message to client' ela= 2 driver id=1952673792
> #bytes=1 p3=0 obj#=49584 tim=649035957968
> FETCH #9:c=0,e=166,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=649035958028
> ... (many many many of those wait)
The above are mostly irrelevant preliminaries. You cut it off just where
things start to get interesting. Obviously you can't post the entire thing
here, but you should take a cut from the middle, where the slowness is
happening.
Earlier you reported "SQL*Net more data to client" was the problem, but
you didn't include any examples of those waits in your trace excerpt.
Is the time spread out pretty much equally among the SQL*Net waits, or are
most of them very fast (low value for e=<number>) with a few very long
ones?
kuon
goyman@fulgore:~$ tnsping nadesico
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on
14-NOV-2005 18:02:10
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/home/goyman/oracle/product/10.2.0/client_1/network/admin/sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=nadesico.goyman.com))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.6)(PORT=1521)))
OK (0 msec)
DNS are fine, I checked them. (both reverse and straight)
I also checked getBufferSize and getChunkSize in JDBC, I have adjusted
my own buffer to match them, but without any change.
Anyway, what I don't really understand is why writing is fast (about
1mb per second, 1/50 ftp performances for the same transfer, but enough
for my application), but reading has those strange hang. That's why I
think it's not a network problem.
I also just begun to write a debug application in C, with oci, I'll let
you know the results.
Sybrand Bakker
>I also checked getBufferSize and getChunkSize in JDBC, I have adjusted
>my own buffer to match them, but without any change.
As explained before, you need to set *SDU* in listener.ora on the
server and tnsnames.ora on the client.
kuon
I hope this data will speak more:
*** 2005-11-14 20:05:04.492
WAIT #0: nam='SQL*Net message from client' ela= 8006447 driver
id=1952673792 #bytes=1 p3=0 obj#=49584 tim=97037326582
WAIT #0: nam='direct path read' ela= 286 file number=4 first dba=45628
block cnt=1 obj#=49584 tim=97037327721
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1952673792
#bytes=1 p3=0 obj#=49584 tim=97037327813
WAIT #0: nam='SQL*Net more data to client' ela= 78 driver id=1952673792
#bytes=2049 p3=0 obj#=49584 tim=97037327930
WAIT #0: nam='SQL*Net more data to client' ela= 31 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037327999
WAIT #0: nam='SQL*Net more data to client' ela= 15 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328049
WAIT #0: nam='SQL*Net more data to client' ela= 15 driver id=1952673792
#bytes=2020 p3=0 obj#=49584 tim=97037328096
WAIT #0: nam='direct path read' ela= 60 file number=4 first dba=45632
block cnt=1 obj#=49584 tim=97037328217
WAIT #0: nam='SQL*Net more data to client' ela= 18 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328303
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328351
WAIT #0: nam='SQL*Net more data to client' ela= 13 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328396
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2020 p3=0 obj#=49584 tim=97037328440
WAIT #0: nam='direct path read' ela= 2 file number=4 first dba=45636
block cnt=1 obj#=49584 tim=97037328471
WAIT #0: nam='SQL*Net more data to client' ela= 15 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328542
WAIT #0: nam='SQL*Net more data to client' ela= 15 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328588
WAIT #0: nam='SQL*Net more data to client' ela= 13 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328631
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2020 p3=0 obj#=49584 tim=97037328685
WAIT #0: nam='direct path read' ela= 3 file number=4 first dba=45640
block cnt=1 obj#=49584 tim=97037328718
WAIT #0: nam='SQL*Net more data to client' ela= 15 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328789
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328835
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97037328879
WAIT #0: nam='SQL*Net more data to client' ela= 13 driver id=1952673792
#bytes=2020 p3=0 obj#=49584 tim=97037328921
WAIT #0: nam='SQL*Net message from client' ela= 8006764 driver
id=1952673792 #bytes=1 p3=0 obj#=49584 tim=97045335826
WAIT #0: nam='direct path read' ela= 279 file number=4 first dba=45581
block cnt=1 obj#=49584 tim=97045336921
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1952673792
#bytes=1 p3=0 obj#=49584 tim=97045337042
WAIT #0: nam='SQL*Net more data to client' ela= 152 driver
id=1952673792 #bytes=2049 p3=0 obj#=49584 tim=97045337234
WAIT #0: nam='SQL*Net more data to client' ela= 29 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045337303
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045337351
WAIT #0: nam='SQL*Net more data to client' ela= 13 driver id=1952673792
#bytes=2020 p3=0 obj#=49584 tim=97045337397
WAIT #0: nam='direct path read' ela= 65 file number=4 first dba=45585
block cnt=1 obj#=49584 tim=97045337495
WAIT #0: nam='SQL*Net more data to client' ela= 18 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045337581
WAIT #0: nam='SQL*Net more data to client' ela= 15 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045337629
WAIT #0: nam='SQL*Net more data to client' ela= 15 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045337675
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2020 p3=0 obj#=49584 tim=97045337732
WAIT #0: nam='direct path read' ela= 3 file number=4 first dba=45589
block cnt=1 obj#=49584 tim=97045337766
WAIT #0: nam='SQL*Net more data to client' ela= 15 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045337838
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045337885
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045337928
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2020 p3=0 obj#=49584 tim=97045337971
WAIT #0: nam='direct path read' ela= 39 file number=4 first dba=45593
block cnt=1 obj#=49584 tim=97045338038
WAIT #0: nam='SQL*Net more data to client' ela= 17 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045338117
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045338165
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2048 p3=0 obj#=49584 tim=97045338218
WAIT #0: nam='SQL*Net more data to client' ela= 14 driver id=1952673792
#bytes=2020 p3=0 obj#=49584 tim=97045338263
*** 2005-11-14 20:05:20.895
I also found something while I was in EM,
(Under the session information Tab)
Last Call Elapsed Time 6 Seconds
Each time I got data on the client, this drop down to 0 seconds.
Then it grow up to 9 seconds, and again back to 0. (At this moment I
got data on my client machine)
xho...@gmail.com
From your database's perspective, it spent less than one millisecond
sending 32kb of data to your client, and then spent 8 seconds waiting for
your client to get its act together and make its next request.
kuon
I tried with an OCI programme, I got exactly the same as JDBC.
It seems that the client do not ask for data after he receive data from
db.
So as OCI do the exact same thing as JDBC, I don't think it's JDBC. But
I don't know what it is.
Any idea are welcome.
Sybrand Bakker
>
>I hope this data will speak more:
It does. You didn't set SDU (it's still at 2048), as I recommended to
you several times.
kuon
But I read several documents, and according to them, SDU = MTU is the
best value.
I also tried a higher value,with absolutely no difference.
Regards
Kuons
Mladen Gogala
> I hope this data will speak more:
>
> *** 2005-11-14 20:05:04.492
> WAIT #0: nam='SQL*Net message from client' ela= 8006447 driver
This speaks volumes. Given that the time is expressed in microseconds,
your wait for data from client is 8 seconds. All other waits are in
tens of microseconds. So, you checked the network, it's OK, you checked
the database, its OK. That leaves us the client as the only remaining
culprit. Profile the client and see where the time is spent. Then figure
out how to rewrite the routine that causes such delays.
Alternatively, get your boss to buy you much faster PC and rewrite the
client. Faster PC is usually better for playing games. Make sure that it
has a video card with at least 128MB memory of RAM, OpenGL and
anti-aliasing implemented in hardware. I hear that NVIDIA 7800 with 256MB
is pretty decent. It makes unspeakable abominations run much faster.
Mladen Gogala
> It does. You didn't set SDU (it's still at 2048), as I recommended to
> you several times.
And he didn't buy a Powerball ticket, either. It has as much to do with
the problem as SDU. Sybrand, your advice is usually priceless, except when
it's drivel, like in this case. He is spending time in the client. His
largest wait is the following:
"WAIT #0: nam='SQL*Net message from client' ela= 8006447 driver"
His client is spending 8 seconds to send the data. What's SDU got to do
with it? Fortunately, your pleasant and charming personality makes even
this advice useful....for Monty Python lovers.
Mladen Gogala
Kuon, forget about the SDU. It's baloney, delivered in his usual charming
way. Look into the wait states, that is your best clue. You are waiting
for the data FROM THE CLIENT. The delay is on the client side. You must
find where is client spending the time. To do that, you should profile the
client. There are many Java profilers which can help you there. I am not
Java programmer, so I don't have any recommendations. If you are sending
many small packets, then large SDU will only increase delays, as you will
have to wait for the packets to fill. Increasing SDU on Sybrand's advice
is suicidal. Reading Sybrand's replies is sheer fun, but you shouldn't
follow his advice.
Sybrand Bakker
wrote:
>His client is spending 8 seconds to send the data. What's SDU got to do
>with it? Fortunately, your pleasant and charming personality makes even
>this advice useful....for Monty Python lovers.
So much for the 'kind' replies of 'Professional' Mr. Gogala.
Belonging to the redneck rabble already begins to show.
kuon
tried this application on both a macosx (double 2.5ghz) and a linux
(pretty big and nice configuration too), on both I got the exact same
problem as I got with java.
And I checked DNS, and I posted tnsping output.
Mladen Gogala
> As stated before I wrote an application in C, with instant client. I
> tried this application on both a macosx (double 2.5ghz) and a linux
> (pretty big and nice configuration too), on both I got the exact same
> problem as I got with java.
As stated before, you need to profile them both. Trace file doesn't lie.
Mladen Gogala
> So much for the 'kind' replies of 'Professional' Mr. Gogala. Belonging
> to the redneck rabble already begins to show.
Is the guy who was making comments on my physical appearance hurt? Is your
face a little bit red or is it just tomato soup on your beard? One way or
another. what does SDU have to do with "more data from client"? My basic
objection was that your advice was drivel. As for the kindness....who
lives by the kind word, dies by the kind word.
DA Morgan
> His client is spending 8 seconds to send the data. What's SDU got to do
> with it? Fortunately, your pleasant and charming personality makes even
> this advice useful....for Monty Python lovers.
Unless you see some value in tossing gasoline about it strikes me
that matches will follow soon.
Mladen Gogala
> Unless you see some value in tossing gasoline about it strikes me
> that matches will follow soon.
I do indeed. $2.5/gallon.