Small TNS packages
Troels Arvin
I'm transferring a sizeable amount of data from an Oracle 11 DBMS over a
VPN tunnel; the client software is also Oracle 11. Both client and server
are running Linux. There row widths in the involved tables vary somewhat,
but BLOBs aren't involved.
When inspecting the data in Wireshark, I see TNS packages with sizes
between 350 and 450; the TCP window size is 501.
I find this to be rather small drops of data.
Are TNS packages this small normal?
joel garry
Look up SDU and and SEND_BUF_SIZE (and related parameters) in the
docs. In 11g, the default is 8K for dedicated and 32K for shared
connections. MOS Note: 44694.1 explains it in some detail, it could
be up to 1430 bytes of actual data. You may need to adjust the SDU to
be a multiple of your window size. Note: 260984.1 explains how to use
the send and receive buffer size commands to affect the tcp size, and
also disavows any knowledge of your actions.
jg
--
@home.com is bogus.
"should you decide not to accept this mission, you're fired!" - Get
Smart
Mladen Gogala
That depends on the characteristics of your fetch. If you're doing an
array fetch, this is unusual. If, on the other hand, you're doing single
row operations, that's the normal behavior.
Troels Arvin
> Look up SDU and and SEND_BUF_SIZE (and related parameters) in the docs.
Those are server-side parameters?
As a follow-up:
The application uses JDBC. By using
((OracleConnection)connection).setDefaultRowPrefetch(10000)
we experienced a dramatic speedup: Execution time was cut to 1/20
(although TNS packet sizes had only increased by a factor 5, from around
400 to around 2000).
Thanks to you and and Mladen, by the way.
--
Troels
joel garry
> joel garry wrote:
> > Look up SDU and and SEND_BUF_SIZE (and related parameters) in the docs.
>
> Those are server-side parameters?
See the net services and administration guides, as well as metalink.
The recv_buf_size is the client side of the send_buf_size.
http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/plan.htm#sthref616
says "You can deploy SDU at the client, the application Web server,
and the database server."
>
> As a follow-up:
> The application uses JDBC. By using
> ((OracleConnection)connection).setDefaultRowPrefetch(10000)
> we experienced a dramatic speedup: Execution time was cut to 1/20
> (although TNS packet sizes had only increased by a factor 5, from around
> 400 to around 2000).
>
> Thanks to you and and Mladen, by the way.
You're welcome. Note the lesson here: You can usually get much
greater performance improvement from fixing the app than tweaking
parameters. Tune the app then tune the system if there is still a
problem (assuming the system is not way out in left field, which is
why a good methodology will rule out gross misconfiguration first -
this has less importance than in the past, since defaults are mostly
reasonable these days, if you follow directions and aren't doing
anything strange). Fixing a wayward app often helps the rest of the
system. And where do apps come from? That's why there are books like
"Effective Oracle by Design."
The best performance comes from not doing unnecessary work. Here, the
unnecessary work was in generating too many fetches. Is there more?
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2009/nov/23/hp-profit-jumps-on-cost-cuts-new-market-expansion/