行欲取導致sqlplus中trace的consistent gets遠大於實際塊數

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 10 15:09:11 2011Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> SELECT BYTES/1024/1024 SIZE_MB FROM DBA_SEGMENTS
  2  WHERE SEGMENT_NAME='SYS_USER' AND WNER='JESSE';

   SIZE_MB
----------
        56

SQL> alter system flush buffer_cache;

System altered.

SQL> set autotrace traceonly;
SQL> select * from JESSE.SYS_USER;

253348 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4234589240

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   253K|    50M|  1192   (1)| 00:00:17 |
|   1 |  TABLE ACCESS FULL| SYS_USER |   253K|    50M|  1192   (1)| 00:00:17 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      20272  consistent gets
       3524  physical reads
          0  redo size
   31335655  bytes sent via SQL*Net to client
     186271  bytes received via SQL*Net from client
      16891  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     253348  rows processed

資料庫的db_block_size=16k，表SYS_USER的包含資料塊數=表總大小/db_block_size=56MB/16KB=3584同上面的physical reads基本相等，但consistent gets的數量和我預計的3584相差太大，在這個庫從來不會對SYS_USER表資料做修改，在一致性讀的時候也不會獲取回滾段的資料。

原來遇到過這樣問題沒仔細研究，剛查詢資料發現Tom大師曾經對此做過解答：

sqlplus的欲取行預設是arraysize 15，一次只能查15行，行數/每讀取行數=253348/15(sqlplus中查詢完起碼要讀這麼多資料庫塊，即便是有重復的)+3584=20473和上面consistent gets的數量基本相等了。

再驗證一下，將arraysize盡量設大，看看有變化沒有：

SQL> SET ARRAYSIZE 50000;
SP2-0267: arraysize option 50000 out of range (1 through 5000)
SQL> SET ARRAYSIZE 5000;
SQL> select * from JESSE.SYS_USER;

253348 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4234589240

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   253K|    50M|  1192   (1)| 00:00:17 |
|   1 |  TABLE ACCESS FULL| SYS_USER |   253K|    50M|  1192   (1)| 00:00:17 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3582  consistent gets
          0  physical reads
          0  redo size
   28254118  bytes sent via SQL*Net to client
       1042  bytes received via SQL*Net from client
         52  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     253348  rows processed

果不其然，看來以後用sqlplus做trace要小心啦。。