Increasing row retrieving speed via net8
- From: GG <grzegorzof@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 11 Apr 2012 20:30:31 +0200
Hi,
I'm doing large table processing (row fetching) via Net8 , remote
client using JDBC oracle client .
Did some testing using snapper and two VMs with Oracle 11.2.0.2 .
Got table T with 5M rows avg row length is 104 (its CATS from DBA_SOURCE
and doing simple select * from t) .
First I used BEQ (local connection :)) protocol as a reference :
SQL> @snapper all 5 1 145
Sampling SID 145 with interval 5 seconds, taking 1 snapshots...
setting stats to all due to option = all
-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
----------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE,
STATISTIC ,
DELTA, HDELTA, HDELTA/SEC, %TIME, GRAPH
----------------------------------------------------------------------------------------------------------------------------------------------------
145, GG , STAT, user
calls ,
1319, 1.32k, 263.8,
145, GG , STAT, session logical
reads , 23498,
23.5k, 4.7k,
145, GG , STAT, CPU used when call
started , 27,
27, 5.4,
145, GG , STAT, CPU used by this
session , 27,
27, 5.4,
145, GG , STAT, DB
time ,
43, 43, 8.6,
145, GG , STAT, user I/O wait
time , 2,
2, .4,
145, GG , STAT, non-idle wait
time , 15,
15, 3,
145, GG , STAT, non-idle wait
count , 10091,
10.09k, 2.02k,
145, GG , STAT, physical read total IO
requests , 698, 698, 139.6,
145, GG , STAT, physical read total multi block
requests , 687, 687, 137.4,
145, GG , STAT, physical read total
bytes , 91127808, 91.13M,
18.23M,
145, GG , STAT, cell physical IO interconnect
bytes , 91127808, 91.13M, 18.23M,
145, GG , STAT, consistent
gets , 23498,
23.5k, 4.7k,
145, GG , STAT, consistent gets from
cache , 11075, 11.08k,
2.22k,
145, GG , STAT, consistent gets -
examination , 11075,
11.08k, 2.22k,
145, GG , STAT, consistent gets
direct , 12423,
12.42k, 2.48k,
145, GG , STAT, physical
reads , 11124,
11.12k, 2.22k,
145, GG , STAT, physical reads
direct , 11124,
11.12k, 2.22k,
145, GG , STAT, physical read IO
requests , 698,
698, 139.6,
145, GG , STAT, physical read
bytes , 91127808,
91.13M, 18.23M,
145, GG , STAT, Number of read IOs
issued , 698, 698,
139.6,
145, GG , STAT, no work - consistent read
gets , 1348, 1.35k, 269.6,
145, GG , STAT, cleanouts only - consistent read
gets , 11073, 11.07k, 2.21k,
145, GG , STAT, immediate (CR) block cleanout
applications , 11073, 11.07k, 2.21k,
145, GG , STAT, commit txn count during
cleanout , 11073, 11.07k, 2.21k,
145, GG , STAT, cleanout - number of ktugct
calls , 11073, 11.07k, 2.21k,
145, GG , STAT, table scan rows
gotten , 659500,
659.5k, 131.9k,
145, GG , STAT, table scan blocks
gotten , 12421,
12.42k, 2.48k,
145, GG , STAT, buffer is not pinned
count , 12366, 12.37k,
2.47k,
145, GG , STAT, bytes sent via SQL*Net to
client , 70786316, 70.79M, 14.16M,
145, GG , STAT, bytes received via SQL*Net from
client , 26420, 26.42k, 5.28k,
145, GG , STAT, SQL*Net roundtrips to/from
client , 1321, 1.32k, 264.2,
145, GG , TIME, DB
CPU ,
334948, 334.95ms, 66.99ms, 6.7%, |@ |
145, GG , TIME, sql execute elapsed
time , 354361, 354.36ms,
70.87ms, 7.1%, |@ |
145, GG , TIME, DB
time ,
388813, 388.81ms, 77.76ms, 7.8%, |@ |
145, GG , WAIT, direct path
read , 20519,
20.52ms, 4.1ms, .4%, | |
145, GG , WAIT, SQL*Net message to
client , 7278, 7.28ms,
1.46ms, .1%, | |
145, GG , WAIT, SQL*Net more data to
client , 120134, 120.13ms,
24.03ms, 2.4%, |@ |
145, GG , WAIT, SQL*Net message from
client , 4023802, 4.02s,
804.76ms, 80.5%, |@@@@@@@@ |
-- End of Stats snap 1, end=2012-04-11 09:18:15, seconds=5
---------------------------------------------------------------------------------
Active% | SQL_ID | EVENT | WAIT_CLASS
---------------------------------------------------------------------------------
9% | 89km4qj1thh13 | SQL*Net more data to client | Network
5% | 89km4qj1thh13 | ON CPU | ON CPU
-- End of ASH snap 1, end=2012-04-11 09:18:15, seconds=5, samples_taken=43
so if I interpreting this correctly we can fetch like:
131.9k rows per sec which is good
Now switching to Net8 with arraysize 15:
SQL> @snapper all 5 1 149
Sampling SID 149 with interval 5 seconds, taking 1 snapshots...
setting stats to all due to option = all
-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
----------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE,
STATISTIC ,
DELTA, HDELTA, HDELTA/SEC, %TIME, GRAPH
----------------------------------------------------------------------------------------------------------------------------------------------------
149, GG , STAT, user
calls ,
12036, 12.04k, 2.41k,
149, GG , STAT, session logical
reads , 18097,
18.1k, 3.62k,
149, GG , STAT, CPU used when call
started , 67,
67, 13.4,
149, GG , STAT, CPU used by this
session , 67,
67, 13.4,
149, GG , STAT, DB
time ,
67, 67, 13.4,
149, GG , STAT, user I/O wait
time , 1,
1, .2,
149, GG , STAT, non-idle wait
time , 26,
26, 5.2,
149, GG , STAT, non-idle wait
count , 12923,
12.92k, 2.58k,
149, GG , STAT, physical read total IO
requests , 214, 214, 42.8,
149, GG , STAT, physical read total multi block
requests , 211, 211, 42.2,
149, GG , STAT, physical read total
bytes , 27951104, 27.95M,
5.59M,
149, GG , STAT, cell physical IO interconnect
bytes , 27951104, 27.95M, 5.59M,
149, GG , STAT, consistent
gets , 18098,
18.1k, 3.62k,
149, GG , STAT, consistent gets from
cache , 2843, 2.84k,
568.6,
149, GG , STAT, consistent gets -
examination , 2843,
2.84k, 568.6,
149, GG , STAT, consistent gets
direct , 15254,
15.25k, 3.05k,
149, GG , STAT, physical
reads , 3428,
3.43k, 685.6,
149, GG , STAT, physical reads
direct , 3428,
3.43k, 685.6,
149, GG , STAT, physical read IO
requests , 215,
215, 43,
149, GG , STAT, physical read
bytes , 28082176,
28.08M, 5.62M,
149, GG , STAT, Number of read IOs
issued , 216,
216, 43.2,
149, GG , STAT, no work - consistent read
gets , 12387, 12.39k, 2.48k,
149, GG , STAT, cleanouts only - consistent read
gets , 2847, 2.85k, 569.4,
149, GG , STAT, immediate (CR) block cleanout
applications , 2848, 2.85k, 569.6,
149, GG , STAT, commit txn count during
cleanout , 2848, 2.85k, 569.6,
149, GG , STAT, cleanout - number of ktugct
calls , 2848, 2.85k, 569.6,
149, GG , STAT, table scan rows
gotten , 180270,
180.27k, 36.05k,
149, GG , STAT, table scan blocks
gotten , 15225,
15.23k, 3.05k,
149, GG , STAT, buffer is not pinned
count , 15147, 15.15k,
3.03k,
149, GG , STAT, bytes sent via SQL*Net to
client , 22665295, 22.67M, 4.53M,
149, GG , STAT, bytes received via SQL*Net from
client , 238900, 238.9k, 47.78k,
149, GG , STAT, SQL*Net roundtrips to/from
client , 11944, 11.94k, 2.39k,
149, GG , TIME, DB
CPU ,
1566761, 1.57s, 313.35ms, 31.3%, |@@@@ |
149, GG , TIME, sql execute elapsed
time , 494193, 494.19ms,
98.84ms, 9.9%, |@ |
149, GG , TIME, DB
time ,
1566724, 1.57s, 313.34ms, 31.3%, |@@@@ |
149, GG , WAIT, direct path
read , 9762,
9.76ms, 1.95ms, .2%, | |
149, GG , WAIT, SQL*Net message to
client , 16290, 16.29ms,
3.26ms, .3%, | |
149, GG , WAIT, SQL*Net more data to
client , 231066, 231.07ms,
46.21ms, 4.6%, |@ |
149, GG , WAIT, SQL*Net message from
client , 3640845, 3.64s,
728.17ms, 72.8%, |@@@@@@@@ |
-- End of Stats snap 1, end=2012-04-11 09:36:35, seconds=5
---------------------------------------------------------------------------------
Active% | SQL_ID | EVENT | WAIT_CLASS
---------------------------------------------------------------------------------
7% | 89km4qj1thh13 | ON CPU | ON CPU
-- End of ASH snap 1, end=2012-04-11 09:36:35, seconds=5, samples_taken=42
here we can have like 36.05k rows per seconds .
Now with fetch size 500 I can do:
SQL> @snapper all 5 1 149
Sampling SID 149 with interval 5 seconds, taking 1 snapshots...
setting stats to all due to option = all
-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
----------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE,
STATISTIC ,
DELTA, HDELTA, HDELTA/SEC, %TIME, GRAPH
----------------------------------------------------------------------------------------------------------------------------------------------------
149, GG , STAT, user
calls ,
726, 726, 145.2,
149, GG , STAT, session logical
reads , 8640,
8.64k, 1.73k,
149, GG , STAT, CPU used when call
started , 183,
183, 36.6,
149, GG , STAT, CPU used by this
session , 183,
183, 36.6,
149, GG , STAT, DB
time ,
174, 174, 34.8,
149, GG , STAT, user I/O wait
time , 3,
3, .6,
149, GG , STAT, non-idle wait
time , 170,
170, 34,
149, GG , STAT, non-idle wait
count , 5925,
5.93k, 1.19k,
149, GG , STAT, physical read total IO
requests , 434, 434, 86.8,
149, GG , STAT, physical read total multi block
requests , 414, 414, 82.8,
149, GG , STAT, physical read total
bytes , 56483840, 56.48M,
11.3M,
149, GG , STAT, cell physical IO interconnect
bytes , 56483840, 56.48M, 11.3M,
149, GG , STAT, consistent
gets , 8640,
8.64k, 1.73k,
149, GG , STAT, consistent gets from
cache , 1006, 1.01k,
201.2,
149, GG , STAT, consistent gets -
examination , 1006,
1.01k, 201.2,
149, GG , STAT, consistent gets
direct , 7634,
7.63k, 1.53k,
149, GG , STAT, physical
reads , 6911,
6.91k, 1.38k,
149, GG , STAT, physical reads
direct , 6911,
6.91k, 1.38k,
149, GG , STAT, physical read IO
requests , 435,
435, 87,
149, GG , STAT, physical read
bytes , 56614912,
56.61M, 11.32M,
149, GG , STAT, Number of read IOs
issued , 436,
436, 87.2,
149, GG , STAT, no work - consistent read
gets , 6617, 6.62k, 1.32k,
149, GG , STAT, cleanouts only - consistent read
gets , 1016, 1.02k, 203.2,
149, GG , STAT, immediate (CR) block cleanout
applications , 1016, 1.02k, 203.2,
149, GG , STAT, commit txn count during
cleanout , 1016, 1.02k, 203.2,
149, GG , STAT, cleanout - number of ktugct
calls , 1016, 1.02k, 203.2,
149, GG , STAT, table scan rows
gotten , 363000,
363k, 72.6k,
149, GG , STAT, table scan blocks
gotten , 7630,
7.63k, 1.53k,
149, GG , STAT, buffer is not pinned
count , 7630, 7.63k,
1.53k,
149, GG , STAT, bytes sent via SQL*Net to
client , 41730447, 41.73M, 8.35M,
149, GG , STAT, bytes received via SQL*Net from
client , 14500, 14.5k, 2.9k,
149, GG , STAT, SQL*Net roundtrips to/from
client , 726, 726, 145.2,
149, GG , TIME, DB
CPU ,
2018693, 2.02s, 403.74ms, 40.4%, |@@@@ |
149, GG , TIME, sql execute elapsed
time , 1853871, 1.85s,
370.77ms, 37.1%, |@@@@ |
149, GG , TIME, DB
time ,
2018383, 2.02s, 403.68ms, 40.4%, |@@@@ |
149, GG , WAIT, direct path
read , 25985,
25.99ms, 5.2ms, .5%, |@ |
149, GG , WAIT, SQL*Net message to
client , 2854, 2.85ms,
570.8us, .1%, | |
149, GG , WAIT, SQL*Net more data to
client , 1674304, 1.67s,
334.86ms, 33.5%, |@@@@ |
149, GG , WAIT, SQL*Net message from
client , 2222786, 2.22s,
444.56ms, 44.5%, |@@@@@ |
-- End of Stats snap 1, end=2012-04-11 09:31:06, seconds=5
---------------------------------------------------------------------------------
Active% | SQL_ID | EVENT | WAIT_CLASS
---------------------------------------------------------------------------------
30% | 89km4qj1thh13 | SQL*Net more data to client | Network
3% | 89km4qj1thh13 | ON CPU | ON CPU
-- End of ASH snap 1, end=2012-04-11 09:31:06, seconds=5, samples_taken=40
72,6k rows fetched per fetch .
Is there anything to do I can go further with Net8 ?
I mean SDU in 11.2.0.2 defaults to 8k which seems ok .
Considering 100Mbit ethernet speed , I can have like 10Mbytes/per sec so
with 1GB table
i need ~ 100 sec to transfer that .
So feels like not on the edge yet .
Any ideas ?
Regards
GregG
--
//www.freelists.org/webpage/oracle-l
Other related posts:
