高水位线对于全表扫描的影响

最新推荐文章于 2022-05-01 11:52:06 发布

原创最新推荐文章于 2022-05-01 11:52:06 发布 · 849 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#全表扫描 #HWM

Performance Tuning 专栏收录该内容

21 篇文章

订阅专栏

本文探讨了全表扫描的概念及其在Oracle数据库中的工作原理，特别是在ASMM环境下。通过实验展示了空闲数据块如何显著增加全表扫描的成本，并介绍了通过收缩表来优化HWM的方法。

简单来说，全表扫描就是扫描表中的所有数据块，包括空数据块。在ASMM的环境下，全表扫描通常是扫描到低高水位线(Low HWM),然后通过位图扫描低高水位线到高水位线之间格式化的数据块。在ASMM环境下，当空间不足的时候，oracle就会分配新的区间给表，然后HWM随即就会提高，但是数据库并没有马上格式化这些新分配的数据块，而是等到需要使用这些数据块的时候才格式化这些数据块。为此，low HWM到HWM之间很有可能存在非格式化的数据块。如下图：

为此，全表扫描的效率主要受到空闲数据块以及结果的返回率的影响。对于存在大量空闲数据块的全表扫描成本将会很高，因为其同时读取了大量的空数据块。要解决这个问题，可以通过重建表、收缩表等方法来降低HWM。下面的实验是验证空数据块对于全表扫描性能的影响，以及采用收缩表的方式降低HWM.

----通过PCTFREE制造大量数据块
SQL> create table t1(
  2  id int,
  3  time date)
  4  pctfree 80
  5  pctused 10;

表已创建。

SQL> begin
  2  for i in 1..100000 loop
  3  insert into t1
  4  select i,sysdate+i from dual;
  5  end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> delete from t1 where id>10;

已删除99990行。

SQL> create index ind on t1(id);

索引已创建。

SQL> analyze table t1 compute statistics;---分析表，获取统计数据；

表已分析。

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name ='T1';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1                                     10       1133           18

SQL> select index_name,clustering_factor from user_indexes where index_name ='IND';

INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
IND                                            1
----由上面两个SELECT语句可以知道，10行数据聚集在一个数据块里，其他的都没有数据，也就是说水位线很高。

SQL> set autotrace traceonly explain;

-----因此，下面的全表扫描耗费了巨大的成本；
SQL> select count(*) from t1;

执行计划
----------------------------------------------------------
Plan hash value: 3724264953

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

----而，若使用索引则扫描成本则非常低；
SQL> select count(*) from t1 where id>=1;

执行计划
----------------------------------------------------------
Plan hash value: 1947457635

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     2 |            |          |
|*  2 |   INDEX RANGE SCAN| IND  |    10 |    20 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=1)

----从上面的两个性能差异，说明了全表扫描会很大程度上受到空闲数据块的影响；
SQL>