理清全表扫描和高水位，行预取的关系

以前写过一篇博客，调侃了一下全表扫，今天继续，感觉对全表扫的认识还是不够：侃侃全表扫

㈠ 全表扫描与HWM

在Table Access Full中，数据库引擎顺次读取表在HWM下的所有数据块
全表扫描可能是不够理想，特别是表包含大量空的或者接近空的块时
很明显，一个块必须先读才能知道它是否包含数据
当表的删除操作比插入操作多的时候，最容易导致表包含许多稀疏的数据块
所以，逻辑读依赖于块的数量而非行数
测试如下：

hr@ORCL> select /*+ full(t) */ * from t where object_id=51;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |   160   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    87 |   160   (2)| 00:00:02 |
--------------------------------------------------------------------------

 
 
hr@ORCL> set autot trace stat
hr@ORCL> select * from t where object_id=51;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        698  consistent gets
        335  physical reads
          0  redo size
       1199  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

hr@ORCL> delete t where object_id <> 51;

50321 rows deleted.

hr@ORCL> select * from t where object_id=51;--此时会对许多完全空的块进行无效的访问


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        698  consistent gets  ---查询所需的逻辑读并没有改变
          0  physical reads
          0  redo size
       1199  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--要降低高水位线，有必要对表进行重组
hr@ORCL> alter table t enable row movement; --行迁移必须先激活，因为重组过程中有新的rowid产生

Table altered.

hr@ORCL> alter table t shrink space;

Table altered.

hr@ORCL> select * from t where object_id=51;


Statistics
----------------------------------------------------------
        179  recursive calls
          0  db block gets
         26  consistent gets  --此时查询所需的逻辑读变成26个
          5  physical reads
          0  redo size
       1199  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

㈡ 全表扫描与行预取

全表扫描的逻辑读严重依赖于行预取的设置
下面Think和大家一起谈谈行预取对全表扫描的逻辑读的影响
简单而言，数据库引擎每访问一个数据块就产生一个逻辑读
对于全表扫描，存在两种极端：
如果行预取设定为1，则每个返回行相当于一个逻辑读
如果行预取设定比每个表的单块存储行数都大，逻辑读的数量就和表的块数量接近
测试：

hr@ORCL> ed
Wrote file afiedt.buf

  1  select num_rows,blocks,round(num_rows/blocks) as rows_per_block
  2    from user_tables
  3*  where table_name='T'
hr@ORCL> /

  NUM_ROWS     BLOCKS ROWS_PER_BLOCK
---------- ---------- --------------
     50323        712             71  --T表每个块平均有71行
     
hr@ORCL> set autot trace stat
hr@ORCL> set arraysize 2
hr@ORCL> select * from t;

50323 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      25532  consistent gets
          0  physical reads
          0  redo size
    8236771  bytes sent via SQL*Net to client
     277156  bytes received via SQL*Net from client
      25163  SQL*Net roundtrips to/from client     --[返回行的总数/arraysize]＋1
          0  sorts (memory)
          0  sorts (disk)
      50323  rows processed

hr@ORCL> set arraysize 3000
hr@ORCL> select * from t;

50323 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        714  consistent gets
          0  physical reads
          0  redo size
    4967955  bytes sent via SQL*Net to client
        561  bytes received via SQL*Net from client
         18  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50323  rows processed

当行预取设定为2，逻辑读的数量(25532)大概是行数(50323)的一半
当行预取设定比平均每个块中的行数(71)都要高，逻辑读的数量(714)和总块数(712)接近