高水位线和全表扫描

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

  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开销

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

执行计划中各字段各模块描述

      在SQL语句的执行计划中,包含很多字段项和很多模块,其不同字段代表了不同的含义且在不同的情形下某些字段、模块显示或不显示,下 面的描述给出了执行计划...

834
来自专栏乐沙弥的世界

SQLPlus 常用命令

3->LIST [m][*] [n](简写L)显示缓冲区的所有内容。* 当前行,m 第m行,n 第n行,m n 同时出现,m到n行

732
来自专栏杨建荣的学习笔记

关于drop user的cascade选项解惑(52天)

在数据库中,有时候需要删除用户,大多数时候都需要使用cascade选项,有些时候却不需要,想知道在这个简单的命令之后数据库倒底在干什么, 这时候给一些指定的操作...

3848
来自专栏大内老A

WCF版的PetShop之二:模块中的层次划分[提供源代码下载]

上一篇文章主要讨论的是PetShop的模块划分,在这一篇文章中我们来讨论在一个模块中如何进行层次划分。模块划分应该是基于功能的,一个模块可以看成是服务于某项功能...

25110
来自专栏乐沙弥的世界

Oracle ROWID

  ROWID 是一个类似于rownum的伪列,用于定位数据库中一条记录的一个相对唯一地址值。通常情况下,该值在该行数据插入到数据库表时即 被确定且唯一。而...

1514
来自专栏乐沙弥的世界

Heap size 80869K exceeds notification threshold (51200K)

      前阵子的alert日志获得了所需堆尺寸的大小超出指定阙值的提示,即Heap size 80869K exceeds notification thr...

983
来自专栏数据库新发现

如何使用USE_CONCAT提示

USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块. 最后合并所有查询块的结果,返回结果集给用户。

1312
来自专栏乐沙弥的世界

Oracle 控制文件(CONTROLFILE)

为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份

1162
来自专栏杨建荣的学习笔记

物化视图相关的性能改进 (r7笔记第58天)

今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个...

3405
来自专栏乐沙弥的世界

Oracle 闪回特性(Flashback Query、Flashback Table)

--==================================================

853

扫码关注云+社区

领取腾讯云代金券