ORACLE空间管理实验5：块管理之ASSM下高水位的影响

高水位概念： 所有的oracle段(segments，在此，为了理解方便，建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限，我们把这个上限称为high water mark或HWM。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。HWM原则

高水位概念：
所有的oracle段(segments，在此，为了理解方便，建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限，我们把这个上限称为"high water mark"或HWM。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。HWM原则上HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这个特点，使HWM很象一个水库的历史最高水位，这也就是HWM的原始含义。
这个概念百度下一大把，可以参考：
http://www.blogjava.net/decode360/archive/2009/07/14/287767.html
http://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html

高水位与低高水位：低高水位与高水位之间存在的数据块的状态可能是未格式化或格式的。低高水位以下的是格式化了的，可以被使用。
HWM对数据库的操作有如下影响：
1.全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。
3.即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。
2.插入时，默认只能插入到高水位以下的数据块，这在高并发时可能引起热块从而导致性能问题。
下面就通过实验来验证一下:

全表扫描通常要读出直到低高水位HWM标记的所有的属于该表数据库块，即使该表中没有任何数据--DELETE来测试。TRUNCATE会释放。

解决方法有：expdp/impdp，shrink：需要在ASSM且表开启行移动。MOVE不支持在线，或者CRTEATE TABLE A AS，再删除原表，将新表改为原名。。

#################################################

1.  DELETE不会改变高水位,但是删除后对表做SHRINK操作可以回收空间，降低高水位

BYS@ bys3>create table test12 as select * from dba_objects;
Table created.
BYS@ bys3>insert into test12 select * from dba_objects;
17558 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
35116 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
70232 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
140464 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>alter system checkpoint;  ---要做检查点
System altered.
BYS@ bys3>select header_block,header_file from dba_segments where segment_name='TEST12' and owner='BYS';
HEADER_BLOCK HEADER_FILE
------------ -----------
         922           4
BYS@ bys3>select sum(blocks) from dba_extents where segment_name='TEST12' and owner='BYS';
SUM(BLOCKS)
-----------
       3840
BYS@ bys3>select count(extent_id) from dba_extents where segment_name='TEST12' and owner='BYS';
COUNT(EXTENT_ID)
----------------
              45
多执行几次，取后面的平均值：
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
280960 rows selected.
Elapsed: 00:00:05.70
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      23504  consistent gets
       3710  physical reads
          0  redo size
   29190599  bytes sent via SQL*Net to client
     206449  bytes received via SQL*Net from client
      18732  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     280960  rows processed

BYS@ bys3>alter system dump datafile 4 block 922;
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_25994.trc
###############DUMP段头，查看高水位信息：Highwater::  0x01001180 --4480号块
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 45     #blocks: 3840  
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x01002100  ext#: 44     blk#: 128    ext size: 128      --高水位的DBA：0x01002100，8448号块
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 3784  
  mapblk  0x00000000  offset: 44    
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01002080  ext#: 43     blk#: 128    ext size: 128     --低高水位 0x01002080
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 3712  
  mapblk  0x00000000  offset: 43    
  Level 1 BMB for High HWM block: 0x01002081
  Level 1 BMB for Low HWM block: 0x01002001
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x01000399
  Last Level 1 BMB:  0x01002081
  Last Level II BMB:  0x01000399
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 45   obj#: 23303  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x01000398  length: 8     
%……………………
   0x01002080  length: 128    --最后一个L1 DBA是8320号块，管理了128个块，高水位是8448号块，刚好是最后一个L1的最后一个数据块。
 
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x01000398 Data dba:  0x0100039b
……………………
   Extent 44    :  L1 dba:  0x01002080 Data dba:  0x01002082   高水位应该就是这个L1管理的最后一个块：0x01002082 -8322号块，

  --------------------------------------------------------
 
   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x01000399

对表做分析后

BYS@ bys3>analyze table test12 compute statistics;
Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
  PCT_FREE   PCT_USED     BLOCKS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ---------- ----------- ----------
        10                  3784          93          0
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
280960 rows selected.
Elapsed: 00:00:05.46
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      22204  consistent gets
          0  physical reads
          0  redo size
   29190599  bytes sent via SQL*Net to client
     206449  bytes received via SQL*Net from client
      18732  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     280960  rows processed
DUMP信息与未做表分析时相同，不贴了。
 
######################

DELETE删除表中全部数据

BYS@ bys3>delete test12;
280944 rows deleted.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>select header_block,header_file from dba_segments where segment_name='TEST12' and owner='BYS';
HEADER_BLOCK HEADER_FILE
------------ -----------
         922           4
BYS@ bys3>select sum(blocks) from dba_extents where segment_name='TEST12' and owner='BYS';
SUM(BLOCKS)
-----------
       3840
BYS@ bys3>select count(extent_id) from dba_extents where segment_name='TEST12' and owner='BYS';
COUNT(EXTENT_ID)
----------------

              45

BYS@ bys3>analyze table test12 compute statistics;
Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
  PCT_FREE   PCT_USED     BLOCKS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ---------- ----------- ----------
        10                  3784           0          0

BYS@ bys3>alter system dump datafile 4 block 922;
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_26054.trc
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
no rows selected
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3721  consistent gets
          2  physical reads
          0  redo size
       1183  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
###############DELETE删除后的DUMP信息和上一个未删除时的没啥区别，不贴了。

DELETE删除后对表做SHRINK：--可以回收空间，降低高水位

BYS@ bys3>alter table test12 enable row movement;
Table altered.
BYS@ bys3>alter table test12 shrink space;  --SHRINK与MOVE，详见：shrink合并数据块--解决数据块碎片问题
Table altered.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
  PCT_FREE   PCT_USED     BLOCKS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ---------- ----------- ----------
        10                  3784           0          0
BYS@ bys3>analyze table test12 compute statistics;  对表做分析后，tabs里的BLOCKS信息才会变。
Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
  PCT_FREE   PCT_USED     BLOCKS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ---------- ----------- ----------
        10                     1           0          0
BYS@ bys3>alter system dump datafile 4 block 922;
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_26432.trc        

#########################DUMP信息如下：--可以看到空间已经回收了。高水位也已经下降
 Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x0100039c  ext#: 0      blk#: 4      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 1     
  mapblk  0x00000000  offset: 0     
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x0100039c  ext#: 0      blk#: 4      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 1     
  mapblk  0x00000000  offset: 0     
  Level 1 BMB for High HWM block: 0x01000398
  Level 1 BMB for Low HWM block: 0x01000398
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x01000399
  Last Level 1 BMB:  0x01000398
  Last Level II BMB:  0x01000399
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 23303  flag: 0x10000000
  Inc # 1
  Extent Map
  -----------------------------------------------------------------
   0x01000398  length: 8     
 
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x01000398 Data dba:  0x0100039b
  --------------------------------------------------------
 
   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x01000399  
End dump data blocks tsn: 4 file#: 4 minblk 922 maxblk 922
##################################

2.TRUNCATE操作直接就可以回收空间，改变高水位--但是如果非分区表，没有DELETE应用场景多。

create table test13 as select * from dba_objects;
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>alter system dump datafile 4 block 466;
System altered.
#############DUMP文件信息：
Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 17     #blocks: 256   
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x010011f5  ext#: 16     blk#: 117    ext size: 128   
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 245   
  mapblk  0x00000000  offset: 16    
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x010011f5  ext#: 16     blk#: 117    ext size: 128   
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 245   
  mapblk  0x00000000  offset: 16    
  Level 1 BMB for High HWM block: 0x01001181
  Level 1 BMB for Low HWM block: 0x01001181
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x010001d1
  Last Level 1 BMB:  0x01001181
  Last Level II BMB:  0x010001d1
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 17   obj#: 23300  flag: 0x10000000
  Inc # 0
  Extent Map

################

做TRUNCATE操作然后DUMP段头

BYS@ bys3>truncate table test13;
Table truncated.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>alter system dump datafile 4 block 466;
System altered.
#########################

Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x010001d3  ext#: 0      blk#: 3      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 0     
      Disk Lock:: Locked by xid:  0x0002.019.00001354
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x010001d3  ext#: 0      blk#: 3      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 0     
  Level 1 BMB for High HWM block: 0x010001d0
  Level 1 BMB for Low HWM block: 0x010001d0
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x010001d1
  Last Level 1 BMB:  0x010001d0
  Last Level II BMB:  0x010001d1
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 23304  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x010001d0  length: 8     
 
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x010001d0 Data dba:  0x010001d3
  --------------------------------------------------------
   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x010001d1