高水位线和全表扫描

雨点打透心脏的1/2处 2022-05-15 13:21 244阅读 0赞

高水位线好比水库中储水的水位线，用于描述数据库中段的扩展方式。高水位线对全表扫描方式有着至关重要的影响。当使用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)][shrink space]  
[dbms\_xplan之display\_cursor函数的使用][dbms_xplan_display_cursor]  
[dbms\_xplan之display函数的使用][dbms_xplan_display]  
[执行计划中各字段各模块描述][Link 1]  
[Oracle 绑定变量窥探][Oracle]  
[Oracle 自适应共享游标][Oracle 1]  
[Oracle ROWID][]

[shrink space]: http://blog.csdn.net/robinson_0612/article/details/6630673
[dbms_xplan_display_cursor]: http://blog.csdn.net/robinson_0612/article/details/6866925
[dbms_xplan_display]: http://blog.csdn.net/robinson_0612/article/details/6866870
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/6860007
[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6923627
[Oracle 1]: http://blog.csdn.net/robinson_0612/article/details/
[Oracle ROWID]: http://blog.csdn.net/robinson_0612/article/details/6931886