背景

瞭解表段的高水位線與不同狀態資料塊的關係，以及高水位線一些相關概念；
且熟悉哪些字典或包及過程與此相關。

結論
1,oradebug形成的TRC檔案中的HWM我沒有研究明白，這塊比較深，還需梳理思路再戰
2，表的高水位線採用dbms_space.unsed_space獲取
3，表的高水位線=total blocks - total unused blocks +1
4, 表僅插入時，其total unused blocks為0，僅DELETE後此引數才會為非0值
5，last used block為最新分配的區的資料塊個數
6, alter table move或者alter table shrink space可以在刪除表的記錄後，下降表的高水位線
7, alter table shrink space compact即使在表刪除記錄後，仍不會下降表的高水位線

測試

1,資料庫版本
SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

2，建立測試表
SQL> create table t_hwm(a int,b int);

Table created.

3，插入資料
SQL> insert into t_hwm values(1,1);

1 row created.

SQL> commit;

Commit complete.

4，獲取表段所屬檔案及段頭資料塊
SQL> select segment_name,header_file,header_block,bytes,blocks from dba_segments where lower(segment_name)='t_hwm';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS
------------------------------ ----------- ------------ ---------- ----------
T_HWM 4 85442 65536 8

5，轉儲表段頭資料塊
SQL> oradebug setmypid;
ORA-01031: insufficient privileges
SQL> show user
USER is "SCOTT"
SQL> conn /as sysdba
Connected.
SQL> oradebug setmypid;
Statement processed.
SQL> alter system dump datafile 4 block 85442;

System altered.

SQL> oradebug tracefile_name;
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_28025.trc
SQL>

