573
Oracle sqlplus的arraysize
場景描述
開始我覺得這是個神奇的事情。u1用戶的t1表共有大約1230塊，但是對該表進行全表掃描的時候（select *），竟需要consistent gets接近7000次。
因為從正常的邏輯來理解，block從物理設備讀取到內存，然後cpu從內存讀取數據進行計算/過濾，這是一個完整的過程。如果內存中有緩存的block，那麼就可以不用經曆物理設備讀取block的過程，這麼看來，consistent gets應該是不大於physical reads的才對。
arraysize參數
這裏先簡單描述下這個客戶端參數arrzysize。
SQL> show arraysize;
arraysize 15
arraysize是sqlplus的一個特定參數，最大值為5000（java程序中叫做fetchsize）。表示讀取一次buffer，最多返回給用戶的行的數量。
呃，那麼需要注意一點，當一個block的行數大於arraysize參數時，需要反複讀取同一個buffer
場景模擬
接下來對這個場景進行模擬，分析下arraysize參數對於SQL執行的影響。
使用u1用戶登錄
SQL> connect u1/u1;
Connected.
SQL> show user;
USER is "U1"
查看當前的arraysize值
SQL> show arraysize
arraysize 15
執行一個大型的查詢語句
SQL> select * from t1;
86335 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86335 | 8262K| 344 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| T1 | 86335 | 8262K| 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6907 consistent gets
1233 physical reads
0 redo size
9926753 bytes sent via SQL*Net to client
63828 bytes received via SQL*Net from client
5757 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86335 rows processed
這裏我們發現內存讀的次數遠遠大於物理讀的次數。
然後增大一下arraysize的值，再執行一遍
SQL> set arraysize 100;
SQL> /
86335 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86335 | 8262K| 344 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| T1 | 86335 | 8262K| 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2089 consistent gets
1233 physical reads
0 redo size
9016841 bytes sent via SQL*Net to client
10016 bytes received via SQL*Net from client
865 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86335 rows processed
發現內存讀次數明顯減少，開始接近物理讀的次數
然後將arraysize設置為5000
SQL> set arraysize 5000;
SQL> /
86335 rows selected.
...
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1253 consistent gets
1233 physical reads
0 redo size
8859485 bytes sent via SQL*Net to client
710 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86335 rows processed
內存讀和物理讀的次數變得十分接近，這樣似乎會讓我們看起來覺得是“正常”的了。
給個解釋。。
這裏我們先看另一個指標的變化，roundtrips。
5757 SQL*Net roundtrips to/from client
865 SQL*Net roundtrips to/from client
19 SQL*Net roundtrips to/from client
用戶一次請求，服務器一次數據返回叫做一次roundtrip。
結合起來，很好解釋這個現象了。arraysize的值遠遠小於block中rows，因此每次隻能讀取一個block（或者說buffer）的一部分數據，而對於這個全表查詢，需要每個block的所有rows，那麼同一個bolck就需要被反複讀取，帶來的結果就是，讀取的次數就增加了。由於讀取的最小單位就是block，因此我們發現在一個block需要反複讀取的時候，一次consistent gets變成了n次。
在不斷增加arraysize大小的過程中，讀取的次數逐漸減少，consistent gets與physical reads越來越接近。
最後更新：2017-09-17 13:33:03