oracle中row prefetch测试

效果总结:row prefetch一方面可以减少网络传输次数,降低响应时间,另一方面oracle可以把多个单块读转变合并为一次多块读,减少逻辑读的次数。

一、sqlplus的测试

说明:sqlplus默认arraysize=2

SYS@orcl>create table t1(id number(*,0));
SYS@orcl>begin
2 for i in 1..1000 loop
3 insert into t1 values(i);
4 end loop;
5 end;
/

SYS@orcl>set autotrace traceonly;

SYS@orcl>select id from t1;

1000 rows selected.

Elapsed: 00:00:00.04
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
503 consistent gets
0 physical reads
0 redo size
74196 bytes sent via SQL*Net to client
 5874 bytes received via SQL*Net from client
501 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed

SYS@orcl>set arraysize 50

SYS@orcl>select id from t1;

1000 rows selected.
Elapsed: 00:00:00.01
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
11796 bytes sent via SQL*Net to client
  594 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed

二、jdbc的测试

说明:oracle的jdbc驱动默认fetchSize=10

public static void test_fetch_size(int rows) {

	long t1 = System.currentTimeMillis();

	Connection conn = DbUtil.getConnection();

	PreparedStatement ps = null;

	String sql;

	sql = "select level from dual connect by level <= 20000";

	try {

		ps = conn.prepareStatement(sql);

		ps.setFetchSize(rows);

		ResultSet rs;

		rs = ps.executeQuery();

		while (rs.next()) {

		}

		rs.close();

		ps.close();

	} catch (Exception e) {

		System.out.println(e.getMessage());

	}

	DbUtil.closeConnection(conn);

	long t2 = System.currentTimeMillis();

	System.out.println(t2 - t1);
}
fetch size = 10
1156
641
672
fetch size = 50
281
282
281
fetch size = 100
188
187
188
fetch size = 200
140
157
140

可以参考这篇文章:J2EE/Oracle性能调优实录

Comments are closed.