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
Oracle DBA in KSA 