Oracle Study之－－Oracle High Water Mark-白红宇

Oracle Study之－－Oracle High Water Mark

阅读量：6075 次

发布时间：2019-06-20

本文共 9786 字，大约阅读时间需要 32 分钟。

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, p_w_picpath: 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

转载于:https://blog.51cto.com/tiany/1770823

你可能感兴趣的文章

CodeForces 580B Kefa and Company

查看>>

开发规范浅谈

查看>>

Spark Streaming揭秘 Day29 深入理解Spark2.x中的Structured Streaming

某公司面试java试题之【二】，看看吧，说不定就是你将要做的题

查看>>

BABOK －企业分析（Enterprise Analysis）概要

查看>>

Linux 配置vnc，开启linux远程桌面

ThreadPoolExecutor线程池运行机制分析-线程复用原理

查看>>

React Native 极光推送填坑（ios）

查看>>

Terratest：一个用于自动化基础设施测试的开源Go库

查看>>

修改Windows远程终端默认端口,让服务器更安全

查看>>

扩展器必须，SAS 2.0未必（SAS挺进中端存储系统之三）

查看>>

Eclipse遇到Initializing Java Tooling解决办法

查看>>

while((ch = getchar()) != '\n')

查看>>

好程序员web前端分享JS检查浏览器类型和版本

查看>>

Oracle DG 逻辑Standby数据同步性能优化

查看>>