高水位线和全表扫描 - 军军小站|张军博客

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

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

高水位线和全表扫描

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义