首页 > 代码库 > PLSQL_性能优化系列16_Oracle High Water Level高水位分析
PLSQL_性能优化系列16_Oracle High Water Level高水位分析
2014-10-04 BaoXinjian
一、摘要
高水位线好比水库中储水的水位线，用于描述数据库中段的扩展方式。高水位线对全表扫描方式有着至关重要的影响。
当使用delete 操作表记录时，高水位线并不会下降，随之导致的是全表扫描的实际开销并没有任何减少。
本文给出高水位线的描述，如何降低高水位线，以及高水位线对全表扫描的影响。
1. 何谓高水位线
如前所述，类似于水库中储水的水位线。只不过在数据库中用于描述段的扩展方式。
可以将数据段或索引段等想象为一个从左到右依次排开的一系列块。当这些块中未填充任何数据时，高水位线位于块的最左端(底端)
随着记录的不断增加，新块不断地被填充并使用，高水位线随之向右移动。高水位线之上为未格式化的数据块。
删除(delete)操作之后，高水位线之下的块处于空闲状态，但高水位线并不随之下降，直到重建，截断或收缩表段。
全表扫描会扫描高水位线之下的所有块，包括空闲数据块(执行了delete操作)。
2. 低高水位线
是在使用ASSM时的一个概念。即使用ASSM时除了高水位线之外，还包括一个低高水位线。低高水位线一定是位于高水位线之下。
当段使用MSSM管理方式时只有一种情况即只存在一个高水位线。
使用MMSM时，当HWM升高时，Oracle立即格式化所有块且有效，并可以安全读取。仅当第一次使用时完成格式化，便于安全读取数据。
使用ASSM时，当HWM升高时，Oracle并不会立即格式化所有块。仅当第一次使用时完成格式化，便于安全读取数据。
使用低高水位线可以减少当全面扫描表段时，低高水位线与高水位线之间不安全块的检查数量。即低高水位线之下的块不再检查。
二、案例 - Delete / SHRINK SPACE CASCADE / Truncate Table 对水位线的影响
1. 创建测试表和资料，并分析
Step1. 创建表
CREATE TABLE sh.bxj_high_water_levelAS SELECT ROWNUM AS id, ROUND (DBMS_RANDOM.normal * 1000) AS val1, DBMS_RANDOM.string (‘p‘, 250) AS pad FROM DUAL CONNECT BY LEVEL <= 10000;
Step2. 收集表的统计信息
BEGIN DBMS_STATS.gather_table_stats (‘SH‘, ‘BXJ_HIGH_WATER_LEVEL‘, cascade => TRUE);END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS;
Step3. 表的统计信息和Block信息
SQL> select count(*) from sh.bxj_high_water_level;Execution Plan----------------------------------------------------------Plan hash value: 4214873579-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 107 (0)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| BXJ_HIGH_WATER_LEVEL | 10000 | 107 (0)| 00:00:02 |-----------------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 375 consistent gets 0 physical reads 0 redo size 422 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
2. Delete Record 对水位线的影响
Step1. 删除表中记录
DELETE FROM sh.bxj_high_water_level WHERE ROWNUM <= 9900;
Step2. 收集表的统计信息
BEGIN DBMS_STATS.gather_table_stats (‘SH‘, ‘BXJ_HIGH_WATER_LEVEL‘, cascade => TRUE);END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS;
Step3. 表的统计信息和Block信息
SQL> select count(*) from sh.bxj_high_water_level;Execution Plan----------------------------------------------------------Plan hash value: 4214873579-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 107 (0)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| BXJ_HIGH_WATER_LEVEL | 100 | 107 (0)| 00:00:02 |-----------------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 375 consistent gets 0 physical reads 0 redo size 422 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
3. SHRINK SPACE CASCADE 对水位线的影响
Step1。合并控件
ALTER TABLE sh.bxj_high_water_level ENABLE ROW MOVEMENT; ALTER TABLE sh.bxj_high_water_level SHRINK SPACE CASCADE; ALTER TABLE sh.bxj_high_water_level DISABLE ROW MOVEMENT;
Step2. 收集表的统计信息
BEGIN DBMS_STATS.gather_table_stats (‘SH‘, ‘BXJ_HIGH_WATER_LEVEL‘, cascade => TRUE);END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS;
Step3. 表的统计信息和Block信息
SQL> select count(*) from sh.bxj_high_water_level;Execution Plan----------------------------------------------------------Plan hash value: 4214873579-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| BXJ_HIGH_WATER_LEVEL | 100 | 3 (0)| 00:00:01 |-----------------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 422 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
4. Truncate Table 对水位线的影响
Step1. Truncate 表
TRUNCATE TABLE sh.bxj_high_water_level
Step2. 收集表的统计信息
BEGIN DBMS_STATS.gather_table_stats (‘SH‘, ‘BXJ_HIGH_WATER_LEVEL‘, cascade => TRUE);END; ANALYZE TABLE bxj_water_level COMPUTE STATISTICS;
Step3. 表的统计信息和Block信息
SQL> select count(*) from sh.bxj_high_water_level;Execution Plan----------------------------------------------------------Plan hash value: 4214873579-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| BXJ_HIGH_WATER_LEVEL | 1 | 2 (0)| 00:00:01 |-----------------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 421 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
六、案例 - 总结
(1). 高水线直接决定了全表扫描所需要的I/O开销
(2). delete操作不会降低高水位线，高水位线之下的所有块依然被扫描
(3). 使用truncate 会重置高水位线到0位
(4). 定期使用alter table tab_name shrink space cascade 有效减少该对象上的I/O开销
参考：了沙弥 http://blog.csdn.net/leshami/article/details/6949179
PLSQL_性能优化系列16_Oracle High Water Level高水位分析