hwm的影响

HWM：
当需要进行全表扫描时，oracle会读取从第一个被分配的块到曾经包含数据的最高的那个块之间的每个块。这个最高块就叫做高水位线。
自动段存储管理器assm中，它会跟踪两条高水位线，一条高水位线线界定已经分配单从未保存数据的块，另一条则界定在过去某个时间点保存了数据的块。
因此很明确的说明，如果一张表上经常发生大量的删除操作，高水位线将比实际需要的位置更高。平均每个块里保存的记录数就会减少，检索记录的IO开销就会增加

==>建立测试表及插入数据
CREATE TABLE hwm_trsen (id INT PRIMARY KEY, DATA VARCHAR2(2000));

INSERT INTO hwm_trsen
            (id, DATA)
   SELECT ROWNUM, RPAD ('rownum', 1400, 'x')
         FROM DUAL
   CONNECT BY ROWNUM < 2000;

COMMIT ;

==>查询数据，查看执行计划
set autotrace on

SELECT /*  2000 rows */  MAX (DATA)
  FROM hwm_trsen;
Execution Plan
----------------------------------------------------------
Plan hash value: 3308937592
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |  1002 |   142   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |  1002 |            |          |
|   2 |   TABLE ACCESS FULL| HWM_TRSEN |  2204 |  2156K|   142   (0)| 00:00:02 |
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        448  consistent gets
          0  physical reads
          0  redo size
       1828  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
==>在原有基础上升高高水位线
INSERT INTO hwm_trsen
            (id, DATA)
   WITH maxpk AS
        (SELECT /*+ materialize */
                MAX (id) maxpk
           FROM hwm_trsen)
   SELECT     maxpk + ROWNUM x, RPAD ('rownum', 1400, '') y
         FROM maxpk
   CONNECT BY ROWNUM < 100000;
   
commit;

SELECT /* 102000 rows */ MAX (DATA)
  FROM hwm_trsen;

Execution Plan
----------------------------------------------------------
Plan hash value: 3308937592
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |  1002 |   179   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |  1002 |            |          |
|   2 |   TABLE ACCESS FULL| HWM_TRSEN |   132K|   127M|   179   (1)| 00:00:02 |
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        576  consistent gets
          0  physical reads
          0  redo size
       1828  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
==>不降高水位线删除数据
delete from hwm_trsen where id>2000;
commit;

==>发现查询消耗没有降低
SELECT /* After delete of 100000 */ MAX (DATA)
  FROM hwm_trsen;
Plan hash value: 3308937592
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |  1002 |   178   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |  1002 |            |          |
|   2 |   TABLE ACCESS FULL| HWM_TRSEN |  2666 |  2608K|   178   (0)| 00:00:02 |
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        576  consistent gets
          0  physical reads
          0  redo size
       1828  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

==>采用收缩空间降高水位线
alter table hwm_trsen enable row movement;
alter table hwm_trsen shrink space ;

 ==>发现性能降低
SELECT /* After shrink */ MAX (DATA)
  FROM hwm_trsen ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3308937592
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |  1002 |   115   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |  1002 |            |          |
|   2 |   TABLE ACCESS FULL| HWM_TRSEN |  1841 |  1801K|   115   (0)| 00:00:02 |
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        404  consistent gets
          0  physical reads
          0  redo size
       1828  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

INSERT INTO hwm_trsen
            (id, DATA)
   WITH maxpk AS
        (SELECT /*+ materialize */
                MAX (id) maxpk
           FROM hwm_trsen)
   SELECT     maxpk + ROWNUM x, RPAD ('rownum', 1400, 'x') y
         FROM maxpk
   CONNECT BY ROWNUM < 100000;

COMMIT ;

DELETE FROM hwm_trsen
      WHERE id > 1000 AND ROWNUM < 100000;

COMMIT ;

SELECT /* After delete of middle rows */ MAX (DATA)
  FROM hwm_trsen;
Execution Plan
----------------------------------------------------------
Plan hash value: 3308937592
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |  1002 |  6074   (1)| 00:01:07 |
|   1 |  SORT AGGREGATE    |           |     1 |  1002 |            |          |
|   2 |   TABLE ACCESS FULL| HWM_TRSEN |   106K|   102M|  6074   (1)| 00:01:07 |
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
      20772  consistent gets
          0  physical reads
          0  redo size
       1828  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

alter table hwm_trsen enable row movement;
alter table hwm_trsen shrink space ;

SELECT /* After 2nd shrink */ MAX (DATA)
  FROM hwm_trsen;
Execution Plan
----------------------------------------------------------
Plan hash value: 3308937592
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |  1002 |   115   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |  1002 |            |          |
|   2 |   TABLE ACCESS FULL| HWM_TRSEN |  1841 |  1801K|   115   (0)| 00:00:02 |
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        404  consistent gets
          0  physical reads
          0  redo size
       1828  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
分析数据：
 
操作    逻辑读       
插入1999    448       
插入101998    576       
删除id>2000    576       
删除id>2000并降高水位线    404       
删除id > 1000 AND ROWNUM < 100000    20772       
删除id > 1000 AND ROWNUM < 100000并降高水位线    404    

只有当一张表包含的记录远远小于它曾经拥有的记录时，才需要重建或收缩空间（shrink space)以重置高水位线。这将减少执行全表扫描时需要读取的数据块数目。