Hello,
I'm transferring large volume of data ( 1-5GB) over GigE LAN between database server and application server. I'm observing very long time fetching this data from DB to App tier ( No! , Transfer to App Tier can't be avoided) .
I'm using Oracle 10gR2. Here is few more details.
FetchSize : 500-1000 rows.
SDU/TDU : Default , I beleive it's 2k.
MTU : Default of 1500 for Ethernet normal frame.
Here is the wait times from Tanel Podar's [script.|http://www.tanelpoder.com/files/scripts/waitprof.sql]
SID STATE EVENT P1 P2 P3 SEQ# % TotTime TEventTime(ms) DistEvnts Avgtime(ms)/Evnt
------- ------- ----------------------------------- -------------------------- --------------- --------------- ---------- --------- -------------- ---------- ----------------
4280 WAITING SQL*Net message from client driver id= 1413697536 #bytes= 1 = 0 95.63 1128.410 45 25.076
4280 WAITING db file scattered read file#= 168 block#= 6651 blocks= 16 3.36 39.695 1 39.695
4280 WORKING On CPU / runqueue .72 8.496 134 .063
4280 WAITING gc cr multi block request file#= 168 block#= 6650 class#= 1 .09 1.015 1 1.015
4280 WAITING db file scattered read file#= 168 block#= 6635 blocks= 16 .07 .873 1 .873
4280 WAITING gc cr multi block request file#= 168 block#= 6655 class#= 1 .05 .543 1 .543
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2001 = 0 .02 .189 8 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2002 = 0 .01 .142 6 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2011 = 0 .01 .094 4 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2006 = 0 .01 .094 4 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 1 = 0 .00 .047 2 .024
4280 WAITING SQL*Net message to client driver id= 1413697536 #bytes= 1 = 0 .00 .047 2 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2003 = 0 .00 .047 2 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2012 = 0 .00 .047 2 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2009 = 0 .00 .047 1 .047
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2008 = 0 .00 .047 2 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2010 = 0 .00 .024 1 .024
4280 WAITING SQL*Net message to client driver id= 1413697536 #bytes= 2003 = 0 .00 .024 1 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2016 = 0 .00 .024 1 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2015 = 0 .00 .024 1 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2013 = 0 .00 .024 1 .024
4280 WAITING SQL*Net message to client driver id= 1413697536 #bytes= 2014 = 0 .00 .024 1 .024
4280 WAITING SQL*Net more data to client driver id= 1413697536 #bytes= 2004 = 0 .00 .024 1 .024
Based on this each wait is no more than 0.024 ms. Is that high for GigE network?? How to imporve the fetch time here? Will changing SDU/TDU help here given MTU is 1500 ? If so how much?