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@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18921899/viewspace-1016949/,如需转载,请注明出处,否则将追究法律责任。