The first thing I see in your output is that "more data to client" was only 10 seconds...The dominant part was the "from client" being 4000 seconds!
So I'm not sure I'm confident in using those figures.
Anyway, lets *assume* that "more data" was the issue here.
You'll can take a look at the raw trace file to get more info. For example, I copied your example
SQL> CREATE TABLE t (
2 "ID" NVARCHAR2(36) NOT NULL ENABLE,
3 "ROWINDEX" NUMBER(*,0) NOT NULL ENABLE,
4 "UPLOADID" NVARCHAR2(36) NOT NULL ENABLE,
5 "BUSINESSKEY" NVARCHAR2(1000) NOT NULL ENABLE,
6 "BATCHID" NVARCHAR2(36),
7 "SERVICENAME" NVARCHAR2(255) NOT NULL ENABLE,
8 "ISFAILED" NUMBER(1,0) NOT NULL ENABLE,
9 "FAILREASON" VARCHAR2(255 BYTE),
10 "NAME" NVARCHAR2(100) NOT NULL ENABLE,
11 "ATTRIBUTENAME" NVARCHAR2(255),
12 "ACTUALVALUE" NVARCHAR2(1000)
13 ) tablespace largets;
Table created.
SQL>
SQL> insert into t
2 select
3 sys_guid(),
4 1,
5 dbms_random.string('a',30),
6 dbms_random.string('a',900),
7 dbms_random.string('a',30),
8 dbms_random.string('a',230),
9 1,
10 dbms_random.string('a',230),
11 dbms_random.string('a',80),
12 dbms_random.string('a',230),
13 dbms_random.string('a',930)
14 from dual
15 connect by level <= 10000;
10000 rows created.
SQL>
SQL> insert into t
2 select * from t;
10000 rows created.
SQL>
SQL> insert into t
2 select * from t;
20000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T')
PL/SQL procedure successfully completed.
SQL> select num_rows, blocks , avg_row_len from user_tables
2 where table_name = 'T';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
40000 40697 5169
and then ran a simple SELECT *
The summarised trace data looks like:
select *
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 401 0.84 1.68 31796 40210 0 40000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 403 0.84 1.68 31796 40210 0 40000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
40000 40000 40000 TABLE ACCESS FULL T (cr=40210 pr=31796 pw=0 time=786007 us starts=1 cost=11036 size=206760000 card=40000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 401 0.00 0.00
SQL*Net more data to client 25599 0.00 0.35
SQL*Net message from client 401 0.00 0.05
db file sequential read 155 0.00 0.02
db file scattered read 1400 0.00 0.75
********************************************************************************
so you can see the ~25000 "more data" requests. To see how that came about, we can look at the raw trace data:
WAIT #2815597664216: nam='SQL*Net more data to client' ela= 15 driver id=1413697536 #bytes=8461 p3=0 obj#=7 tim=624936000635
WAIT #2815597664216: nam='SQL*Net more data to client' ela= 14 driver id=1413697536 #bytes=7142 p3=0 obj#=7 tim=624936000667
WAIT #2815597664216: nam='SQL*Net more data to client' ela= 14 driver id=1413697536 #bytes=8461 p3=0 obj#=7 tim=624936000697
WAIT #2815597664216: nam='SQL*Net more data to client' ela= 14 driver id=1413697536 #bytes=7142 p3=0 obj#=7 tim=624936000729
WAIT #2815597664216: nam='SQL*Net more data to client' ela= 14 driver id=1413697536 #bytes=8461 p3=0 obj#=7 tim=624936000758
WAIT #2815597664216: nam='SQL*Net more data to client' ela= 13 driver id=1413697536 #bytes=7672 p3=0 obj#=7 tim=624936000789
Notice that on my setup, I'm capped at approx 8k per request.
Since I've 40000 rows at about 5169 bytes each, that is going to be:
40000 * 5169 / 8500 = ~25k requests.
So a couple of things:
a) Check out the SDU/TDU parmaeters in sqlnet.ora to bump up the request size
b) Remember that nvarchar2 is going to be twice the size as a varchar2. You can see from above, I only inserted approx 2600 characters per row, but this yields double that because of nvarchar2.