測試SQLPLUS的ARRAYSIZE對效能的影響

arraysize定義了一次返回到SQLPLUS客戶端的行數，當掃描了arraysize 行後，停止掃描，返回資料，然後繼續掃描。

 這個過程就是統計資訊中的SQL*Net roundtrips to/from client。

因為arraysize 預設是15行，那麼就有一個問題，因為我們一個block中的記錄數一般都會超過15行，所以如果按照15行掃描一次，那麼每次掃描要多掃描一個資料塊，一個資料塊也可能就會重複掃描多次。

 重複的掃描會增加consistent gets 和 physical reads。 增加physical reads，這個很好理解，掃描的越多，物理讀的可能性就越大。

consistent gets，這個是從undo裡讀的數量，Oracle 為了保證資料的一致性，當一個查詢很長，在查詢之後，資料塊被修改，還未提交，再次查詢時候，Oracle根據Undo 來構建CR塊，這個CR塊，可以理解成資料塊在之前某個時間的狀態。 這樣通過查詢出來的資料就是一致的。

那麼如果重複掃描的塊越多，需要構建的CR塊就會越多，這樣讀Undo 的機會就會越多，consistent gets 就會越多。

 如果資料每次傳到客戶端有中斷，那麼這些資料會重新掃描，這樣也就增加邏輯讀，所以調整arraysize可以減少傳的次數，減少邏輯讀。

 所以通過上面的說明，arraysize 引數如果過低，會影響如physical reads，consistent gets 還有SQL*Net roundtrips to/from client次數。  ---本段引自DAVE部落格。

實驗結論：實驗用表是由dba_objects;建立。通過設定arraysize為1、15、200，可以通過最後的彙總表格得出將arraysize設定為200，可以得到更好的查詢效能。

具體表現在：SQL語句執行時間大幅減少，通過Oracle Net從客戶端收到的位元組總數大幅減少，SQL * Net傳送和從客戶端接收的位元組總數大幅減少--減幅比例接近arraysize尺寸的比例。

所以在使用SQLPLUS客戶端查取大資料、SPOOL輸出時，可以考慮將arraysize設定的大一點，提高效能。永久設定此引數可以在$ORACLE_HOME/sqlplus/admin/glogin.sql中寫入set arraysize 15 這樣。

1，使用SQLPLUS的ARRAYSIZE預設值15來進行測試

BYS@bys1>create tabele test2 as select * from dba_objects;
BYS@bys1>alter system flush shared_pool;
System altered.
BYS@bys1>alter system flush buffer_cache;
System altered.

BYS@bys1>set arraysize 15    因為我已經更改過，所以手動再改為預設的15

此設定只在當前SESSION中有用，如果需要永久設定，可以在$ORACLE_HOME/sqlplus/admin/glogin.sql中寫入set arraysize 15 這樣。

BYS@bys1>set autotrace traceonly stat
BYS@bys1>select * from test2;

72465 rows selected.

Elapsed: 00:00:02.12

Statistics
----------------------------------------------------------
        606  recursive calls
          0  db block gets
       5882  consistent gets
       1052  physical reads
          0  redo size
    8036433  bytes sent via SQL*Net to client
      53549  bytes received via SQL*Net from client
       4832  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      72465  rows processed

彙總：執行時間：02.12秒，606次遞迴呼叫，5882 一致讀，1052 物理讀，8036433 bytes傳送，53549 bytes接收，4832次往返

2.將SQLPLUS的ARRAYSIZE值設定為200

BYS@bys1>alter system flush buffer_cache;
System altered.
BYS@bys1>alter system flush shared_pool;
System altered.
BYS@bys1>set arraysize 200
BYS@bys1>set autotrace traceonly stat   只顯示統計資訊不顯示輸出結果
BYS@bys1>select * from test2; 
72465 rows selected.

Elapsed: 00:00:00.62
Statistics
----------------------------------------------------------
        606  recursive calls
          0  db block gets
       1491  consistent gets
       1049  physical reads
          0  redo size
    7455593  bytes sent via SQL*Net to client
       4401  bytes received via SQL*Net from client
        364  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      72465  rows processed                    彙總：執行時間：00.62秒，606次遞迴呼叫，1491一致讀，1049物理讀，7455593 bytes傳送，4401 bytes接收，364次往返

3.將SQLPLUS的ARRAYSIZE值設定為1

BYS@bys1>alter system flush buffer_cache;
System altered.
BYS@bys1>alter system flush shared_pool;
System altered.
BYS@bys1>set autotrace traceonly stat
BYS@bys1>set arraysize 1
BYS@bys1>select * from test2;
72465 rows selected.
Elapsed: 00:00:08.71
Statistics
----------------------------------------------------------
        606  recursive calls
          0  db block gets
      36830  consistent gets
       1049  physical reads
          0  redo size
   12118659  bytes sent via SQL*Net to client
     398971  bytes received via SQL*Net from client
      36234  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      72465  rows processed

彙總：執行時間：08.71秒，606次遞迴呼叫，36830一致讀，1049物理讀，12118659 bytes傳送，398971bytes接收，36234次往返

三種引數的效能彙總表格如下：

ARRAYSIZE	執行時間/秒	遞迴呼叫	一致讀	物理讀	客戶端接收	客戶端傳送	Oracle網路訊息傳送和從客戶端接收總數
1	8.17	606	36830	1049	12118659	398971	36234
15	2.12	606	5882	1052	8036433	53549	4832
200	0.62	606	1491	1049	7455593	4401	364
倍數
15與1=15各項效能對比（倍）	8.17/2.12=3.85		36830/5882=6.2		1.5	7.4	7.5
15與200=13各項效能對比-倍	3.4		3.9		1.07	12.6	13.2