I have an Oracle database getting large amounts of data over a dblink from another Oracle database. The top wait event is SQL*Net message from dblink. I understand that something has to be the top wait event no matter how things are performing. Using the centiseconds, it looks like about 24 seconds in a 10 minute period. This sounds worth looking into to me. That's about 5%. One suggestion was to hike up the SDU and TDU. What do you think? The downside is big mostly empty packets? Also, since the metalink notes seem to indicate that the listener.ora for the "remote" db must be set as well as the client, is there any way to prevent these settings from affecting other connections from different servers that are not presenting a problem currently? I.e. different clients?

Dear Tom,
<Ref MetaLink Doc ID: Note:1005123.6 >

Trying to tune SQL*Net.

4. Does increasing the array_size parameter provide a consistent increase in performance? Should this be raised until the MTU size is met? What are the negative consequences?
Array_size is often the easiest way to make orders-of-magnitude gains in performance. There is, however, a point of diminishing returns. In general, aim for an arraysize that would result in about 2k of data per request to SQL*Net if all columns were full. You could aim for an arraysize of 4K, a SQL*Net SDU of 4K and TCPIP packet size of 4K and maybe see some improvement.

Question on
"In general, aim for an arraysize that would result in about 2k of data per request to SQL*Net if all columns were full"

We have verious types of queries (both in Java and PL/SQL)for example
Query1 row size is 50 bytes
Query2 row size is 500 bytes
Query3 row size is 2000 bytes

So, how do I set arraysize that would closely match to size 2k.

Q1) Do I have to set java.sql.Statement.setFetchSize() for each statement(based on the row size) and arraysize in PL/SQL?

How do you set arraysize for connection?
Is it in the tnsnames and listener.ora
Thanks.

Tom,

We are finding lots of waits on SQL*Net more data to client and SQL*Net message from client.

For eg:-

WAIT #1: nam='SQL*Net more data to client' ela= 4652 p1=1413697536 p2=1982 p3=0

In order to minimize these waits we are looking at increasing the bytes sent in one go. We have set SDU and TDU to 32KB both in the listener and tnsnames.ora of the client.

Could you please confirm my understanding here

1. If my Sql*Plus arraysize is set to 5000 (say a big number). Would Oracle attempt to send bigger packets over the network and thus see an increased number of sql*net waits?

I have set TCP.NODELAY which means data would not be broken into small packets. But, still the p2 value shown in the waits do not change.

2. Qn: Why does P2 not change? Who controls the bytes size and what makes it change. Irrespective of my SDU,TDU settings, p2 hovers around the 2000 mark.

3. If we see an increased number of sql*net more data waits, is the only tunable parameter; the arraysize?

Regards,
Rennie

Tom,
According to one of the docs i read
"Ideally, SDU should not surpass the size of the maximum transmission unit(MTU)"

1.Since the default for SDU is 2K and my MTU being 1500 bytes, should i readjust the SDU parameter in listener and tnsnames to 1500 bytes to get any sort of gain or will it be fine with the default setting?

2. If my maximum transmission unit is 1500 bytes what is the point in keeping SDU at 2048 as a single packet of 2048 will then be divided into two(1500+548) which would be actaully wastage of network resources.

Thanks

Thsts right Tom, but i just wanted to know those things just from a knowledge point of view.If you can explain that then it would be great.

Thanks

As a first-time "reviewer", MANY thanks to Tom whose invaluable insights have helped me find incredible performance boosts, although I am not a trained DBA.

Please note :
-- Ethernet packets contain 1500 bytes of DATA. This is the MTU.
-- These 1500 bytes hold the IP packet, which has 40 or 60 bytes of header and 1440-1460 bytes of DATA. This is the MSS.

The SDU should be a multiple of the MSS, not the MTU ! I do wish Oracle would integrate this into its performance tuning docs.

With Ethernet, if you set the SDU to 1500, you will send 1500 bytes of data in two packets. If you leave it at the default, you will send 2048 bytes in two packets. If you set it to 1440, you will send 2880 bytes in two packets.

I confirmed these results using IP trace. However, "optimal" settings did not increase performance over the default settings. Why? Because I have a decent, properly configured network that can maintain throughput despite some fairly small packets.

Bottom line: Tom's right (surprise), leave this stuff alone. If there are network problems, fix them.

Please note that each SQL statement is treated separately. If you ask for 10 bytes of data, you will get a packet with 10 bytes of data in it. Also, each fetch is treated separately so avoid small fetch sizes. I believe Tom gave 100 rows as a rule of thumb earlier.

I have a third party package runnining on one machine where Oracle 8.1.7 client runs; this 3rd party package makes use of 8.1.7 client to connect to the 10gR2 DB server on another machine. (Oracle 10.2.0.4.0 -- on SUSE Linux Enterprise Server 10 SP2 RC3 (ia64)) When queries fetching large data (5000 rows * 430 (Average row size)=2100 K) are fired from this package the session on 10gR2 hangs and I can see the hanged process' PID on the OS consuming >90% CPU. The wait event from v$session is 'SQL*Net more data to client'.

I did increase the SDU size to 16K on both tnsnames.ora and listener.ora for both client and server as per suggestion of support. The problem still persists. I am also unable to run strace command on the PID. Tried to trace the session using DBMS_SUPPORT package. It did not generate any trace data. Please help.

Tom -

I just need some clarification on this topic. Suppose that a 10gR2 server and client are using the default SDU setting of 2048. Also suppose that the server is sending a 1,200 byte message to the client (smaller than the MTU of the IP network infrastructure). How many packets will be sent across the network? Will there be only one packet sent or will it still send two, one of which will be empty?

Thank you.

ops$tkyte%ORA11GR2> set autotrace on statistics;
ops$tkyte%ORA11GR2> select * from dual;

D
-
X


Statistics
----------------------------------------------------------
         27  recursive calls
          0  db block gets
         19  consistent gets
          2  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed