Oracle Study之－－Oracle High Water Mark

谁践踏了优雅 2022-08-21 15:56 147阅读 0赞

**Oracle Study之－－Oracle High Water Mark**

**       在手动段空间管理（Manual Segment Space Management）中，段中只有一个HWM，但是在Oracle9iRelease1才添加的自动段空间管理（Automatic Segment Space Management）中，又有了一个低HWM的概念出来。为什么有了HWM还又有一个低HWM呢，这个是因为自动段空间管理的特性造成的。在手段段空间管理中，当数据插入以后，如果是插入到新的数据块中，数据块就会被自动格式化等待数据访问。而在自动段空间管理中，数据插入到新的数据块以后，数据块并没有被格式化，而是在第一次在第一次访问这个数据块的时候才格式化这个块。所以我们又需要一条水位线，用来标示已经被格式化的块。这条水位线就叫做低HWM。**

**       未格式化，意思就是这个块，已经是属于这个段了，但是还保留着原来的样子没动  
       格式化就是把块中的数据清除掉，并把块头改为这个对象的  
       MSSM表空间中的段，只有一个高水位，高水位下的块都是格式化了的  
       但是ASSM表空间中的段，有两个高水位：低高水位和高高水位  
       低高水位下的块全部是格式化了的  
       但是低高水位和高高水位之间的块，则可能是格式化了的，也可能是没有**

**      全表扫描时，通常都是读至低高水位线，然后根据位图去读低高与高高之间格式化过的块，避开未格式化的块**

案例分析：

**1、分析表**

**09:46:07 SCOTT@ test1>analyze table emp2 compute statistics**  
09:48:00   2  ;  
Table analyzed.

2、查看table中的hwm  
**09:48:01 SCOTT@ test1>select table\_name,num\_rows,blocks,empty\_blocks from user\_tables  
09:48:47   2   where table\_name='EMP2';**  
TABLE\_NAME                       NUM\_ROWS     BLOCKS EMPTY\_BLOCKS  
\------------------------------ ---------- ---------- ------------  
EMP2                                71680        499           13  
  
**3、分析segment header block**

**10:09:18 SYS@ test1>select segment\_name,header\_block from dba\_segments  
10:09:32   2   where segment\_name='EMP2';**

SEGMENT\_NAME         HEADER\_BLOCK  
\-------------------- ------------  
EMP2                         178

**10:07:34 SYS@ test1>alter system dump datafile 4 block 178;**  
System altered.  
  
\[oracle@rh5 ~\]$ ls -lt /u01/app/oracle/diag/rdbms/test1/test1/trace|more  
total 3272  
\-rw-r----- 1 oracle oinstall   6083 May  6 10:07 test1\_ora\_3212.trc  
  
