使用Array Fetch对于fetch的性能有很大的影响, 下面是我测试的结果,Array的大小并不是越大越好,在sqlplus中默认是15,在oracle的jdbc中默认是10, 在我的程序中一般是设为50或100的
SQL> set fetchsize 1
SQL> unload select * from testobj >> a.txt;
Query executed in 00:00:00.030
100000 rows writed in 00:00:10.983
200000 rows writed in 00:00:22.046
300000 rows writed in 00:00:34.234
400000 rows writed in 00:00:49.132
500000 rows writed in 00:01:01.039
600000 rows writed in 00:01:14.863
700000 rows writed in 00:01:28.587
800000 rows writed in 00:01:41.588
900000 rows writed in 00:01:55.302
938496 rows writed in 00:02:01.205
Done, total:938496 , avg:7743 rows/s.
SQL> set fetchsize 10
SQL> unload select * from testobj >> a.txt;
Query executed in 00:00:00.030
100000 rows writed in 00:00:04.177
200000 rows writed in 00:00:08.233
300000 rows writed in 00:00:12.670
400000 rows writed in 00:00:16.967
500000 rows writed in 00:00:21.133
600000 rows writed in 00:00:25.350
700000 rows writed in 00:00:29.616
800000 rows writed in 00:00:33.401
900000 rows writed in 00:00:37.166
938496 rows writed in 00:00:38.622
Done, total:938496 , avg:24299 rows/s.
SQL> set fetchsize 20
SQL> unload select * from testobj >> a.txt;
Query executed in 00:00:00.020
100000 rows writed in 00:00:03.654
200000 rows writed in 00:00:07.409
300000 rows writed in 00:00:11.073
400000 rows writed in 00:00:14.858
500000 rows writed in 00:00:18.623
600000 rows writed in 00:00:22.347
700000 rows writed in 00:00:26.052
800000 rows writed in 00:00:29.415
900000 rows writed in 00:00:32.818
938496 rows writed in 00:00:34.164
Done, total:938496 , avg:27470 rows/s.
SQL> set fetchsize 50
SQL> unload select * from testobj >> a.txt;
Query executed in 00:00:00.030
100000 rows writed in 00:00:03.483
200000 rows writed in 00:00:07.128
300000 rows writed in 00:00:11.344
400000 rows writed in 00:00:15.049
500000 rows writed in 00:00:18.653
600000 rows writed in 00:00:22.849
700000 rows writed in 00:00:26.905
800000 rows writed in 00:00:30.258
900000 rows writed in 00:00:33.612
938496 rows writed in 00:00:34.927
Done, total:938496 , avg:26870 rows/s.
SQL>
[/FONT]
|
