高水位线和全表扫描

您的位置：
门户
>> 文章精选
>> 软件开发专栏
>> 数据库
>> 查看资讯

发表于：2013-5-31 09:00

作者：RobinsonCheng 来源：51Testing软件测试网采编

软件开发

数据库

Oracle

　　高水位线好比水库中储水的水位线，用于描述数据库中段的扩展方式。高水位线对全表扫描方式有着至关重要的影响。当使用delete 操作表记录时，高水位线并不会下降，随之导致的是全表扫描的实际开销并没有任何减少。本文给出高水位线的描述，如何降低高水位线，以及高水位线对全表扫描的影响。

　　一、何谓高水位线

　　如前所述，类似于水库中储水的水位线。只不过在数据库中用于描述段的扩展方式。可以将数据段或索引段等想象为一个从左到右依次排开的一系列块。当这些块中未填充任何数据时，高水位线位于块的最左端（底端），随着记录的不断增加，新块不断地被填充并使用，高水位线随之向右移动。高水位线之上为未格式化的数据块。

　　删除（delete）操作之后，高水位线之下的块处于空闲状态，但高水位线并不随之下降，直到重建，截断或收缩表段。全表扫描会扫描高水位线之下的所有块，包括空闲数据块（执行了delete操作）。

　　低高水位线

　　是在使用ASSM时的一个概念。即使用ASSM时除了高水位线之外，还包括一个低高水位线。低高水位线一定是位于高水位线之下。当段使用MSSM管理方式时只有一种情况即只存在一个高水位线。

　　使用MMSM时，当HWM升高时，Oracle立即格式化所有块且有效，并可以安全读取。仅当第一次使用时完成格式化，便于安全读取数据。

　　使用ASSM时，当HWM升高时，Oracle并不会立即格式化所有块。仅当第一次使用时完成格式化，便于安全读取数据。

　　使用低高水位线可以减少当全面扫描表段时，低高水位线与高水位线之间不安全块的检查数量。即低高水位线之下的块不再检查。

　　二、演示高水位线与全表扫描

SQL> create table t -->创建测试表
2 as
3 select rownum as id,
4 round(dbms_random.normal*1000) AS val1,
5 dbms_random.string('p',250) AS pad
6 from dual
7 connect by level <=10000;

Table created.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true); -->收集统计信息

SQL> @Tab_Stat -->从dba_tab_statistics中获得表对象的统计信息，此时无empty_blocks的信息
Enter value for input_table_name: t
Enter value for input_owner: scott

/**************************************************/
/* Author: Robinson Cheng                         */
/* Blog:  http://blog.csdn.net/robinson_0612     */
/* MSN:   robinson_0612@hotmail.com              */
/* QQ:     645746311                              */
/**************************************************/

SQL> analyze table t compute statistics; -->执行analyze

SQL> @Tab_Stat -->此时的empty_blocks值为125
Enter value for input_table_name: t
Enter value for input_owner: scott

SQL> col segment_name format a15
SQL> select segment_name,segment_type,blocks,extents from dba_segments -->查看表段上的块的信息
2 where segment_name='T' and owner='SCOTT';

SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS -->此数据字典中记录的块数为512块(包含了已使用块与空闲块)
--------------- ------------------ ---------- ----------
T TABLE 512 19

SQL> set autotrace traceonly; -->开启autotrace
SQL> select count(*) from t; -->此时SQL语句的执行计划为全表扫描(执行计划中部分信息被省略)

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    86   (0)| 00:00:02 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 10000 |    86   (0)| 00:00:02 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        375 consistent gets          -->consistent gets的值为375
          0 physical reads

SQL> set autotrace off;
SQL> delete from t where rownum<=9900; -->删除大多数的记录，删除后剩余记录值为100

9900 rows deleted.
SQL> commit;

SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true); -->收集统计信息

SQL> analyze table t compute statistics; -->收集统计信息

SQL> @Tab_Stat -->此时对象上的统计信息无任何变化，即高水位线没有发生任何变化
Enter value for input_table_name: t
Enter value for input_owner: scott

NUM_ROWS       BLKS    EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA
---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---
       100        387        125       7921          0         262                  0 03-NOV-11 NO

SQL> set autotrace traceonly
SQL> select count(*) from t;     -->SQL的执行计划中预估的值准确，为100行

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    86   (0)| 00:00:02 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   100 |    86   (0)| 00:00:02 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        375 consistent gets   -->consistent gets的值仍然为375，并没有下降
          0 physical reads

SQL> set autotrace off;
SQL> alter table t enable row movement; -->启用row movement

SQL> alter table t shrink space cascade; --> 实施shrink space

SQL> alter table t disable row movement;

SQL> exec dbms_stats.gather_table_stats('SCOTT','T');

SQL> analyze table t compute statistics;

SQL> @Tab_Stat                           -->此时对象上的统计信息已发生变化，已使用的块为4块，空闲块为4块
Enter value for input_table_name: t
Enter value for input_owner: scott
NUM_ROWS       BLKS    EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA
---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---
       100          4          4       7921          0         259                 25 03-NOV-11 NO

SQL> set autotrace traceonly
SQL> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   100 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          6 consistent gets    -->表段收缩之后，consistent gets由375下降为6
          0 physical reads

SQL> truncate table t; -->使用表截断技术(turncate table)

Table truncated.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T'); -->收集统计信息

PL/SQL procedure successfully completed.

SQL> select count(*) from t; -->此时执行计划中的rows变为1

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          3 consistent gets   -->consistent gets的值降为3
          0 physical reads

　　三、总结

　　1、高水线直接决定了全表扫描所需要的I/O开销

　　2、delete操作不会降低高水位线，高水位线之下的所有块依然被扫描

　　3、使用truncate 会重置高水位线到0位

　　4、定期使用alter table tab_name shrink space cascade 有效减少该对象上的I/O开销

《2023软件测试行业现状调查报告》独家发布~

搜索风云榜

测试技术了解

2023测试行业调查报告

挣点稿费

AI与软件测试

文章资料精选