**4、查看header block中信息**  
**\[oracle@rh5 ~\]$ more /u01/app/oracle/diag/rdbms/test1/test1/trace/test1\_ora\_3212.trc**  
Start dump data blocks tsn: 4 file\#:4 minblk 178 maxblk 178  
Block dump from cache:  
Dump of buffer cache at level 4 for tsn=4, rdba=16777394  
Block dump from disk:  
buffer tsn: 4 rdba: 0x010000b2 (4/178)  
scn: 0x0000.000a2333 seq: 0x13 flg: 0x04 tail: 0x23332313  
frmt: 0x02 chkval: 0x980b type: 0x23=PAGETABLE SEGMENT HEADER  
Hex dump of block: st=0, typ\_found=1  
Dump of memory from 0x00918200 to 0x0091A200  
918200 0000A223 010000B2 000A2333 04130000  \[\#.......3\#......\]  
918210 0000980B 00000000 00000000 00000000  \[................\]  
918220 00000000 00000001 00000008 00000A9C  \[................\]  
918230 00000001 00000080 00000080 01000300  \[................\]  
918240 00000000 00000001 00000000 000001F3  \[................\]  
918250 001C0014 00000021 00000001 00000011  \[....!...........\]  
918260 00000080 00000080 01000280 00000000  \[................\]  
918270 00000011 00000000 00000180 01000201  \[................\]  
918280 01000281 00000000 00000000 00000000  \[................\]  
918290 00000000 00000000 00000000 00000000  \[................\]  
        Repeat 3 times  
9182D0 00000001 00002000 00000000 00001434  \[..... ......4...\]  
9182E0 00000000 010000B1 00000001 01000281  \[................\]  
9182F0 010000B1 00000000 00000000 00000000  \[................\]  
918300 00000000 00000000 00000001 00000000  \[................\]  
918310 000032E0 12000000 010000B0 00000008  \[.2..............\]  
918320 00000000 00000000 00000000 00000000  \[................\]  
        Repeat 152 times  
918CB0 010000B0 010000B3 00000000 00000000  \[................\]  
918CC0 00000000 00000000 00000000 00000000  \[................\]  
        Repeat 151 times  
919640 00000000 00000000 010000B1 00000000  \[................\]  
919650 00000000 00000000 00000000 00000000  \[................\]  
        Repeat 185 times  
91A1F0 00000000 00000000 00000000 23332313  \[.............\#3\#\]  
  Extent Control Header  
  -----------------------------------------------------------------  
  Extent Header:: spare1: 0      spare2: 0      \#extents: 1      \#blocks: 8       
                  last map  0x00000000  \#maps: 0      offset: 2716    
      **Highwater::  0x01000300  ext\#: 1      blk\#: 128    ext size: 128   **  
  \#blocks in seg. hdr's freelists: 0       
**  \#blocks below: 499   **  
  mapblk  0x00000000  offset: 1       
      Disk Lock:: Locked by xid:  0x0014.01c.00000021  
  --------------------------------------------------------  
**  Low HighWater Mark : **  
  **    Highwater::  0x01000280  ext\#: 17     blk\#: 128    ext size: 128**     
  \#blocks in seg. hdr's freelists: 0       
**  \#blocks below: 384   **  
  mapblk  0x00000000  offset: 17      
  Level 1 BMB for High HWM block: 0x01000281  
  Level 1 BMB for Low HWM block: 0x01000201  
  --------------------------------------------------------  
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0        
  L2 Array start offset:  0x00001434  
  First Level 3 BMB:  0x00000000  
  L2 Hint for inserts:  0x010000b1  
  Last Level 1 BMB:  0x01000281  
  Last Level II BMB:  0x010000b1  
  Last Level III BMB:  0x00000000  
     Map Header:: next  0x00000000  \#extents: 1    obj\#: 13024  flag: 0x12000000  
  Inc \# 0   
  Extent Map  
  -----------------------------------------------------------------  
   0x010000b0  length: 8       
    
  Auxillary Map  
  --------------------------------------------------------  
   Extent 0     :  L1 dba:  0x010000b0 Data dba:  0x010000b3  
  --------------------------------------------------------  
  
**5、验证hwm的变化**  
  
**09:48:55 SCOTT@ test1>delete from emp2 where rownum < 70001;**  
70000 rows deleted.  
09:55:20 SCOTT@ test1>commit;  
Commit complete.  
**09:55:22 SCOTT@ test1>alter table emp2 move;**  
Table altered.  
**09:56:45 SCOTT@ test1>select table\_name,num\_rows,blocks,empty\_blocks from user\_tables  
09:56:58   2   where table\_name='EMP2';**  
TABLE\_NAME                       NUM\_ROWS     BLOCKS EMPTY\_BLOCKS  
\------------------------------ ---------- ---------- ------------  
EMP2                                 1680         13            3  
  
10:09:02 SYS@ test1>col segment\_name for a20  
**10:09:18 SYS@ test1>select segment\_name,header\_block from dba\_segments  
10:09:32   2   where segment\_name='EMP2';**  
SEGMENT\_NAME         HEADER\_BLOCK  
\-------------------- ------------  
EMP2                          362  
  
**10:12:58 SYS@ test1>alter system dump datafile 4 block 362;**  
System altered.  
  
\[oracle@rh5 ~\]$ ls -lt /u01/app/oracle/diag/rdbms/test1/test1/trace|more  
total 3284  
\-rw-r----- 1 oracle oinstall   4514 May  6 10:13 test1\_ora\_3300.trc  
**\[oracle@rh5 ~\]$ more /u01/app/oracle/diag/rdbms/test1/test1/trace/test1\_ora\_3300.trc**  
Trace file /u01/app/oracle/diag/rdbms/test1/test1/trace/test1\_ora\_3300.trc  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  
ORACLE\_HOME = /u01/app/oracle/product/11.2.0/db\_1  
System name:    Linux  
Node name:      rh5  
Release:        2.6.18-194.el5  
Version:        \#1 SMP Tue Mar 16 21:52:43 EDT 2010  
Machine:        i686  
Instance name: test1  
Redo thread mounted by this instance: 1  
Oracle process number: 25  
Unix process pid: 3300, image: oracle@rh5 (TNS V1-V3)  
\*\*\* 2016-05-06 10:13:07.142  
\*\*\* SESSION ID:(31.2) 2016-05-06 10:13:07.142  
\*\*\* CLIENT ID:() 2016-05-06 10:13:07.142  
\*\*\* SERVICE NAME:(SYS$USERS) 2016-05-06 10:13:07.142  
\*\*\* MODULE NAME:(sqlplus@rh5 (TNS V1-V3)) 2016-05-06 10:13:07.142  
\*\*\* ACTION NAME:() 2016-05-06 10:13:07.142  
   
Start dump data blocks tsn: 4 file\#:4 minblk 362 maxblk 362  
Block dump from cache:  
Dump of buffer cache at level 4 for tsn=4, rdba=16777578  
Block dump from disk:  
buffer tsn: 4 rdba: 0x0100016a (4/362)  
scn: 0x0000.000a2350 seq: 0x01 flg: 0x04 tail: 0x23502301  
frmt: 0x02 chkval: 0x9801 type: 0x23=PAGETABLE SEGMENT HEADER  
Hex dump of block: st=0, typ\_found=1  
Dump of memory from 0x05DCB600 to 0x05DCD600  
5DCB600 0000A223 0100016A 000A2350 04010000  \[\#...j...P\#......\]  
5DCB610 00009801 00000000 00000000 00000000  \[................\]  
5DCB620 00000000 00000002 00000010 00000A9C  \[................\]  
5DCB630 00000001 00000005 00000008 0100017D  \[............\}...\]  
5DCB640 00000000 00000001 00000000 0000000D  \[................\]  
5DCB650 00000000 00000000 00000000 00000001  \[................\]  
5DCB660 00000005 00000008 0100017D 00000000  \[........\}.......\]  
5DCB670 00000001 00000000 0000000D 01000168  \[............h...\]  
5DCB680 01000168 00000000 00000000 00000000  \[h...............\]  
5DCB690 00000000 00000000 00000000 00000000  \[................\]  
        Repeat 3 times  
5DCB6D0 00000001 00002000 00000000 00001434  \[..... ......4...\]  
5DCB6E0 00000000 01000169 00000001 01000168  \[....i.......h...\]  
5DCB6F0 01000169 00000000 00000000 00000000  \[i...............\]  
5DCB700 00000000 00000000 00000002 00000000  \[................\]  
5DCB710 000033F8 10000000 01000168 00000008  \[.3......h.......\]  
5DCB720 01000178 00000008 00000000 00000000  \[x...............\]  
5DCB730 00000000 00000000 00000000 00000000  \[................\]  
        Repeat 151 times  
5DCC0B0 01000168 0100016B 01000168 01000178  \[h...k...h...x...\]  
5DCC0C0 00000000 00000000 00000000 00000000  \[................\]  
        Repeat 151 times  
5DCCA40 00000000 00000000 01000169 00000000  \[........i.......\]  
5DCCA50 00000000 00000000 00000000 00000000  \[................\]  
        Repeat 185 times  
5DCD5F0 00000000 00000000 00000000 23502301  \[.............\#P\#\]  
  Extent Control Header  
  -----------------------------------------------------------------  
  Extent Header:: spare1: 0      spare2: 0      \#extents: 2      \#blocks: 16      
                  last map  0x00000000  \#maps: 0      offset: 2716    
**      Highwater::  0x0100017d  ext\#: 1      blk\#: 5      ext size: 8    **                   /／HWM在 header block中发生了变化  
  \#blocks in seg. hdr's freelists: 0       
  \#blocks below: 13      
  mapblk  0x00000000  offset: 1       
                   Unlocked  
  --------------------------------------------------------  
**  Low HighWater Mark : **  
**      Highwater::  0x0100017d  ext\#: 1      blk\#: 5      ext size: 8**       
  \#blocks in seg. hdr's freelists: 0       
**  \#blocks below: 13    **  
  mapblk  0x00000000  offset: 1       
  Level 1 BMB for High HWM block: 0x01000168  
  Level 1 BMB for Low HWM block: 0x01000168  
  --------------------------------------------------------  
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0        
  L2 Array start offset:  0x00001434  
  First Level 3 BMB:  0x00000000  
  L2 Hint for inserts:  0x01000169  
  Last Level 1 BMB:  0x01000168  
  Last Level II BMB:  0x01000169  
  Last Level III BMB:  0x00000000  
     Map Header:: next  0x00000000  \#extents: 2    obj\#: 13304  flag: 0x10000000  
  Inc \# 0   
  Extent Map  
  -----------------------------------------------------------------  
   0x01000168  length: 8       
   0x01000178  length: 8       
    
  Auxillary Map  
  --------------------------------------------------------  
   Extent 0     :  L1 dba:  0x01000168 Data dba:  0x0100016b  
   Extent 1     :  L1 dba:  0x01000168 Data dba:  0x01000178  
  --------------------------------------------------------  
    
   Second Level Bitmap block DBAs   
   --------------------------------------------------------  
   DBA 1:   0x01000169  
    
End dump data blocks tsn: 4 file\#: 4 minblk 362 maxblk 362