SQL*Net More Data to Client & SDU

In one of our projects, we had an operation that moves large amounts of data through a db link. This operation was taking much longer than expected, a SQL trace indicated excessive number of SQL*Net more data to client waits. This led us to perform an extensive investigation of all layers between the 2 databases: OS parameters, network, firewall/DMZ which all came back OK.

In our development environment, we decided to increase SDU parameter to 32K and see the outcome of such modification. Prior to this test, our impression of SDU parameter was a very little improvement in performance can be achieved by increasing this value and not worth fiddling with.

Test Scenario Setup:

  • Create a db link pointing back to same database so we eliminate any network traffic overhead.
  • Run 2 tests; one with default values and the second test with SDU value set at 32767 at both the TNS and listener.
  • Create an additional listener with a different name and listening on any port outside 1521. This along with local_listener parameter left at default will disable dynamic registration with the new listener. Dynamic registration overwrites SDU parameter according to Metalink article 124802.1 SDU Value not Working with Dynamic Registration. When creating an additional listener use netca not netmgr. We couldn’t logon with listeners created with netmgr. We got an error ORA-01034 Oracle not available.
  • Create an additional TNS entry with new SDU value
  • Enable SQLNET trace to verify SDU parameter is set correctly.
  • Trace remote sessions with dbms_monitor.enable_session_trace.

Test 1 using default values

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      353     30.87      98.88      36206      53047          0    11503946
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      355     30.88      98.88      36206      53047          0    11503946

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows     Row Source Operation
-------  ---------------------------------------------------
11503946  TABLE ACCESS FULL TABLE1 (cr=53047 pr=36206 pw=0 time=11504289 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     353        0.00          0.00
  SQL*Net message from client                   353        0.32         38.37
  SQL*Net more data to client                 38967        0.16         68.63
  db file scattered read                       2274        0.04          4.42

Test 2 using SDU at 32K

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch      353     27.33      59.64      36872      53047          0    11503946
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      355     27.34      59.65      36872      53047          0    11503946

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows     Row Source Operation
-------  ---------------------------------------------------
11503946  TABLE ACCESS FULL TABLE1 (cr=53047 pr=36872 pw=0 time=11504274 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     353        0.00          0.00
  SQL*Net message from client                   353        0.30         69.02
  SQL*Net more data to client                  2223        0.24         29.76
  db file scattered read                       2316        0.12          6.87

In 2nd test with SDU set at 32K, we’ve seen SQL*NET more data to client total wait time dropped down more than half, in other tests (not published here), we seen 10% improvement in elapsed time. A very welcomed improvement.

How to Put in Production

We don’t totally understand the exact ramification of increasing this value on a production system, so to play it safe, we decided to create a new listener at the remote site and a new TNS entry local site as indicated earlier in the test setup. The db link would use both new TNS entry pointing to the new listener at the remote site.

Conclusion

Projects that move large amounts of data across a TCP link such as Oracle Streams, materialized view over a db link, etc… can see some benefits by increasing SDU value.

Hazem Ameen
Senior Oracle DBA

Advertisements

One thought on “SQL*Net More Data to Client & SDU

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s