首页 > 代码库 > 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高水位分析