Skip to Content

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>



/wp-content/uploads/2013/02/default_sdu_182702.png

.. 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>



DEFAULT_SDU_SIZE.png

.. 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>



DEFAULT_SDU_SIZE_SDU.png

.. 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>



DEFAULT_SDU_SIZE_SDU_CUST.png

.. 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)

Default_SDU.png

Setting DEFAULT_SDU_SIZE to 32768 in sqlnet.ora at client side (SDU parameters are not set in tnsnames.ora / listener.ora)

Client_SDU_SQLnet.png

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

Client_Server_SDU_SQLnet.png

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

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Jens Gleichmann

    Hi Stefan,

    excellent blog (as usual)!!!

    I’ve tried this also in my test environment and can confirm this behaviour!

    But you have to notice if you want to change the listener admin restrictions (e.g. trace level), you have to modify listener.ora to set ADMIN_RESTRICTIONS_LISTENER = OFF !

    By default SAP is set this to on because of security reasons.

    Another thing is the own config of SAP in the profile directory (/sapmnt/SID/profile/oracle). You also have to take care of this files.

    But I can’t imagine that this is not document by SAP nor Oracle. So every time I thought I have set SDU size correct it was more show and shine instead of a useful work.

    Where did you find the last listener.ora entry? Only with this entry the SDU size can be really increased!

    Great work, thanks for analyzing and sharing this!

    Best Regards,

    Jens

    (0) 
    1. Stefan Koehler Post author

      Hi Jens,

      > Another thing is the own config of SAP in the profile directory (/sapmnt/SID/profile/oracle). You also have to take care of this files.

      Not really due to fact that the listener configuration is read from $ORACLE_HOME by default. You need to modify the corresponding listener.ora, if you have defined a custom TNS_ADMIN for ora<sid> (but this makes no sense at all in a SAP environment). The corresponding DEFAULT_SDU_SIZE (which is used by the client) is already set in both environments for newer releases (afaik).

      > Where did you find the last listener.ora entry?

      Just by researching / reengineering. I noticed that “unusual” network packet sizes by performance troubleshooting a mid-sized SAP system (which needed to transfer a lot of data to the application server by design). Maybe it is a bug in the newer Oracle releases, that the documented syntax is not recognized properly (you can crosscheck this by enabling the listener trace and search for “nsgllsn”) or whatever … i have no clue …

      Regards

      Stefan

      (0) 
      1. Jens Gleichmann

        Hi Stefan,

        > Another thing is the own config of SAP in the profile directory (/sapmnt/SID/profile/oracle). You also have to take care of this files.

        Not really due to fact that the listener configuration is read from $ORACLE_HOME by default. You need to modify the corresponding listener.ora, if you have defined a custom TNS_ADMIN for ora<sid> (but this makes no sense at all in a SAP environment). The corresponding DEFAULT_SDU_SIZE (which is used by the client) is already set in bothenvironments for newer releases (afaik).

        => Nope, the last systems which I have installed pointed by default to the profile directory! This means environment variable TNS_ADMIN had the value /usr/sap/SID/SYS/profile/oracle . I don’t know when SAP changed this but with the new SWPM (Software Provisioning Manager – there is no install master any more) the environment is set to this value.

        > Where did you find the last listener.ora entry?

        Just by researching / reengineering. I noticed that “unusual” network packet sizes by performance troubleshooting a mid-sized SAP system (which needed to transfer a lot of data to the application server by design). Maybe it is a bug in the newer Oracle releases, that the documented syntax is not recognized properly (you can crosscheck this by enabling the listener trace and search for “nsgllsn”) or whatever … i have no clue …


        => I have tested it on 11.2.0.3 , I will test it also for 10G and let you know the results 😉

        edit: Tested on Oracle 10.2.0.2 -> same behaviour!!!

        Cheers,

        Jens

        (0) 
  2. Former Member

    Hi Stefan,

    Thanks for the excellent post. However, I didn’t quite understand the “Part II” part. Did you set DEFAULT_SDU_SIZE in the server’s sqlnet.ora and as well as listener.ora file? If so, is it needed in both the places?

    In my case, I have a database to database connection for a materialized view refresh. I set DEFAULT_SDU_SIZE=32767 in sqlnet.ora in both server and client(database server) but I still only see 8192 in the trace files.

    Can you please clarify?

    Anand

    (0) 
    1. Stefan Koehler Post author

      Hi Anand,


      > In my case, I have a database to database connection for a materialized view refresh … but I still only see 8192 in the trace files.


      This is a possible “anomaly” depending on the exact version and RDBMS / client (type) constellation, that i have mentioned in the blog post.

      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.

      I have used an instant client (on Mac OS X) and the usual RDBMS server software (on Linux) in my “Part II” example.

      The easiest way to check your “real” used SDU size is to run Snapper for a simple SELECT statement (which uses several SDU buffers) over that database link. After you captured that baseline you can remove the “DEFAULT_SDU_SIZE” parameter and do the same SELECT again. If the amount of “SQL*Net” events are still the same, then the “DEFAULT_SDU_SIZE” setting is not enough (as the default size is used), but if the second SELECT shows a higher amount of “SQL*Net more data to client” events, then setting the “DEFAULT_SDU_SIZE” is sufficient in your case and you have previously used a larger SDU size.

      > Did you set DEFAULT_SDU_SIZE in the server’s sqlnet.ora and as well as listener.ora file? If so, is it needed in both the places?


      In the last example i have set the DEFAULT_SDU_SIZE in sqlnet.ora (on both sides as far as i can remember) and added the SDU line (“(DESCRIPTION = (SDU = 32768)) in the listener.ora file on server side as well. That has worked in my client / server combination, but as previously mentioned just check your configuration and version/type combination with Snapper.

      Regards

      Stefan

      (0) 

Leave a Reply