Oracle arraysize 和 fetch size 参数与性能优化说明 - 数据库编程

a value of zero, then the hint is ignored: the JDBC driver returns one row at a time. The default value is zero.

如下连接是一个Jdbc 中配置Fetch size的示例。

http://www.idevelopment.info/data/Programming/java/jdbc/FetchSize.java

二. 相关测试

每个block 中row的条数和row的大小也有关系，row 内容越多，那么block 中的row就会少。

每个block里有多少条记录，可以通过rowid 来判断。

关于Oracle rowid说明，参考我的Blog

Oracle Rowid 介绍

rowid 格式如下：OOOOOOFFFBBBBBBRRR, 其中：

（1）OOOOOO

The data object number identifies the segment (data object AAAPec in Example 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.

（2）FFF

The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).

（3）BBBBBB

The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.

（4）RRR

The row number identifies the row in the block (row AAA in Example 12-1).

DAVE@anqing2(rac2)> create table dave as select * from sys.ta where rownum<10000;

Table created.

-- 查看table 占用blocks 数量

DAVE@anqing2(rac2)> select owner,extents,segment_name,blocks from dba_segments where segment_name=''DAVE'' and owner=''DAVE'';

OWNER EXTENTS SEGMENT_NAME BLOCKS

---------- ---------- -------------------- ----------

DAVE 3 DAVE 24

从这个数据算一个，1000行数据24个数据块。平均下来每个数据块里有417条记录. 但事情情况可能不是这样.

--表结构很简单

DAVE@anqing2(rac2)> desc dave;

Name Null Type

----------------------------------------- -------- ----------------------------

ID NUMBER

NAME VARCHAR2(10)

-- 查看rowid格式

DAVE@anqing2(rac2)> select rowid from dave where rownum=1;

ROWID

------------------

AAANXzAAHAAAAAMAAA

--查看每个数据块中有多少记录：

/* Formatted on 2011/7/1 14:59:56 (QP5 v5.163.1008.3004) */

SELECT prerid, COUNT (rid) rid

FROM (SELECT SUBSTR (ROWID, 1, 15) prerid, ROWID rid FROM dave)

GROUP BY prerid;

DAVE@anqing2(rac2)> select prerid,count(rid) rid from (select substr(rowid,1,15) prerid,rowid rid from dave) group by prerid;

PRERID RID

------------------------------ ----------

AAANXzAAHAAAAAa 517

AAANXzAAHAAAAAf 517

AAANXzAAHAAAAAP 517

AAANXzAAHAAAAAU 517

AAANXzAAHAAAAAW 517

AAANXzAAHAAAAAX 517

AAANXzAAHAAAAAM 524

AAANXzAAHAAAAAO 517

AAANXzAAHAAAAAQ 517

AAANXzAAHAAAAAS 517

AAANXzAAHAAAAAY 517

AAANXzAAHAAAAAR 517

AAANXzAAHAAAAAg 169

AAANXzAAHAAAAAN 517

AAANXzAAHAAAAAT 517

AAANXzAAHAAAAAV 517

AAANXzAAHAAAAAb 517

AAANXzAAHAAAAAe 517

AAANXzAAHAAAAAc 517

AAANXzAAHAAAAAd 517

20 rows selected.

-- 这里只有20行，即实际只使用了20个数据块，每个数据块的记录如上查询结果，因为表的记录很简单，所以每个块中的记录很多。

Oracle arraysize 和 fetch size 参数与性能优化说明(二)