dump data on the server is slow, most of the time is spent on " SQL*Net message from client ". The sql is simple:
select * from test_net.
20:01:02 SQL>select * from test_net.
100000 rows selected.
Elapsed: 00:00:39.27
The elapsed time show in tkprof report file is 7.56, but the elapse time from sqlplus about 40. Looks to me the time for "SQL*Net message from client" is not include in the elapse time in tkrpof, but it will be included in the client side elapse time.
I tried to tune SDU,TDU, and tcp window but no gain. Even use IPC, it's still slow. If there is no way to improve this, then the good way to send tons of data to remote side is to dump it locally (even exp is slow, we may use expdp and run in parallel to kick off several connections to improve the thoughtput) and then import it at the remote side.
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.01 0.00 0 0 0 0
Execute 6 0.00 0.00 0 6 2 2
Fetch 23 3.65 7.55 0 5194 0 100012
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 35 3.66 7.56 0 5200 2 100014
Misses in library cache during parse: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 28 0.00 0.00
SQL*Net message from client 27 8.99 39.08
SQL*Net more data to client 16823 0.00 4.60