HWM Introduction

在ORACLE Database中，执行对表的删除操作不会降低该表的高水位线。而全表扫描将始终读取一个段(extent)中所有低于高水位线标记的块。如果在执行删除操作后不降低高水位线标记，则将导致查询语句的性能低下。rebuild, truncate, shrink,move 等操作会降低高水位。

实验

创建表

[oracle@dev ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 17 14:25:48 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn dev
Enter password: 
Connected.
SQL> create table hwm (id number);

Table created.

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

no rows selected


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

TABLE_NAME			 			NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
HWM

疑问：在查询dba_segments时没有找到数据，延时？？？

插入数据

SQL> declare
  2      i number;
  3  begin
  4      for i in 1..10000 loop
  5          insert into hwm values(i);
  6      end loop;
  7      commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.


SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

SEGMENT_NAME	   SEGMENT_TYPE	  	  	  BLOCKS
------------------ ------------------ ----------
HWM			       TABLE			  		  24


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

TABLE_NAME		   NUM_ROWS       BLOCKS EMPTY_BLOCKS
------------------ ---------- ---------- ------------
HWM

此时表HWM已经占有了数据，24个数据块。其他统计信息为空。这些信息需要做统计分析之后才会有。

搜集统计信息

SQL> exec dbms_stats.gather_table_stats('DEV','HWM');

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

SEGMENT_NAME	   SEGMENT_TYPE	  	  	  BLOCKS
------------------ ------------------ ----------
HWM			       	TABLE			  		  24

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

TABLE_NAME		   	 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------ ---------- ---------- ------------
HWM				    	10000	  	  20			0

使用dbms_stats收集统计信息后，显示表HWM有1000行，占用20个数据块。但EMPTY_BLOCKS为空，该列需要在ANALYZE之后才会有数据。

分析表

SQL> analyze table hwm compute statistics;

Table analyzed.


SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

SEGMENT_NAME	   SEGMENT_TYPE	  	  	  BLOCKS
------------------ ------------------ ----------
HWM			       TABLE			  		  24

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

TABLE_NAME		   	 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------ ---------- ---------- ------------
HWM				    	10000	  	  20			4

删除数据

SQL> delete from hwm;

10000 rows deleted.

SQL> commit;

Commit complete.

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

SEGMENT_NAME	   SEGMENT_TYPE	  	  	  BLOCKS
------------------ ------------------ ----------
HWM			       TABLE			  		  24

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

TABLE_NAME		   	 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------ ---------- ---------- ------------
HWM				        10000	      20			4

删除数据后再分析表

SQL> analyze table hwm compute statistics;

Table analyzed.

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

SEGMENT_NAME	   SEGMENT_TYPE	  	  	  BLOCKS
------------------ ------------------ ----------
HWM			       TABLE			  	      24

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

TABLE_NAME		   	 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------ ---------- ---------- ------------
HWM							0	  	  20			4

delete数据，不会降低高水位。

truncate表

SQL> truncate table hwm;

Table truncated.

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

SEGMENT_NAME	   SEGMENT_TYPE	  	  	  BLOCKS
------------------ ------------------ ----------
HWM			       TABLE			   		   8

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

TABLE_NAME		   	 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------ ---------- ---------- ------------
HWM						    0	  	  20			4

runcate后再次收集统计信息

SQL> exec dbms_stats.gather_table_stats('DEV','HWM');

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

SEGMENT_NAME	   SEGMENT_TYPE	  	  	  BLOCKS
------------------ ------------------ ----------
HWM			       TABLE			   		   8


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

TABLE_NAME		   	 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------ ---------- ---------- ------------
HWM						    0	       0			4

再次分析表

SQL> analyze table hwm compute statistics;

Table analyzed.

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name = 'HWM';

SEGMENT_NAME	   SEGMENT_TYPE	  	  	  BLOCKS
------------------ ------------------ ----------
HWM			       TABLE			   		   8

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

TABLE_NAME		   	 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------ ---------- ---------- ------------
HWM						    0	   	   0		    8

truncate后，高水位降低。

blog comments powered by Disqus

Published

17 April 2013

Github

Rendered

Raw