OracleStudy之OracleHighWaterMark的示例分析

小編給大家分享一下Oracle Study之Oracle High Water Mark的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

成都創(chuàng)新互聯(lián)是一家從事企業(yè)網(wǎng)站建設(shè)、成都做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)、行業(yè)門戶網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)制作的專業(yè)的建站公司，擁有經(jīng)驗(yàn)豐富的網(wǎng)站建設(shè)工程師和網(wǎng)頁(yè)設(shè)計(jì)人員，具備各種規(guī)模與類型網(wǎng)站建設(shè)的實(shí)力，在網(wǎng)站建設(shè)領(lǐng)域樹(shù)立了自己獨(dú)特的設(shè)計(jì)風(fēng)格。自公司成立以來(lái)曾獨(dú)立設(shè)計(jì)制作的站點(diǎn)上千多家。

在手動(dòng)段空間管理（Manual Segment Space Management）中，段中只有一個(gè)HWM，但是在Oracle9iRelease1才添加的自動(dòng)段空間管理（Automatic Segment Space Management）中，又有了一個(gè)低HWM的概念出來(lái)。為什么有了HWM還又有一個(gè)低HWM呢，這個(gè)是因?yàn)樽詣?dòng)段空間管理的特性造成的。在手段段空間管理中，當(dāng)數(shù)據(jù)插入以后，如果是插入到新的數(shù)據(jù)塊中，數(shù)據(jù)塊就會(huì)被自動(dòng)格式化等待數(shù)據(jù)訪問(wèn)。而在自動(dòng)段空間管理中，數(shù)據(jù)插入到新的數(shù)據(jù)塊以后，數(shù)據(jù)塊并沒(méi)有被格式化，而是在第一次在第一次訪問(wèn)這個(gè)數(shù)據(jù)塊的時(shí)候才格式化這個(gè)塊。所以我們又需要一條水位線，用來(lái)標(biāo)示已經(jīng)被格式化的塊。這條水位線就叫做低HWM。

未格式化，意思就是這個(gè)塊，已經(jīng)是屬于這個(gè)段了，但是還保留著原來(lái)的樣子沒(méi)動(dòng)
格式化就是把塊中的數(shù)據(jù)清除掉，并把塊頭改為這個(gè)對(duì)象的
MSSM表空間中的段，只有一個(gè)高水位，高水位下的塊都是格式化了的
但是ASSM表空間中的段，有兩個(gè)高水位：低高水位和高高水位
低高水位下的塊全部是格式化了的
但是低高水位和高高水位之間的塊，則可能是格式化了的，也可能是沒(méi)有

全表掃描時(shí)，通常都是讀至低高水位線，然后根據(jù)位圖去讀低高與高高之間格式化過(guò)的塊，避開(kāi)未格式化的塊

案例分析：

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@rh6 ~]$ 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@rh6 ~]$ 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、驗(yàn)證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@rh6 ~]$ 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@rh6 ~]$ 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: rh6
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@rh6 (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@rh6 (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中發(fā)生了變化
#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

以上是“Oracle Study之Oracle High Water Mark的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

文章名稱：OracleStudy之OracleHighWaterMark的示例分析
瀏覽地址：http://muchs.cn/article36/ihsosg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、Google、自適應(yīng)網(wǎng)站、定制開(kāi)發(fā)、網(wǎng)站收錄、營(yíng)銷型網(wǎng)站建設(shè)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容