Arraysize的設定以及對查詢效能的影響
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》
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26344835/viewspace-1277255/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 測試SQLPLUS的ARRAYSIZE對效能的影響SQL
- arraysize/TDU/SDU的設定對sql執行代價的影響SQL
- Arraysize 對consistent get的影響
- 【效能】Oracle表並行對邏輯遷移後查詢效能的影響Oracle並行
- MySQL：查詢欄位數量多少對查詢效率的影響MySql
- Sort_Buffer_Size 設定對伺服器效能的影響伺服器
- 影響Oracle標量子查詢效能的三個因素Oracle
- 無線安全設定對速度的影響
- 並行查詢對於響應時間的影響實驗並行
- dex最佳化對Arouter查詢路徑的影響
- Postgresql MVCC架構對從庫長查詢的影響SQLMVC架構
- SQL查詢結果集對注入的影響及利用SQL
- GPFS Persistent Reserve 的設定對Oracle RAC 的影響Oracle
- 淺談SQL Server中統計對於查詢的影響SQLServer
- 複合索引中前導列對sql查詢的影響索引SQL
- 軟體的效能設計（一）介面設計對軟體效能的影響 (轉)
- JAVA 異常對於效能的影響Java
- 遊戲盈利的商業模式，以及對遊戲設計造成的影響模式遊戲設計
- elasticsearch的Java Heap Size設定大於32G對效能的影響因素列舉ElasticsearchJava
- 查詢快取(query_cache)的影響快取
- 檢視Oracle回滾段的詳細情況，以及對效能的影響 -- 轉Oracle
- ORALCE 的AUDIT 以及開啟AUDIT對REDO 的影響
- DB2 HADR對效能的影響DB2
- InnoDB 隔離模式對 MySQL 效能的影響模式MySql
- 軟體的效能設計（二） 臨時物件對軟體效能的影響 (轉)物件
- oracle 索引升降序及排序條件 對查詢計劃的影響Oracle索引排序
- 效能與穩定：SuperFetch對Win7老電腦的影響Win7
- 軟體效能的設計（三）資料型別對軟體效能的影響 (轉)資料型別
- css屬性的選擇對動畫效能的影響CSS動畫
- 分支對程式碼效能的影響和優化優化
- JavaScript 事件對記憶體和效能的影響JavaScript事件記憶體
- mysql刪除和更新操作對效能的影響MySql
- 磁碟排序對Oracle資料庫效能的影響排序Oracle資料庫
- 硬體環境對系統效能的影響
- try catch 對程式碼執行的效能影響
- 音樂對程式設計的影響程式設計
- Java中的Exception拋異常對效能的影響 - BaeldungJavaException
- 【MV】group by查詢子句是否包含count(*)對物化檢視重新整理的影響