本文主要是介绍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)以重置高水位线。这将减少执行全表扫描时需要读取的数据块数目。
这篇关于hwm的影响的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
