Introduction
I come across this SDU issue from time to time by doing oracle database consulting work and so i think it is worth to write a blog about it. Basically this blog is about how to verify that the SDU setting is considered (or not) at all. It also covers some basics and SAP suggestions.
Lets' start with an explanation and some SAP information about the SDU, before we go on with researching and testing several settings.
[UPDATE 1] 12/05/13: Today i received a mail with a follow-up question about how to cross-check the SDU size setting from an application point of view and do not rely on the listener trace output only. At first i replied privately by mail with a short demo case, but i think the demo is also valuable for everybody. So i added section "Verify SDU settings from an application point of view" to this blog post.
Official Oracle 11g R2 documentation
Under typical database configuration, Oracle Net encapsulates data into buffers the size of the session data unit (SDU) before sending the data across the network. Oracle Net sends each buffer when it is filled, flushed, or when an application tries to read data. Adjusting the size of the SDU buffers relative to the amount of data provided to Oracle Net to send at any one time can improve performance, network utilization, and memory consumption. When large amounts of data are being transmitted, increasing the SDU size can improve performance and network throughput.
The amount of data provided to Oracle Net to send at any one time is referred to as the message size. Oracle Net assumes by default that the message size will normally vary between 0 and 8192 bytes, and infrequently, be larger than 8192 bytes. If this assumption is true, then most of the time, the data is sent using one SDU buffer.
The SDU size can range from 512 bytes to 65535 bytes. The default SDU for the client and a dedicated server is 8192 bytes. The default SDU for a shared server is 65535 bytes.
The actual SDU size used is negotiated between the client and the server at connect time and is the smaller of the client and server values. Configuring an SDU size different from the default requires configuring the SDU on both the client and server computers, unless you are using shared servers. For shared servers, only the client value must be changed because the shared server defaults to the maximum value.
SAP Information / documentation
- SAPnote #562403 - FAQ: Oracle Net
What do the contents of tnsnames.ora look like?
For example:
< sid>.WORLD= (DESCRIPTION = (SDU = 32767) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = <sid>)) (ADDRESS = (PROTOCOL = TCP) (HOST = <host>) (PORT = <port>))) (CONNECT_DATA = (SID = <sid>)))
.. contains the SDU (Session Data Unit) that defines the size of the data packets that were sent at session level. The maximum value is 32767 (Oracle 11.2.0.2 or higher: 65535). The larger the value selected, the fewer the number of packages that have to be sent when larger volumes of data are exchanged.
What do the contents of listener.ora look like?
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SDU = 32767) (SID_NAME = <sid>) (ORACLE_HOME = /oracle/<sid>/817_64)))
... contains the SDU that is also contained in tnsnames.ora.
What must I take into account with regard to network performance between SAP and Oracle?
The smaller the SDU set in listener.ora or tnsnames.ora, the less data can be transferred for each network communication. To avoid unnecessary network communications if there are large volumes of data, you can increase the SDU in listener.ora and tnsnames.ora to 32767 (Oracle 11.2.0.2 or higher: 65535); see the sample files above. However, note that a higher SDU value may result in an overhead when lower volumes of data are transferred.
As of Oracle 9.2.0.4 or 10g, you can also adjust the default SDU size. To do this, you must set the DEFAULT_SDU_SIZE=<size_in_byte> parameter in sqlnet.ora. The maximum value is 32767 (Oracle 11.2.0.2 or higher: 65535).
- SAPnote #618868 - FAQ: Oracle performance
It also makes sense to set the maximum SDU size in tnsnames. ora and listener.ora, and, if possible, to define the maximum DEFAULT_SDU_SIZE in sqlnet.ora (refer to Note 562403).
- SAPnote #1100926 - FAQ: Network performance
When you use an Oracle database, ensure in accordance with Note 562403 that the Oracle Net configuration (TCP.NODELAY, SDU, DEFAULT_SDU_SIZE, and so on) is optimal.
This is a lot of information for the beginning, but it also covers a lot of important topics.
Basically we need to remember the following key-points:
- Setting SDU size to a higher value can improve the performance by avoiding unnecessary network communication and better "throughput" by processing huge amount of data
- The SDU size is negotiated on handshake between the client and server (listener) by connect and the lowest setting of both is used
- Setting DEFAULT_SDU_SIZE is enough regarding the SAPnotes (by the way DEFAULT_SDU_SIZE is already set to 32768 by SAPinst)
- You can set it to a max value of 32768 or 65535 bytes depending on the Oracle release (server and client)
The researching
My test environment is an Oracle database server and client (11.2.0.3.2) on OEL 6.2. It is important to mention both components, because of i have found out that different supported combinations (e.g. like Oracle 11g R2 server and 10g R2 client) work differently. However this researching is focused on how to verify the negotiated SDU size.
I want to provide some information about the listener trace content before we take a look at it:
In the first section you can see the connection data being passed in by the client and in the last section you see the line where the server reports what it can do. If two nsconneg lines show two different values for the SDU size - the client and server both volunteer information about what they can do, and the final result is the lower of the two offers.
Default setting (DEFAULT_SDU_SIZE and SDU parameters are not set in tnsnames.ora / listener.ora)
shell> lsnrctl set trc_level admin
shell> lsnrctl show trc_file
LISTENER parameter "trc_file" set to ora_1920_139698349319936.trc
shell> sqlplus system/<PASS>@<SID>
.. SDU size of 8192 bytes is used as default setting if nothing is set explicitly. This is also mentioned in the official Oracle documentation and works as expected.
Setting DEFAULT_SDU_SIZE to 32768 in sqlnet.ora (SDU parameters are not set in tnsnames.ora / listener.ora)
shell> lsnrctl stop
shell> lsnrctl start
shell> lsnrctl set trc_level admin
shell> lsnrctl show trc_file
LISTENER parameter "trc_file" set to ora_1985_139887209207552.trc
shell> sqlplus system/<PASS>@<SID>
.. SDU size of 32768 bytes is requested by the client, but the server is only able to work with 8192 bytes and as documented only 8192 bytes are used in this case.
Setting DEFAULT_SDU_SIZE to 32768 in sqlnet.ora and SDU parameter in listener.ora (SDU parameter is not set in tnsnames.ora)
The following listener.ora setting is done according SAPnote #562403:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SDU = 32768) (SID_NAME = <sid>) (ORACLE_HOME = /oracle/<sid>/<vers>)))
shell> lsnrctl stop
shell> lsnrctl start
shell> lsnrctl set trc_level admin
shell> lsnrctl show trc_file
LISTENER parameter "trc_file" set to ora_2139_140549120628480.trc
shell> sqlplus system/<PASS>@<SID>
.. SDU size of 32768 bytes is requested by the client, but the server is only able to work with 8192 bytes and as documented only 8192 bytes are used in this case. But what is wrong here? We follow the second SAP instruction (the first suggestion is about setting DEFAULT_SDU_SIZE and second suggestion is about set the SDU parameter in the listener.ora as well), but it still does not work as expected.
Setting DEFAULT_SDU_SIZE to 32768 in sqlnet.ora and SDU parameters in listener.ora and tnsnames.ora
This combination is not tested, because of the client already uses a SDU size of 32768 bytes and setting this in tnsnames.ora once again makes no difference at all.
Setting DEFAULT_SDU_SIZE to 32768 in sqlnet.ora and SDU parameter in listener.ora (SDU parameter is not set in tnsnames.ora) - Part II
Let's try another configuration, that is not mentioned in the Oracle or SAP documentation:
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (SDU = 32768) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <host>) (PORT = <port>) )) ))
shell> lsnrctl stop
shell> lsnrctl start
shell> lsnrctl set trc_level admin
shell> lsnrctl show trc_file
LISTENER parameter "trc_file" set to ora_2245_139786876253952.trc
shell> sqlplus system/<PASS>@<SID>
.. SDU size of 32768 bytes is requested by the client and the server is able to work with it as well. Now we are using a SDU size of 32768 bytes as wanted.
Verify SDU settings from an application point of view
As already mentioned previously it is very important to test and verify the behavior for every client, server and version combination. In a few combinations i also noticed that adjusting the DEFAULT_SDU_SIZE at server and client side (in sqlnet.ora) is already sufficient, even if the listener trace states something different. You can use Snapper by Tanel Poder for validating the SDU settings. Let's take a step back and discuss various "SQL*Net message" wait events before digging into the demo. Tanel Poder has also written an awesome blog post about this called SQL*Net message to client vs SQL*Net more data to client. The key statements of his blog post can be summarized in the following way:
- The first session data unit (SDU) bufferful of return data is written to TCP socket buffer under “SQL*Net message to client” wait event
- If Oracle needs to write more data for a call than fits into the first SDU buffer, then further writes for that call are done under "SQL*Net more data to client" wait event
- "SQL*Net message to client" and "SQL*Net more data to client" waits only record the time it took to write the return data from Oracle’s userland SDU buffer to OS kernel-land TCP socket buffer
- All of the time a TCP packet spent “flying” towards the client is actually accounted as "SQL*Net message from client" wait event
The following demo case was run on OEL 6.4 with Oracle RDBMS 11.2.0.3.6 and Oracle client 11.2.0.3.0. The network wait times differ due to parallel testing scenarios on my environment (especially the first case is faster based on third party settings and workload, but case two and three are comparable from an infrastructure point of view), but we just need to focus on the amount of wait events (NUM_WAITS) for cross-checking the SDU setting from an application point of view.
SQL
SQL> set termout off
SQL> set arraysize 5000
SQL> select * from dba_source;
Default setting (DEFAULT_SDU_SIZE and SDU parameters are not set in tnsnames.ora / listener.ora)
Setting DEFAULT_SDU_SIZE to 32768 in sqlnet.ora at client side (SDU parameters are not set in tnsnames.ora / listener.ora)
Notice the same amount of "SQL*Net more data to client" wait events after setting the DEFAULT_SDU_SIZE to 32768 at client side (only). In this case the SDU size has been negotiated at 8192 bytes (the same value as the default setting).
Setting DEFAULT_SDU_SIZE to 32768 in sqlnet.ora and SDU parameter in listener.ora (SDU parameter is not set in tnsnames.ora) - Part II
Notice how the amount of "SQL*Net more data to client" wait events haven been decreased. The same amount of data was read and transferred to the client in all three cases, but only in the last case it needed less SDU buffers for transferring the data.
Summary
This blog should not provide a "recommended SDU setting" - it is much more about how you can verify the implemented settings (by SAP) on your own. Unfortunately the information that is provided by Oracle and SAP does not seem to be correct at all (anymore). Maybe the mentioned settings have worked in older Oracle versions (just check the mentioned ORACLE_HOME version in SAPnote #562403), but nowadays it seems to get even more complex. However the deployed default sqlnet.ora configuration (by SAPinst) may not be sufficient at all for increasing the SDU size.
Don't believe everything you read - test and verify it on your own.
If you have any further questions - please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database issues.
References
- SAP Managed Tags:
6 Comments
