playing with SDU/TDU

Platform: Ora 8.0.5, NT4

Do I have a problem with my testing methods or my expectations?

SQL*Net related wait events continue to be my biggest hitters, so I decided to play around w/ SDU and TDU.

First, I pulled the 5 most executed and 5 "most blocks read" queries from v$sqltext and put all in a sql script, adding a CONNECT at the top of the script and a DISCONNECT at the bottom.

Next, set the SDU and TDU both to 32768 for the sid in question in LISTENER.ORA.

Next, set the SDU and TDU both to 2048 for the sid in question in TNSNAMES.ORA.

Next, run utlbstat, execute the script 20 times, then run utlestat.

Repeat the 'bstat - execute - estat' cycle, incrementing SDU/TDU in tnsnames by 2k increments until we reach a value of 20k.

Examining the estat reports showed virtually no change in any of the SQL*Net wait events across the range of tests.

Next I tried evaluating SQLNet traces. I set client_trace_level=16, then repeated the cycle of incrementing SDU/TDU in tnsnames from 2k to 20k in 2k increments. For these tests, I only executed the script once at each value, and isolated the trace files for each trial, then counted the total number of packets sent and received at each SDU value. Results are as follows:

SDU value - packets received - packets sent

2k	86	69
4k	73	66
6k	69	66
8k	69	66
10k	67	66
12k	65	66
14k	65	66
16k	65	66
18k	65	66
20k	65	66

All of these trials were run on a test server. The databases have approximately the same data volumes as production (they are refreshed from full production exports) but the user traffic is far less than in production.

So, I see that increasing SDU resulted in a decrease of packets sent from the server to the client, until we reached SDU=12k, but there was no corresponding improvement in TNS* related wait events. So is this an indication that I should set my SDU/TDU values to 12k? Would that be a waste of time? Or am I on the right track but using flawed testing methods?

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)