加大arraysize的值減少邏輯讀提高sql返回速度
首先介紹下arraysize這個引數，該引數只能在當前會話設定。不是 oracle 端設定的，而是sqlplus設定的，所以我們不能使用設定初始化引數alter system set來改變它，
而是使用set命令，該引數的含義就是sqlplus裡預抓取的行數，預設值是15行，也就是伺服器程式每次從buffer cache中複製15行到PGA中，從PGA
在傳送給客戶端。與之相關的另一個引數就是SDU(session data unit)，預抓取在PGA所佔的記憶體大小和SDU大小相關，預設是8K.
SQL> show arraysize
arraysize 15
SQL> create table jason as select * from dba_objects;
Table created.
SQL>
SQL>
SQL> select count(1) from dba_objects;
   COUNT(1)
———-
      50042
SQL>
SQL>
SQL> set autot trace
SQL>
SQL>
SQL>
SQL> select * from jason;
50042 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1258222954
—————————————————————————
| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
—————————————————————————
| 0 | SELECT STATEMENT  |    | 44426 |  7679K| 155 (2)| 00:00:02 |
| 1 |  TABLE ACCESS FULL| JASON | 44426 |  7679K| 155 (2)| 00:00:02 |
—————————————————————————
Note
—–
   – dynamic sampling used for this statement
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
         3992  consistent gets
            0  physical reads
            0  redo size
      2544199  bytes sent via SQL*Net to client
      37188  bytes received via SQL*Net from client
         3338  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
      50042  rows processed
SQL> set arraysize 400
SQL>
SQL>
SQL>
SQL> /
SQL> /
50042 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1258222954
—————————————————————————
| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
—————————————————————————
| 0 | SELECT STATEMENT  |    | 44426 |  7679K| 155 (2)| 00:00:02 |
| 1 |  TABLE ACCESS FULL| JASON | 44426 |  7679K| 155 (2)| 00:00:02 |
—————————————————————————
Note
—–
   – dynamic sampling used for this statement
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
         817  consistent gets
            0  physical reads
            0  redo size
      1956586  bytes sent via SQL*Net to client
         1867  bytes received via SQL*Net from client
         127  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
      50042  rows processed
可以看到當我們把arraysize從預設的15調整到400後consistent gets邏輯讀從原來的3992下降到817，bytes received via SQL*Net from client相關的值也有明顯的下降。
arraysize引數的設定對大資料的範圍比如全表掃效能提高還是比較大的，所以在網路頻寬允許的情況下適當調大arraysize和估算SDU的值，對減少
邏輯讀，減少網路互動性，從而介紹CPU的消耗幫助還是比較大的。

適當調大arraysize減少邏輯讀

相關文章