One of the cool new things in 12.1 is that you can set the Session Data Unit to 2MB. This might not sound like a Big Deal, but getting this to work required me to dig deeper into the TNS layer than I intended…Then I somehow got stuck on the wrong track, thankfully the team at Enkitec helped out here with pointers.
This post is rather boring if you just look at it but it’s probably one of the best examples where a few sentences in writing are vastly different from the time it took to get to the bottom of it. The solution explained here works on our 18.104.22.168.3 Exadata lab system, which is an X2-2 but should likewise be applicable for other configurations.
The Server Setup
I created a new TNS_ADMIN directory just for the new listener I wanted to test with. Experiments with the live listener are not encouraged at all, you might cause disruption. So here’s the listener.ora file I created for my new listener. It’s created on the IB network. In the little testing I performed I didn’t get more than 64k SDU when using bondeth0 as the network.
> cat listener.ora listener_sdutest = (DESCRIPTION_LIST = (DESCRIPTION = (SDU = 2097152) (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.x.x) (PORT = 1891) ) ) ) sid_list_listener_sdutest = (sid_desc= (GLOBAL_DBNAME=db12c) (ORACLE_HOME = /u01/app/oracle/product/22.214.171.124/dbhome_1) (sid_name = db12c1) ) TRACE_LEVEL_listener_sdutest=16 trace_directory_listener_sdutest=/home/oracle/mbach/tns/trace diag_adr_enabled_listener_sdutest=off trace_file_listener_sdutest = bigsdu.trc
Please ignore the tracing information for now. In the file I define the SDU to be 2M. Note how the SDU = directive belongs immediately beneath the description. It does not seem to have an effect in the sid_list_listener part, at least not in my tests. This is actually the result of about 2 hours of research…quite underwhelming if you asked me now.
Alongside the listener.ora I created a sqlnet.ora file to set the default_sdu_size, just in case.
> cat sqlnet.ora DEFAULT_SDU_SIZE=2097152
That’s it! Over to the client.
The Client Configuration
Now I need a 12c client to test. I am using the following tnsnames.ora and sqlnet.ora files:
> cat sqlnet.ora DEFAULT_SDU_SIZE=2097152 trace_level_client=16 trace_directory_client=/home/oracle/mbach/tns/trace trace_file_client=client.trc trace_unique_client = true DIAG_ADR_ENABLED=OFF > cat tnsnames.ora db12cbigsdu = (DESCRIPTION = (SDU=2097152) (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.x.x) (PORT = 1891) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB12C) ) )
Ignore the tracing information for now, you will see in a little while why this is important. The connection information points me to the IB network again. Now if I start the listener and connect to it, I can grep for the SDU sizes in the trace files.
Now I can check if I have actually been able not only to negotiate, but also receive the 2M SDU. On the listener side it looks good:
> grep 'nsconneg.*.sdu.*' trace/bigsdu.trc [10-JUN-2014 16:51:16:840] nsconneg: vsn=315, lov=300, opt=0xc41, sdu=65535, tdu=65535, ntc=0x7f08 [10-JUN-2014 16:51:16:840] nsconneg: Large sdu=2097152,tdu=2097152,compression flg=0 [10-JUN-2014 16:51:16:840] nsconneg: vsn=315, gbl=0xc01, sdu=2097152, tdu=2097152
Likewise, on the client side:
> grep 'nsconneg.*.sdu.*' trace/client_26421.trc (2998822304) [10-JUN-2014 16:51:16:859] nsconneg: vsn=315, gbl=0xa41, sdu=2097152, tdu=2097152
So it all looks ok!
Considerations for RAC
Based on Stefan Koehler’s comment (see below) I thought I’d add the word-of-warning to the RAC users amongst us. In the above example I created a single listener, not registered in Clusterware, for a test. For the typical use-case: data migration-this listener wouldn’t be on the Exadata, it would be on the source databases system. I usually pull data from the source to Exadata.
If however you need to change the SDU in a RAC listener, please refer to “Setting Parameters for Scan and Node Listeners on RAC, Queuesize, SDU, Ports, etc (Doc ID 1292915.1)” for more information. In summary changes to the listener configuration files are discouraged because most listener properties are maintained by Clusterware. The SDU size specifically requires you to set the DEFAULT_SDU_SIZE in sqlnet.ora (in $GRID_HOME/network/admin), not in the listener configuration file.
How to Determine SDU Value Being Negotiated Between Client and Server (Doc ID 304235.1)
Stefan Koehler on SAP SCN: http://goo.gl/y0MAuH
As well as the usual suspects: Net Services Admin and Reference Guide.