ORACLE FREELIST HWM(2)

3.分析方法
儲存引數基本上屬於oracle internal的東西，因此oralce並沒有提供很好的手段來分析。但是對於DBA來說，還是可以透過block dump和DBMS_SPACE等手段來獲取部分資訊。

3.1 提取block和free list資訊
建立dbms_space使用的儲存過程show_space

SQL>

create or replace procedure show_space

( p_segname in varchar2,

p_owner in varchar2 default user,

p_type in varchar2 default 'TABLE',

p_partition in varchar2 default NULL )

as

l_free_blks number;

l_total_blocks number;

l_total_bytes number;

l_unused_blocks number;

l_unused_bytes number;

l_LastUsedExtFileId number;

l_LastUsedExtBlockId number;

l_last_used_block number;

procedure p( p_label in varchar2, p_num in number )

is

begin

dbms_output.put_line( rpad(p_label,40,'.') || p_num );

end;

begin

dbms_space.free_blocks

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

partition_name => p_partition,

freelist_group_id => 0,

free_blks => l_free_blks );

dbms_space.unused_space

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

partition_name => p_partition,

total_blocks => l_total_blocks,

total_bytes => l_total_bytes,

unused_blocks => l_unused_blocks,

unused_bytes => l_unused_bytes,

last_used_extent_file_id => l_LastUsedExtFileId,

last_used_extent_block_id => l_LastUsedExtBlockId,

last_used_block => l_last_used_block );

p( 'Free Blocks', l_free_blks );

p( 'Total Blocks', l_total_blocks );

p( 'Total Bytes', l_total_bytes );

p( 'Unused Blocks', l_unused_blocks );

p( 'Unused Bytes', l_unused_bytes );

p( 'Last Used Ext FileId', l_LastUsedExtFileId );

p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

p( 'Last Used Block', l_last_used_block );

end;

過程已建立。

SQL> create table t1(a char(1000)) storage( freelists 3);

表已建立。

SQL> set serveroutput on;

SQL> exec show_space('T1');

Free Blocks.............................0 <==Number of blocks on freelist

Total Blocks............................5 <==Total data blocks in segment

Total Bytes.............................20480 <==Total bytes in segment

Unused Blocks...........................4 <==Total unused blocks in segment

Unused Bytes............................16384 <==Total unused bytes in segment

Last Used Ext FileId....................15 <==File id of last used extent

Last Used Ext BlockId...................562 <==Block id of last used extent

Last Used Block.........................1 <==Last used block in extent

PL/SQL 過程已成功完成。

有關show_space的進一步使用技巧可參考文獻5。以下利用上面得到的資料對segment header block進行dump。

SQL>alter system dump datafile 15 block 562;

在udump/ora10792.trc中

*** 2004-09-08 15:29:57.343

Start dump data blocks tsn: 27 file#: 15 minblk 562 maxblk 562

buffer tsn: 27 rdba: 0x03c00232 (15/562)

scn: 0x0000.064560e4 seq: 0x02 flg: 0x00 tail: 0x60e41002

frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 space2: 0 #extents: 1 #blocks: 4

last map 0x00000000 #maps: 0 offset: 2080

Highwater:: 0x03c00233 ext#: 0 blk#: 0 ext size: 4

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 1 obj#: 60033 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x03c00233 length: 4

nfl = 3, nfb = 1 typ = 1 nxf = 0

SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

End dump data blocks tsn: 27 file#: 15 minblk 562 maxblk 562

對於上述塊中欄位的說明，以及相關試驗。由於篇幅所限，本文不再列舉。可參考文獻7。

對非segment header的data block的dump方法和上述類似。data block的結構和segment header block不一樣，如果需要了解，可查閱參考文獻和資料。

3.2 提取HWM資訊
3.2.1 HWM位置
HWM位置按下面的公式計算:

HWM = useed byte = Total Bytes - Unused Blocks

Total Bytes和Unused Blocks都可以用show_space提取。

還可以透過ANALYZE tables得到HWM資訊. DBA_TABLES檢視中包含了可用於各表空間分析的列。其中blocks代表已使用過的塊即HWM，empty_blocks代表未使用的空間。

3.2.1 HWM下空間利用資訊
要比較有資料行的塊的塊數和HIGH WATER MARK下總塊數，可以用下面的公式來展示HWM下未用空間的比例。

p = 1- r/h

r:有資料行的塊的塊數

h:HWM下的塊數.

r可以透過如下方法獲得：

Oracle7:

SELECT count(distinct substr(rowid, 15,4) || substr(rowid, 1,8) ) FROM schema.table;

Oracle8 and Oracle9:

SELECT count(distinct substr(rowid, 7,3) || substr(rowid, 10,6) ) FROM schema.table;

如果公式計算的結果 p是0，就不需要對錶進行重建。如果結果p大於0,應該考慮系統狀況和應用需要來決定是否需要總組表。[@more@]