高水位线和全表扫描

   高水位线好比水库中储水的水位线,用于描述数据库中段的扩展方式。高水位线对全表扫描方式有着至关重要的影响。当使用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

  NUM_ROWS       BLKS    EM_BLKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY  STA
---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---
     10000        387          0          0          0         259                 26 03-NOV-11 NO

/**************************************************/
/* Author: Robinson Cheng                         */
/* Blog:   http://blog.csdn.net/robinson_0612     */
/* MSN:    [email protected]              */
/* 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

  NUM_ROWS       BLKS    EM_BLKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY  STA
---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---
     10000        387        125        920          0         262                 26 03-NOV-11 NO

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开销
  
四、延伸参考
  收缩表段(shrink space) 
  dbms_xplan之display_cursor函数的使用 
  dbms_xplan之display函数的使用 
  执行计划中各字段各模块描述 
  Oracle 绑定变量窥探 
  Oracle 自适应共享游标  
  Oracle ROWID 

  

时间: 2011-11-08

高水位线和全表扫描的相关文章

Oracle 全表扫描及其执行计划(full table scan)

    全表扫描是Oracle访问数据库表是较为常见的访问方式之一.很多朋友一看到SQL语句执行计划中的全表扫描,就要考虑对其进行修理一番.全表扫描的存在,的确存在可能优化的余地.但事实上很多时候全表扫描也并非是最低效的,完全要看不同的情形与场合,任一方式都是有利有弊的,也就是具体情况要具体分析.本文描述了什么是全表扫描以及何时发生全表扫描,何时全表扫描才低效.  本文涉及到的相关链接:     高水位线和全表扫描      启用 AUTOTRACE 功能     Oracle 测试常用表BIG

浅析Oracle全表扫描下的逻辑读

T1表全表扫描产生逻辑读的分析 做个实验给你演示一下:以表t1为例,对段t1做dump 1.t1表就一条数据 [email protected]> select * from t1;      ID NAME ---------- ----------       1 AAAAA 2.找t1段的段头块 [email protected]> select  header_file,header_block from dba_segments where segment_name='T1' and owner='GYJ'; HEADER

库表字符集不一致导致的全表扫描问题

背景: 当数据库的建库字符集和表不一样时,在库下针对表创建存储过程可能导致全表扫描 如下例: drop database if exists xx1; drop database if exists xx2; create database xx1 character set utf8; create database xx2 character set gbk;   然后分别在xx1 和 xx2下执行: CREATE TABLE t1 ( `col1` varchar(10) NOT NULL

MongoDB Primary 为何持续出现 oplog 全表扫描?

线上某 MongoDB 复制集实例(包含 Primary.Secondary.Hidden 3个节点 ),Primary 节点突然 IOPS 很高,调查后发现,其中 Hidden 处于 RECOVERING 状态,同时 Priamry 上持续有一全表扫描 oplog 的操作,正是这个 oplog 的 COLLSCAN 导致IO很高. 2017-10-23T17:48:01.845+0800 I COMMAND [conn8766752] query local.oplog.rs query: {

SQL SERVER中关于OR会导致索引扫描或全表扫描的浅析

在SQL SERVER的查询语句中使用OR是否会导致不走索引查找(Index Seek)或索引失效(堆表走全表扫描 (Table Scan).聚集索引表走聚集索引扫描(Clustered Index Scan))呢?是否所有情况都是如此?又该如何优化呢? 下面我们通过一些简单的例子来分析理解这些现象.下面的实验环境为SQL SERVER 2008,如果在不同版本有所区别,欢迎指正.   堆表单索引 首先我们构建我们测试需要实验环境,具体情况如下所示: DROP TABLE TEST    CRE

SQL中WHERE变量IS NULL条件导致全表扫描问题的解决方法_MsSql

复制代码 代码如下: SET @SQL = 'SELECT * FROM Comment with(nolock) WHERE 1=1    And (@ProjectIds Is Null or ProjectId = @ProjectIds)    And (@Scores is null or Score [email protected])' 印象中记得,以前在做Oracle开发时,这种写法是会导致全表扫描的,用不上索引,不知道Sql Server里是否也是一样呢,于是做一个简单的测试1.建立测试用的表结

大幅提升MySQL中InnoDB的全表扫描速度的方法_Mysql

 在 InnoDB中更加快速的全表扫描 一般来讲,大多数应用查询的时候都会用索引,查找很少的几行数据(主键查找或百行内的查询),但有时候我们需要全表查询.典型的全表扫描就是逻辑备份  (mysqldump) 和 online schema changes( 注:在线上对大表 schema 的操作,也是 facebook 的一个开源项目) (SELECT ... INTO OUTFILE).  在 Facebook我们用 mysqldump 来备份数据库. 正如你所知MySql提供两种备份方式,提

MySQL查询优化:LIMIT 1避免全表扫描提高查询效率_Mysql

在某些情况下,如果明知道查询结果只有一个,SQL语句中使用LIMIT 1会提高查询效率. 例如下面的用户表(主键id,邮箱,密码): 复制代码 代码如下: create table t_user( id int primary key auto_increment, email varchar(255), password varchar(255) ); 每个用户的email是唯一的,如果用户使用email作为用户名登陆的话,就需要查询出email对应的一条记录. SELECT * FROM t

LINQ to SQL:处理char(1)字段的方式会引起全表扫描问题_MsSql

  如果表中的字段类型为 char(1) 时,Linq to SQL生成char (System.Char)的属性,如下图 表定义 生成的实体 2. 如果要查询LineCode=='A'的记录,可以这样定义Linq查询语句 var test1 = from p in db.ProductLines             where p.LineCode =='A'             select p; 生成的SQL语句是这样的 SELECT [t0].[LineCode], [t0].[