Arraysize的設定以及對查詢效能的影響

Arraysize是sql*plus中可以設定的一個參數，這個參數設定的意思表示，sql*plus一次可以從資料庫伺服器端擷取的記錄行數。

show arraysize

arraysize 15

可以看到，在SQL*plus中，預設設定是15。有效值是1-5000。按照《Oracle9i Database Performance Tuning Guide and Reference Release 2》的說法，當這個值的設定超過100後，對效能改進基本上不會有多少協助了。

在OCI和OCCI中，都可以設定這個參數，

例如，在OCCI中設定這個參數的函數是：setPrefetchRowCount()。

 

做個簡單的實驗：

create table t as select * from all_objects;

set autotrace traceonly statistics;

 

set arraysize 2

select * from t;

 

統計資訊

----------------------------------------------------------

        288  recursive calls

          0  db block gets

      25300  consistent gets

        682  physical reads

          0  redo size

    8135852  bytes sent via SQL*Net to client

     273691  bytes received via SQL*Net from client

      24848  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      49693  rows processed

 

set arraysize 5

select * from t;

 

統計資訊

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      10477  consistent gets

          0  physical reads

          0  redo size

    6197846  bytes sent via SQL*Net to client

     109703  bytes received via SQL*Net from client

       9940  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      49693  rows processed

 

set arraysize 10

select * from t;

 

統計資訊

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       5588  consistent gets

          0  physical reads

          0  redo size

    5551876  bytes sent via SQL*Net to client

      55044  bytes received via SQL*Net from client

       4971  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      49693  rows processed

 

set arraysize 15

select * from t;

 

統計資訊

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       3952  consistent gets

          0  physical reads

          0  redo size

    5336466  bytes sent via SQL*Net to client

      36817  bytes received via SQL*Net from client

       3314  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      49693  rows processed

 

set arraysize 100

select * from t;

 

統計資訊

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1178  consistent gets

          0  physical reads

          0  redo size

    4970386  bytes sent via SQL*Net to client

       5841  bytes received via SQL*Net from client

        498  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      49693  rows processed

 

set arraysize 500

select * from t;

 

統計資訊

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        786  consistent gets

          0  physical reads

          0  redo size

    4918776  bytes sent via SQL*Net to client

       1474  bytes received via SQL*Net from client

        101  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      49693  rows processed

 

set arraysize 5000

select * from t;

 

統計資訊

---------------------------------------------------------

          0  recursive calls

          0  db block gets

        697  consistent gets

          0  physical reads

          0  redo size

    4907076  bytes sent via SQL*Net to client

        484  bytes received via SQL*Net from client

         11