通過設置SQLPLUS ARRAYSIZE(行預取)加快SQL返回速度

有時候你可能會用SQLPLUS spool 表的數據，那么怎么加快spool速度呢？SQLPLUS中有個行預取的選項

SQLPLUS中 arraysize默認為15

SQL> show arraysize
arraysize 15

它表示從Oracle服務器端一次只傳遞15行記錄到客戶端(SQLPLUS)，當然了JDBC,WEBLOGIC也有行預取，具體自己Google
-
 

舉個例子：

SQL> select * from test where owner='ADWU_OPTIMA_AP11';

773 rows selected.

Elapsed: 00:00:30.95

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   593 |   134K|   882   (3)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TEST |   593 |   134K|   882   (3)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='ADWU_OPTIMA_AP11')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2976  consistent gets
          0  physical reads
          0  redo size
      50484  bytes sent via SQL*Net to client
        597  bytes received via SQL*Net from client
         53  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        773  rows processed

SQL> set arraysize 5000
SQL> select * from test where owner='ADWU_OPTIMA_AP11';

773 rows selected.

Elapsed: 00:00:16.06

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   593 |   134K|   882   (3)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TEST |   593 |   134K|   882   (3)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='ADWU_OPTIMA_AP11')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2927  consistent gets
          0  physical reads
          0  redo size
      47800  bytes sent via SQL*Net to client
        241  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        773  rows processed

當設置 arraysize 之后，SQLPLUS 客戶端與數據庫Server端交互次數明顯減少，這就是為什么返回773行數據第二次比第一次快1倍了，同時也可以看到，第二次邏輯讀比第一次低了，那說明設置行預取會影響邏輯讀。

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]