Ooracle 高水位線（high water mask）在不同段管理模式下的推進
Oracle 高水位線（high water mask）在不同段管理模式下的推進
眾所周知，Oracle高水位線標誌著該線以下的block均被Oracle格式過，通俗一點講就是該高水位線以下的block都被Oracle使用過。
通常在執行insert操作時，當高水位線以下block不夠用時，Oracle將會推進高水位線。更進一步講，當有多個程式在同時進行insert操作時，比較
容易引起高水位線爭用，主要表現為enq: HW – contention。這在段管理模式為手動的情況下（SEGMENT SPACE MANAGEMENT MANUAL），更加容易發生。
本文所探討的是，Oracle高水位線如何在不同段管理模式下手動推進。
一、在段管理模式為手動時，block主要由freelist管理
（1）首先建立測試表空間，注意關鍵字SEGMENT SPACE MANAGEMENT MANUAL
SQL> create tablespace zhoul2 datafile '/oradata/mcstar/zhoul201.dbf' size 20m autoextend on SEGMENT SPACE MANAGEMENT MANUAL;
Tablespace created.
SQL> conn /as sysdba
Connected.
SQL> create user zhoul2 identified by zhoul2 default tablespace zhoul2;
User created.
SQL> grant dba to zhoul2;
Grant succeeded.
（2）建立測試表格，並對其進行100%取樣，觀察其高水位線標記
SQL> conn zhoul2/zhoul2
Connected.
SQL> create table zhoul2test as select * from sys.obj$;
Table created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'ZHOUL2TEST',estimate_percent=>100);
PL/SQL procedure successfully completed.
檢視zhoul2test表格佔用空間
SQL> select sum(blocks) from dba_extents where owner='ZHOUL2' and segment_name='ZHOUL2TEST';
SUM(BLOCKS)
———–
1024
檢視zhoul2test高水位線block
SQL> select BLOCKS from dba_tables where OWNER='ZHOUL2' and TABLE_NAME='ZHOUL2TEST';
BLOCKS
———-
895
（3）手動推進高水位線，並分析表格，可以看到高水位線已經推進至1023，並佔用1152個block空間
SQL> alter table zhoul2test allocate extent (instance 1);
Table altered.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'ZHOUL2TEST',estimate_percent=>100);
PL/SQL procedure successfully completed.
SQL> select sum(blocks) from dba_extents where owner='ZHOUL2' and segment_name='ZHOUL2TEST';
SUM(BLOCKS)
———–
1152
SQL> select BLOCKS from dba_tables where OWNER='ZHOUL2' and TABLE_NAME='ZHOUL2TEST';
BLOCKS
———-
1023
那我們再來看看不加instance屬性的分配語句，看看是否也會推進高水位線
SQL> alter table zhoul2test allocate extent;
Table altered.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'ZHOUL2TEST',estimate_percent=>100);
PL/SQL procedure successfully completed.
可以看到，在執行上述語句之後，Oracle只會給ZHOUL2TEST分配空間，但並不會推高水位線。
SQL> select sum(blocks) from dba_extents where owner='ZHOUL2' and segment_name='ZHOUL2TEST';
SUM(BLOCKS)
———–
1280
SQL> select BLOCKS from dba_tables where OWNER='ZHOUL2' and TABLE_NAME='ZHOUL2TEST';
BLOCKS
———-
1023
在這裡再引申一下，在rac環境下，alter table *** allocate extent (instance 1)的用法。在rac環境下，當表空間為手動段管理模式時，
當有多個會話同時插入一張表時，由於存在GCS，多個節點需要同步插入塊的狀態，極容易引起全域性範圍的熱塊等待，或者全域性範圍內的塊請求。
針對這一情況，Oracle推出了freelist groups技術。如將表格屬性修改為freelists 100 freelist groups 2後，再將表格按照如下語法進行extent預分配:
alter table *** allocate extent (size 10m instance 1);
alter table *** allocate extent (size 10m instance 2);
此時，每個freelist group會有多個freelist，Oracle選擇block插入時，會根據instance id進行hash運算，已確定使用哪個freelist group下的freelist。
採用此方法，可以有效避免高水位線的爭用和熱塊在例項間的傳輸。
**************
二、在段管理模式為自動時，block主要由assm管理
在表空間建立時，如果指定語法SEGMENT SPACE MANAGEMENT AUTO，則表示block由assm管理。
首先驗證一下 alter table *** allocate extent (instance 1)是否在ASSM管理模式下也會推進高水位線？
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'ZHOULTEST',estimate_percent=>100);
PL/SQL procedure successfully completed.
SQL> select sum(blocks) from dba_extents where owner='ZHOUL' and segment_name='ZHOULTEST';
SUM(BLOCKS)
———–
11136
SQL> select BLOCKS from dba_tables where OWNER='ZHOUL' and TABLE_NAME='ZHOULTEST';
BLOCKS
———-
10115
執行手動分配語句，並再次進行100%取樣
SQL> alter table zhoultest allocate extent (size 10m instance 1);
Table altered.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'ZHOULTEST',estimate_percent=>100);
PL/SQL procedure successfully completed.
可以看到在物理空間增加的同時，在assm管理模式下，手動推進並不會提高高水位線
SQL> select sum(blocks) from dba_extents where owner='ZHOUL' and segment_name='ZHOULTEST';
SUM(BLOCKS)
———–
12416
SQL> select BLOCKS from dba_tables where OWNER='ZHOUL' and TABLE_NAME='ZHOULTEST';
BLOCKS
———-
10115
再次使用Oracle提供的指令碼進行高水位檢視，詳見metalink doc 820043.1
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin
dbms_space.unused_space(
'ZHOUL',
'ZHOULTEST',
'TABLE',
TOTAL_BLOCKS,
TOTAL_BYTES,
UNUSED_BLOCKS,
UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('OBJECT_NAME = FREELIST_T');
dbms_output.put_line('———————————–');
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);
dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK);
end;
/
輸出結果為：
OBJECT_NAME = FREELIST_T
———————————–
TOTAL_BLOCKS = 12416
UNUSED_BLOCKS = 2176
LAST_USED_EXTENT_BLOCK_ID = 3593
LAST_USED_BLOCK = 1024
PL/SQL procedure successfully completed.
檢視在ASSM下，高水位block的分佈情況
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'ZHOUL',
'ZHOULTEST',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
輸出結果為
Unformatted Blocks = 0
Blocks with 00-25% free space = 17
Blocks with 26-50% free space = 0
Blocks with 51-75% free space = 0
Blocks with 76-100% free space = 212
Full Blocks = 9868
PL/SQL procedure successfully completed.
10115-（17+212+9868）=18，那麼這18個塊去哪裡了呢？
SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ZHOUL' and segment_name='ZHOULTEST';
HEADER_FILE HEADER_BLOCK
———– ————
7 5131
SQL> alter system dump datafile 7 block 5131;
System altered.
找到跟蹤檔案，可以看到表格ZHOULTEST 第一級點陣圖的High HWM block=Low HWM block=0×01c00e0c，轉換之後為file#=7,block#=3596
——————————————————–
Low HighWater Mark :
Highwater:: 0×01c01209 ext#: 80 blk#: 1024 ext size: 1024
#blocks in seg. hdr's freelists: 0
#blocks below: 10240
mapblk 0×00000000 offset: 80
Level 1 BMB for High HWM block: 0×01c00e0c
Level 1 BMB for Low HWM block: 0×01c00e0c
透過bbed檢視file#=7,block#=3596，由資料檔案頭0×20轉換成十進位制數字32後，可知這是FIRST LEVEL BITMAP BLOCK
BBED> dump block 3596
File: /oradata/mcstar/zhoul01.dbf (0)
Block: 3596 Offsets: 0 to 511 Dba:0×00000000
————————————————————————
20a20000 0c0ec001 bfb54407 000a0104 27ae0000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 04000000 ffffffff 00000000 00000000 00010000
01000100 01000000 00000000 00000000 00000000 ac000000 8eea8e4d 8eea8e4d
00000000 00000000 00000000 00000000 0a14c001 8c000000 50000000 00040000
00040000 0912c001 00000000 50000000 00000000 83270000 00000000 01000000
d31b0100 00000000 00000000 0911c001 00010000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 11111111 11111111 11111111 11111111 11111111
11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111
11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111
11112111 11111111 11111111 11111111 11111111 11111111 11111111 11111111
再次dump block印證了猜想
SQL> alter system dump datafile 7 block 3596;
System altered.
ump of First Level Bitmap Block
——————————–
nbits : 4 nranges: 1 parent dba: 0×01c0140a poffset: 140
unformatted: 0 total: 256 first useful block: 0
owning instance : 1
instance ownership changed at 03/27/2011 15:43:10
Last successful Search 03/27/2011 15:43:10
Freeness Status: nf1 1 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 172
Bitmap block lock opcode 0
Locker xid: : 0×0000.000.00000000
Inc #: 0 Objd: 72659
HWM Flag: HWM Set
Highwater:: 0×01c01209 ext#: 80 blk#: 1024 ext size: 1024
#blocks in seg. hdr's freelists: 0
#blocks below: 10115
mapblk 0×00000000 offset: 80
——————————————————–
DBA Ranges :
——————————————————–
0×01c01109 Length: 256 Offset: 0
回到剛才跟蹤檔案，將0×01c04509轉化成10進製為file#=7 block#=17673
。。。
Extent 96 : L1 dba: 0×01c04409 Data dba: 0×01c04489
Extent 97 : L1 dba: 0×01c04509 Data dba: 0×01c0450a
此值剛好對應
SQL>select EXTENT_ID,block_id,BLOCKS from dba_extents where owner='ZHOUL' and segment_name='ZHOULTEST'；
。。。
96 17545 128
97 17673 128
98 rows selected.
繼續dump
SQL> alter system dump datafile 7 block 17673;
System altered.
檢視dump檔案可以看到很多資料塊均為格式化：
Dump of First Level Bitmap Block
——————————–
nbits : 4 nranges: 1 parent dba: 0×01c0140a poffset: 149
unformatted: 127 total: 128 first useful block: 1
owning instance : 1
instance ownership changed at
Last successful Search
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 1
Bitmap block lock opcode 0
Locker xid: : 0×0000.000.00000000
Inc #: 0 Objd: 72659
——————————————————–
DBA Ranges :
——————————————————–
0×01c04509 Length: 128 Offset: 0
0:Metadata 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
。。。
也就意味在assm下，資料高水位線隨著extent的擴充套件而自動推進，當然全表掃描將掃描第一級點陣圖的High HWM block以下的資料。
此試驗也驗證了tom書中Oracle9i10g程式設計藝術的話：
In an ASSM tablespace, however,there is an HWM and a low HWM (see Figure 10-2). In MSSM, when the HWM is advanced
(e.g., as rows are inserted), all of the blocks are formatted and valid, and Oracle can read
themsafely. With ASSM, however, when the HWM is advanced, Oracle doesn’t format all of the
blocks immediately—they are only formatted and made safe to read upon their first use. So,
when full scanning a segment, we have to know if the blocks to be read are “safe” or unformat-
ted (meaning they contain nothing of interest and we do not process them). To make it so that
not every block in the table need go through this safe/not safe check, Oracle maintains a low
HWM and an HWM. Oracle will full scan the table up to the HWM—and for all of the blocks
below the low HWM, it will just read and process them. For blocks between the low HWM and
the HWM, it must be more careful and refer to the ASSM bitmap information used to manage
these blocks to see which of them it should read and which it should just ignore.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23732248/viewspace-2987201/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle段高水位(HWM, high water mark)問題Oracle
- Oracle表段中的高水位線HWMOracle
- 通過降低表的高水位(HWM: High Water Mark) ，解決一生產系統故障
- oracle 高水位線及如何有效的降低高水位線Oracle
- oracle的高水位線HWMOracle
- oracle的高水位線(HWM)Oracle
- ORACLE 高水位線（HWM）Oracle
- oracle 回收高水位線Oracle
- Oracle 降低高水位線Oracle
- oracle回收高水位線Oracle
- Oracle表段中高水位線HWMOracle
- 降低Oracle高水位線的方法Oracle
- Oracle的高水位線介紹Oracle
- oracle高水位線處理Oracle
- Oracle之降低高水位線Oracle
- oracle 高水位線詳解Oracle
- Oracle 高水位線的一點研究Oracle
- 一、oracle 高水位線詳解Oracle
- delete與高水位線HWM回收delete
- 關於高水位線和deletedelete
- High Water Mark過高導致cache buffer chain等待嚴重AI
- 對Oracle高水位線的研究實踐Oracle
- 高水位線、行遷移行連結
- oracle表碎片以及整理（高水位線）Oracle
- 怎麼留住新進玩家？這款遊戲在不同階段有不同的做法遊戲
- 高水位線下空閒塊過多導致的SQL效能問題SQL
- ORACLE資料庫降低高水位線方法Oracle資料庫
- Oracle delete 高水位線處理問題Oracledelete
- Oracle高水位線(HWM)及效能優化Oracle優化
- Oracle高水位Oracle
- Oracle案例10——HWM（高水位線）效能優化Oracle優化
- oracle回收高水位Oracle
- Oracle 高水位(HWM)Oracle
- 線段樹進階
- Enq : HW-contention高水位線的擴充套件競爭ENQ套件
- 關於高水位的知識
- 各個Oracle 版本下如何調整高水位(HWM)Oracle
- oracle高水位問題Oracle