通过设置SQLPLUS ARRAYSIZE(行预取)加快SQL返回速度

本文主要是介绍通过设置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

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