Arraysize是sql*plus中可以設定的一個引數，這個引數設定的意思表示，sql*plus一次可以從資料庫伺服器端獲取的記錄行數。

show arraysize

arraysize 15

可以看到，在SQL*plus中，預設設定是15。有效值是1-5000。按照《Oracle9i Database Performance Tuning Guide and Reference Release 2》的說法，當這個值的設定超過100後，對效能改進基本上不會有多少幫助了。

在OCI和OCCI中，都可以設定這個引數，

例如，在OCCI中設定這個引數的函式是：setPrefetchRowCount()。

做個簡單的實驗：

create table t as select * from all_objects;

set autotrace traceonly statistics;

set arraysize 2

select * from t;

統計資訊

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

288 recursive calls

0 db block gets

25300 consistent gets

682 physical reads

0 redo size

8135852 bytes sent via SQL*Net to client

273691 bytes received via SQL*Net from client

24848 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49693 rows processed

set arraysize 5

select * from t;

統計資訊

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

0 recursive calls

0 db block gets

10477 consistent gets

0 physical reads

0 redo size

6197846 bytes sent via SQL*Net to client

109703 bytes received via SQL*Net from client

9940 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49693 rows processed

set arraysize 10

select * from t;

統計資訊

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

0 recursive calls

0 db block gets

5588 consistent gets

0 physical reads

0 redo size

5551876 bytes sent via SQL*Net to client

55044 bytes received via SQL*Net from client

4971 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49693 rows processed

set arraysize 15

select * from t;

統計資訊

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

0 recursive calls

0 db block gets

3952 consistent gets

0 physical reads

0 redo size

5336466 bytes sent via SQL*Net to client

36817 bytes received via SQL*Net from client

3314 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49693 rows processed

set arraysize 100

select * from t;

統計資訊

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

0 recursive calls

0 db block gets

1178 consistent gets

0 physical reads

0 redo size

4970386 bytes sent via SQL*Net to client

5841 bytes received via SQL*Net from client

498 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49693 rows processed

set arraysize 500

select * from t;

統計資訊

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

0 recursive calls

0 db block gets

786 consistent gets

0 physical reads

0 redo size

4918776 bytes sent via SQL*Net to client

1474 bytes received via SQL*Net from client

101 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49693 rows processed

set arraysize 5000

select * from t;

統計資訊

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

0 recursive calls

0 db block gets

697 consistent gets

0 physical reads

0 redo size

4907076 bytes sent via SQL*Net to client

484 bytes received via SQL*Net from client

11 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49693 rows processed

set autotrace off

從統計資訊可以看出，查詢同樣的結果集，當arraysize增加時，主要有幾個方面的變化：

第一：consistent gets減少，表示查詢需要的邏輯I/O減少；

第二：資料請求在網路間的往返次數減少，一次傳送的資料多了，往返次數自然會減少；

第三：網路傳輸中的資料總流量減少，這主要是因為減少了網路傳輸中非結果集資料的開銷。

但並不能因此就說，arraysize設定為5000是最好的，因為這樣會導致客戶端和伺服器端使用更多的記憶體，並且，因為伺服器必須一次性準備好5000行記錄才能提供給終端，會導致終端一會在等待，一會又突然處理一大批資料，導致效能出現不穩定。

關於arraysize為什麼會減少邏輯I/O，可以這樣理解：當查詢獲取一個批次的記錄時，其中有可能一部分記錄處在同一個塊中，如果快取較少的結果集，Oracle下次不得不訪問同一個塊獲取某些記錄，如果快取足夠大，則在一次獲取中，就可以把同一個塊中符合條件的記錄都獲取到了，就避免重複訪問同一個塊，從而減少了邏輯I/O。

再做一個實驗，這個實驗來源於《Oracle9i&10g程式設計藝術》，從這個實驗可以看出，arraysize的增加對於查詢物理儲存無序的表的邏輯I/O影響不大。

先建立一個有序表：

create table colocated ( x int, y varchar2(80) );

begin

for i in 1 .. 100000

loop

insert into colocated(x,y)

values (i, rpad(dbms_random.random,75,'*') );

end loop;

end;

/

alter table colocated

add constraint colocated_pk

primary key(x);

begin

dbms_stats.gather_table_stats( user, 'COLOCATED', cascade=>true );

end;

/

再建立一個無序表：

create table disorganized

as

select x,y

from colocated

order by y;

alter table disorganized

add constraint disorganized_pk

primary key (x);

begin

dbms_stats.gather_table_stats( user, 'DISORGANIZED', cascade=>true );

end;

/

執行下列指令碼：

set arraysize 15

select * from colocated a15 where x between 20000 and 30000;

set arraysize 100

select * from colocated a100 where x between 20000 and 30000;

tkprof報告顯示：

（a15）

Rows Row Source Operation

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

10001 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=1452 pr=0 pw=0 time=100109 us)

10001 INDEX RANGE SCAN COLOCATED_PK (cr=689 pr=0 pw=0 time=40047 us)(object id 53215)

（a100）

Rows Row Source Operation

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

10001 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=344 pr=0 pw=0 time=90081 us)

10001 INDEX RANGE SCAN COLOCATED_PK (cr=124 pr=0 pw=0 time=30043 us)(object id 53215)

Arraysize為15時，對索引執行了689個邏輯I/O，對錶執行了763（1452-689）個邏輯I/O，arraysize為100時，對索引執行了124個邏輯I/O，對錶執行了220個邏輯I/O。這說明因為表有序，所以在一個塊中有較多需要的記錄，增加arraysize可以獲得良好效果。

set arraysize 15

select /*+ INDEX(a15 DISORGANIZED_PK) */* from disorganized a15 where x between 20000 and 30000;

set arraysize 100

select /*+ INDEX(a100 DISORGANIZED_PK) */* from disorganized a100 where x between 20000 and 30000;

tkprof報告顯示：

（a15）

Rows Row Source Operation

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

10001 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=10685 pr=0 pw=0 time=180134 us)

10001 INDEX RANGE SCAN DISORGANIZED_PK (cr=689 pr=0 pw=0 time=40336 us)(object id 53218)

（a100）

Rows Row Source Operation

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

10001 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=10119 pr=0 pw=0 time=160110 us)

10001 INDEX RANGE SCAN DISORGANIZED_PK (cr=124 pr=0 pw=0 time=30334 us)(object id 53218)

Arraysize為15時，對索引執行了689個邏輯I/O，對錶執行了9996（10685-689）個邏輯I/O，arraysize為100時，對索引執行了124個邏輯I/O，對錶執行了9995個邏輯I/O。減少的邏輯I/O基本上都來自於索引，索引本身是有序的。這說明因為表無序（表的記錄資訊無序），所以在一個塊中有較少條需要的記錄，增加arraysize效果不明顯。

參考文獻：

《Oracle高效設計》

《Oracle9i&10g程式設計藝術》

《Oracle9i Database Performance Tuning Guide and Reference Release 2》

《Oracle C++ Call Interface Programmer's Guide 10g Release 2》

Arraysize的設定以及對查詢效能的影響

相關文章