6，獲取轉儲檔案TRC的高水位線
[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_28025.trc|grep -i --color high
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Low HighWater Mark :
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Level 1 BMB for High HWM block: 0x01014dc0

7，再次插入資料，看下高水位線會不會變化
SQL> insert into scott.t_hwm select * from scott.t_hwm;

1 row created.

SQL> commit;

Commit complete.

可見插入資料量小時，高水位線是不會變化的
[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_2943.trc|grep -i --color high
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Low HighWater Mark :
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Level 1 BMB for High HWM block: 0x01014dc0

也就是說只要插入資料量到達一定情況，高水位線才會發生變化,所以我們繼續插入資料，直接分配新的區
SQL> select count(*) from dba_extents where segment_name='T_HWM';

COUNT(*)
----------
1

SQL> insert into scott.t_hwm select * from scott.t_hwm;

2048 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from dba_extents where segment_name='T_HWM';

COUNT(*)
----------
2

可見即使分配第2個區，高水位線仍不會變化
[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_6326.trc|grep -i --color high
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Low HighWater Mark :
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Level 1 BMB for High HWM block: 0x01014dc0

8，繼續插入資料，可見只要插入資料到達一定程度，表段的高水位線才會推進和變化
SQL> insert into scott.t_hwm select * from scott.t_hwm;

16384 rows created.

SQL> select count(*) from dba_extents where segment_name='T_HWM';

COUNT(*)
----------
7

SQL> commit;

Commit complete.

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_9443.trc|grep -i --color high
Highwater:: 0x01014dd0 ext#: 1 blk#: 8 ext size: 8
Low HighWater Mark :
Highwater:: 0x01014dd0 ext#: 1 blk#: 8 ext size: 8
Level 1 BMB for High HWM block: 0x01014dc0

9，現在出現一個問題，到底表段的資料插入到什麼程度後，其高水位線才會推進和變化呢？

---表所有者及相關，要用大寫，否則報錯
create or replace procedure proc_show_space
as
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
begin
dbms_space.unused_space('SCOTT','T_HWM','TABLE',v_total_blocks,v_total_bytes,v_unused_blocks,v_unused_bytes,v_last_used_extent_file_id,v_last_used_extent_block_id,v_last_used_block);
dbms_output.put_line('total blocks ..............'||v_total_blocks);
dbms_output.put_line('total unused blocks .......'||v_unused_blocks);
dbms_output.put_line('last used block............'||v_last_used_block);
end;
/

SQL> select extent_id,blocks from dba_extents where segment_name='T_HWM';

EXTENT_ID BLOCKS
---------- ----------
0 8
1 8
2 8
3 8
4 8
5 8
6 8

7 rows selected.

SQL> exec proc_show_space;
total blocks ..............56
total unused blocks .......0
last used block............8

PL/SQL procedure successfully completed.

可見此時高水位線=total blocks - total unused blocks +1=57

SQL> insert into scott.t_hwm select * from scott.t_hwm;

32768 rows created.

SQL> commit;

Commit complete.

SQL> exec proc_show_space;
total blocks ..............112
total unused blocks .......0
last used block............8

PL/SQL procedure successfully completed.

可見繼續插入記錄後，此時高水位線=total blocks - total unused blocks +1=113

刪除表記錄表的高水位線不會下降
SQL> delete from scott.t_hwm where rownum<=3000;

3000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec proc_show_space;
total blocks ..............112
total unused blocks .......0
last used block............8

PL/SQL procedure successfully completed.

繼續插入記錄，驗證下last used block的含義,可見last used block即最新分配區的資料塊個數
SQL> select count(*) from scott.t_hwm;

COUNT(*)
----------
1000576

SQL> select extent_id,blocks from dba_extents where segment_name='T_HWM';

EXTENT_ID BLOCKS
---------- ----------
0 8
1 8
2 8
3 8
4 8
5 8
6 8
7 8
8 8
9 8
10 8

EXTENT_ID BLOCKS
---------- ----------
11 8
12 8
13 8
14 8
15 8
16 128
17 128
18 128
19 128
20 128
21 128

EXTENT_ID BLOCKS
---------- ----------
22 128
23 128
24 128
25 128
26 128
27 128

28 rows selected.

SQL> exec proc_show_space;
total blocks ..............1664
total unused blocks .......0
last used block............128

PL/SQL procedure successfully completed.

表MOVE後高水線下降
SQL> alter table scott.t_hwm move;

Table altered.

SQL> exec proc_show_space;
total blocks ..............1664
total unused blocks .......107
last used block............21

PL/SQL procedure successfully completed.

高水位線=1664-107+1=158

除了表的MOVE可以下降表的高水位線，還有其它方法嗎

SQL> delete from scott.t_hwm where rownum<=2000;

2000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec proc_show_space;
total blocks ..............1664
total unused blocks .......107
last used block............21

PL/SQL procedure successfully completed.

SQL> alter table scott.t_hwm shrink space;
alter table scott.t_hwm shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

可見透過alter table shrink space也可以下降表的高水位線，但前提先要開啟表的行移動，這時就會影響表的索引可用性
SQL> alter table scott.t_hwm enable row movement;

Table altered.

SQL> alter table scott.t_hwm shrink space;

Table altered.

SQL> exec proc_show_space;
total blocks ..............1560
total unused blocks .......6
last used block............18

PL/SQL procedure successfully completed.

可見alter table shrink space compact不會下降表的高水位線
SQL> delete from scott.t_hwm where rownum<=100;

100 rows deleted.

SQL> commit;

Commit complete.

SQL> exec proc_show_space;
total blocks ..............1560
total unused blocks .......6
last used block............18

PL/SQL procedure successfully completed.

SQL> alter table scott.t_hwm shrink space compact;

Table altered.

SQL> exec proc_show_space;
total blocks ..............1560
total unused blocks .......6
last used block............18

PL/SQL procedure successfully completed.

個人簡介

8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。

服務過的客戶：

中國電信

中國移動

中國聯通

中國電通

國家電網

四川達州商業銀行

湖南老百姓大藥房

山西省公安廳

中國郵政

北京302醫院

河北廊坊新奧集團公司

專案經驗:

中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通4G資料庫效能分析與最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案

聯絡方式：

手機：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/

oracle11g表的高水位線hwm與dbms_space系列一

背景

測試

個人簡介

相關文章