arraysize/TDU/SDU的設定對sql執行代價的影響

Arraysize對consistent gets的影響
這是一個以前沒有留意過的引數，
建立一個測試表
SQL> begin
  2    for i in 1..10000 loop
  3      insert into justin values(i,substr('justin',1,mod(i,6)));
  4    end loop;
  5    commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed
 
SQL> select count(*) from justin;
 
  COUNT(*)
----------
     10000
現在透過autotrace traceonly檢視一下執行代價
SQL> select * from justin;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 863436603
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 10000 |   244K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JUSTIN | 10000 |   244K|     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        122  consistent gets
          0  physical reads
          0  redo size
     104228  bytes sent via SQL*Net to client
       1581  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
而現在的arraysize為100，更改為1000，再測試一下
SQL> set autotrace off
SQL> set arraysize 1000  
SQL> show arraysize
arraysize 1000
SQL> set autotrace traceonly
SQL> select * from justin;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 863436603
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 10000 |   244K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JUSTIN | 10000 |   244K|     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
      91088  bytes sent via SQL*Net to client
        591  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
可以看到在將arraysize調大為1000後,sql的各項指標都有所下降了，其中  SQL*Net roundtrips to/from client由原來的101下降為11，差不多有10倍的提升。
再嘗試將arraysize調整到最大值5000,
SQL> show arraysize
arraysize 5000
SQL> select * from justin;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 863436603
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 10000 |   244K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JUSTIN | 10000 |   244K|     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
     105600  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
為了方便對比，列一個表格
arraysize consistent gets SQL*Net roundtrips to/from client bytes received via SQL*Net from client bytes sent via SQL*Net to client
10 1021 1001 11481 271268
50 222 201 2681 104228
100 122 101 1581 104228
1000 33 11 591 91088
5000 25 3 503 105600
注: 為節省篇幅，arraysize=5/50的測試內容沒有貼出來
SQL*Net roundtrips to/from client差不多是隨arraysize的增長成呈線性下降

檢視一下arraysize的詳細定義

What is ARRAYSIZE?
The ARRAYSIZE defines the number of records of a result set Oracle puts into one network package.
文章中還提到兩個相關的概念,SDU/TDU
SDU(session data unit): specifies the size of the packets to send over the network
TDU(transfer data unit): the default packet size used within Oracle*Net
The default value for both SDU and TDU is 2,048 bytes and the maximum value is 32,767 bytes.  The TDU parameter should ideally be a multiple of the SDU parameter.
SDU最大值不能超過網路卡中的MTU值，oracle推薦其餘MTU等值；而TDU最好為SDU的倍數
現在修改listener.ora中的引數，將SDU/TDU調整為8192
    (SID_DESC =
      (ORACLE_HOME = /data/oracle/product/10.2/db1)
      (SID_NAME = justin)
      (SDU = 8192)
      (TDU = 8192)
)
重啟listener之後，重新測試，發現並沒有變化
因為現在網路卡的MTU為1500，而SDU受MTU影響，設定超過MTU之後便沒有效果
[oracle@justin admin]$  ifconfig -a
eth0      Link encap:Ethernet  HWaddr 18:A9:05:6E:19:78 
          inet addr:192.168.110.239  Bcast:192.168.110.255  Mask:255.255.255.0
          inet6 addr: fe80::1aa9:5ff:fe6e:1978/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:29374637 errors:0 dropped:0 overruns:0 frame.:0
          TX packets:30383657 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100
          RX bytes:5285580141 (4.9 GiB)  TX bytes:16506713289 (15.3 GiB)
          Memory:fbe60000-fbe80000

現在將SDU/TDU調小為256
    (SID_DESC =
      (ORACLE_HOME = /data/oracle/product/10.2/db1)
      (SID_NAME = justin)
      (SDU = 256)
      (TDU = 256)
)
重啟監聽器，
測試結果還是沒有任何改變，不知道是測試方法不當造成的。

從以上測試結果來看
arraysize的設定對sql的consistent gets影響是比較明顯的，可是也不能因此一味的擴大arraysize.
因為行從buffer cache中讀取出會先快取在PGA中，經PGA返回給客戶端；如果arraysize過大，會消耗更多的PGA，同時對網路傳輸也有一定的影響。
但並未看到SDU/TDU對consistent gets的影響