Shulou(Shulou.com)05/31 Report--

Editor to share with you the Oracle High Water Mark of Oracle Study example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

In manual segment space management (Manual Segment Space Management), there is only one HWM in the segment, but in automatic segment space management (Automatic Segment Space Management), which is just added by Oracle9iRelease1, there is a concept of low HWM. Why is there a low HWM with HWM? this is due to the feature of automatic segment space management. In the means segment space management, when the data is inserted, if it is inserted into a new data block, the data block will be automatically formatted to wait for data access. In automatic segment space management, after the data is inserted into a new block, the block is not formatted, but the block is formatted the first time it is accessed. So we need another watermark to mark the blocks that have been formatted. This water mark is called low HWM.

Unformatted, which means that this block already belongs to this paragraph, but still retains its original appearance.

Formatting is to erase the data in the block and change the block to this object.

The segment in the MSSM table space has only one high water level, and the blocks at the high water level are all formatted.

But the segment in the ASSM table space has two high water levels: low high water level and high high water level

The blocks under low and high water levels are all formatted.

But the block between the low and high water levels may or may not be formatted.

When scanning a full table, it is usually read to the low and high water mark, and then read the formatted blocks between the low and high according to the bitmap to avoid the unformatted blocks

Case study:

1. Analysis table

09:46:07 SCOTT@ test1 > analyze table emp2 compute statistics

09:48:00 2

Table analyzed.

2. View the hwm in table

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. Analyze 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. View the information in 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

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 [.]

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 [.]

Repeat 152 times

918CB0 010000B0 010000B3 00000000 00000000 [.]

918CC0 00000000 00000000 00000000 [.]

Repeat 151 times

919640 00000000 00000000 010000B1 00000000 [.]

919650 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. Verify the changes in 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 purl 07.142

* SESSION ID: (31. 2) 2016-05-06 10 13 7 142

* CLIENT ID: () 2016-05-06 1015 1314 07.142

* SERVICE NAME: (SYS$USERS) 2016-05-06 10 1315 07.142

* MODULE NAME: (sqlplus@rh6 (TNS V1-V3)) 2016-05-06 10 purl 13 purl 07.142

* ACTION NAME: () 2016-05-06 1015 1314 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

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 [.]

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 [.]

Repeat 151 times

5DCC0B0 01000168 0100016B 01000168 01000178 [h...k...h...x...]

5DCC0C0 00000000 00000000 00000000 [.]

Repeat 151 times

5DCCA40 00000000 00000000 01000169 00000000 [.i.]

5DCCA50 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 has changed in 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

The above is all the contents of the article "sample Analysis of Oracle Study's Oracle High Water Mark". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.