ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 今天试验有关10g的ASSM:

今天试验有关10g的ASSM:

原创 Linux操作系统 作者:orchidllh 时间:2005-03-14 00:00:00 0 删除 编辑
今天看到piner的ASSM内部存储研究大揭密,打算拿我的某一个10g的数据库试验一下,以下是试验的步骤:
1、首先创建两个表空间,分别对应表和索引:
CREATE TABLESPACE A_ASSM
DATAFILE '/home/oracle/oradata/*/a_assm.dbf' SIZE 100M REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 1000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE A_INDEX_ASSM
DATAFILE '/home/oracle/oradata/*/a_index_assm.dbf' SIZE 1000M REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 1000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;

2、将原有的表空间的对象挪到新的assm表空间中:
移表:
ALTER TABLE A.TAB_NAME MOVE TABLESPACE A_ASSM

移索引:
ALTER INDEX A.PK_A_TAB REBUILD TABLESPACE A_INDEX_ASSM;

3、删除原来的表空间:
drop tablespace a;

drop tablespace a_index;

4、创建show_space过程:
create or replace procedure show_space
( p_segname_1 in varchar2,
p_space in varchar2 default 'MANUAL',
p_type_1 in varchar2 default 'TABLE' ,
p_analyzed in varchar2 default 'N',
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);

l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;

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
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;

if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
end if;

if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
end if;

if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
p_type := 'CLUSTER';
end if;


dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
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 );

if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );

p( 'Free Blocks', l_free_blks );
end if;

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 );


/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ',50,'*'));
dbms_output.put_line('The segment is analyzed');
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);

end if;

end;

5、执行show_space结果:
SQL> exec show_space('STAT_INCOME','auto');
Total Blocks............................1024
Total Bytes.............................8388608
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................7
Last Used Ext BlockId...................3720
Last Used Block.........................128

PL/SQL procedure successfully completed.

6、查看dba_extents表:
select t.segment_name,t.extent_id,t.block_id from dba_extents t where t.segment_name = 'STAT_INCOME'
STAT_INCOME 0 2737
STAT_INCOME 1 2745
STAT_INCOME 2 2753
STAT_INCOME 3 2761
STAT_INCOME 4 2769
STAT_INCOME 5 2777
STAT_INCOME 6 2785
STAT_INCOME 7 2793
STAT_INCOME 8 2801
STAT_INCOME 9 2809
STAT_INCOME 10 2817
STAT_INCOME 11 2825
STAT_INCOME 12 2833
STAT_INCOME 13 2841
STAT_INCOME 14 2849
STAT_INCOME 15 2857
STAT_INCOME 16 2953
STAT_INCOME 17 3081
STAT_INCOME 18 3209
STAT_INCOME 19 3337
STAT_INCOME 20 3465
STAT_INCOME 21 3593
STAT_INCOME 22 3721
可以看到这个表有23个extent,对应的块ID也有。

7、由于是assm管理的表空间,所以可以查看块的使用情况:
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 ('ADMAPP', 'STAT_INCOME', '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('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;

Unformatted Blocks = 2
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 1
FS4 Blocks = 12
Full Blocks = 985

PL/SQL procedure successfully completed.

FS1表示0-25%空闲空间的块
FS2表示25-50%空闲空间的块
FS3表示50-75%空闲空间的块
FS4表示 75-100%空闲空间的块
可以看到0-25%空闲和25-50%空闲的块都没有,50-75%空闲的块有1个,75-100%空闲的块有12个,满的块有985个。

8、挑了几个块察看使用的情况:
SQL> alter system dump datafile 7 block 2737;

System altered.
这是0 extents的第一块

SQL> alter system dump datafile 7 block 2738;

System altered.
这是0 extents的第二块

SQL> alter system dump datafile 7 block 2739;

System altered.
这是0 extents的第三块

SQL> alter system dump datafile 7 block 3722;

System altered.

SQL> alter system dump datafile 7 block 3753;

System altered.

[lisa@bj udump]$  cat *_ora_16806.trc
/home/oracle/admin/*/udump/*_ora_16806.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.1.0/db_1
System name:    Linux
Node name:      bj
Release:        2.4.21-20.ELsmp
Version:        #1 SMP Wed Aug 18 20:46:40 EDT 2004
Machine:        i686
Instance name: *
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 16806, image:
oracle@bj (TNS V1-V3)

*** 2005-03-11 17:22:48.306
*** SERVICE NAME:(SYS$USERS) 2005-03-11 17:22:48.306
*** SESSION ID:(240.61977) 2005-03-11 17:22:48.305
Start dump data blocks tsn: 8 file#: 7 minblk 2737 maxblk 2737
buffer tsn: 8 rdba: 0x01c00ab1 (7/2737)
scn: 0x0002.c7196f20 seq: 0x03 flg: 0x04 tail: 0x6f202003
frmt: 0x02 chkval: 0x4761 type: 0x20=FIRST LEVEL BITMAP BLOCK  ----第一级的位图块
Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 2         parent dba:  0x01c00ab2   poffset: 0    
   unformatted: 0       total: 16        first useful block: 3     
   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 : 16    
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Inc #: 0 Objd: 67164
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x01c00ab1  Length: 8      Offset: 0     
   0x01c00ab9  Length: 8      Offset: 8     
 
   0:Metadata   1:Metadata   2:Metadata   3:FULL      ---总共16个块,其中metadata使用3个,其它都是满的
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL   15:FULL
  --------------------------------------------------------
End dump data blocks tsn: 8 file#: 7 minblk 2737 maxblk 2737
*** 2005-03-11 17:28:01.538
Start dump data blocks tsn: 8 file#: 7 minblk 2738 maxblk 2738
buffer tsn: 8 rdba: 0x01c00ab2 (7/2738)
scn: 0x0002.c7196f20 seq: 0x16 flg: 0x04 tail: 0x6f202116
frmt: 0x02 chkval: 0x47e2 type: 0x21=SECOND LEVEL BITMAP BLOCK  ----第二级位图块,记录对应的下一级的位置
Dump of Second Level Bitmap Block
   number: 22      nfree: 1       ffree: 21     pdba:     0x01c00ab3
   Inc #: 0 Objd: 67164
  opcode:0
 xid:
  L1 Ranges :
  --------------------------------------------------------
   0x01c00ab1  Free: 1 Inst: 1  ----13  ab1:2737
   0x01c00ac1  Free: 1 Inst: 1  ----15  ac1:2753
   0x01c00ad1  Free: 1 Inst: 1  ----15  ad1:2769
   0x01c00ae1  Free: 1 Inst: 1  ----15  ae1:2785
   0x01c00af1  Free: 1 Inst: 1  ----15  af1:2801
   0x01c00b01  Free: 1 Inst: 1  ----15  b01:2817
   0x01c00b11  Free: 1 Inst: 1  ----15  b11:2833
   0x01c00b21  Free: 1 Inst: 1  ----15  b21:2849
   0x01c00b89  Free: 1 Inst: 1  ----62  b89:2953
   0x01c00b8a  Free: 1 Inst: 1  ----64  b8a:2954
   0x01c00c09  Free: 1 Inst: 1  ----62  c09:3081
   0x01c00c0a  Free: 1 Inst: 1  ----64  c0a:3082
   0x01c00c89  Free: 1 Inst: 1  ----62  c89:3209
   0x01c00c8a  Free: 1 Inst: 1  ----64  c8a:3210
   0x01c00d09  Free: 1 Inst: 1  ----62  d09:3337
   0x01c00d0a  Free: 1 Inst: 1  ----64  d0a:3338
   0x01c00d89  Free: 1 Inst: 1  ----62  d89:3465
   0x01c00d8a  Free: 1 Inst: 1  ----64  d8a:3466
   0x01c00e09  Free: 1 Inst: 1  ----62  e09:3593
   0x01c00e0a  Free: 1 Inst: 1  ----64  e0a:3594
   0x01c00e89  Free: 1 Inst: 1  ----62  e89:3721
   0x01c00e8a  Free: 5 Inst: 1  ----49  e8a:3722     ------13 + 15*7 + 62*7 + 64*6 + 49 = 985
 
  --------------------------------------------------------
End dump data blocks tsn: 8 file#: 7 minblk 2738 maxblk 2738
*** 2005-03-11 17:29:11.223
Start dump data blocks tsn: 8 file#: 7 minblk 2739 maxblk 2739
buffer tsn: 8 rdba: 0x01c00ab3 (7/2739)
scn: 0x0002.c719f573 seq: 0x01 flg: 0x04 tail: 0xf5732301
frmt: 0x02 chkval: 0x6d35 type: 0x23=PAGETABLE SEGMENT HEADER   ----页表的段头
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 23     #blocks: 1024 
                  last map  0x00000000  #maps: 0      offset: 2716 
      Highwater::  0x01c00f09  ext#: 22     blk#: 128    ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 1024 
  mapblk  0x00000000  offset: 22   
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01c00ef7  ext#: 22     blk#: 110    ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 1006
  mapblk  0x00000000  offset: 22   
  Level 1 BMB for High HWM block: 0x01c00e8a
  Level 1 BMB for Low HWM block: 0x01c00e8a
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0     
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x01c00ab2
  Last Level 1 BMB:  0x01c00e8a
  Last Level II BMB:  0x01c00ab2
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 23   obj#: 67164  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x01c00ab1  length: 8    
   0x01c00ab9  length: 8    
   0x01c00ac1  length: 8    
   0x01c00ac9  length: 8    
   0x01c00ad1  length: 8    
   0x01c00ad9  length: 8    
   0x01c00ae1  length: 8    
   0x01c00ae9  length: 8    
   0x01c00af1  length: 8    
   0x01c00af9  length: 8    
   0x01c00b01  length: 8    
   0x01c00b09  length: 8    
   0x01c00b11  length: 8    
   0x01c00b19  length: 8    
   0x01c00b21  length: 8    
   0x01c00b29  length: 8    
   0x01c00b89  length: 128  
   0x01c00c09  length: 128  
   0x01c00c89  length: 128  
   0x01c00d09  length: 128  
   0x01c00d89  length: 128  
   0x01c00e09  length: 128  
   0x01c00e89  length: 128  
 
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x01c00ab1 Data dba:  0x01c00ab4
   Extent 1     :  L1 dba:  0x01c00ab1 Data dba:  0x01c00ab9
   Extent 2     :  L1 dba:  0x01c00ac1 Data dba:  0x01c00ac2
   Extent 3     :  L1 dba:  0x01c00ac1 Data dba:  0x01c00ac9
   Extent 4     :  L1 dba:  0x01c00ad1 Data dba:  0x01c00ad2
   Extent 5     :  L1 dba:  0x01c00ad1 Data dba:  0x01c00ad9
   Extent 6     :  L1 dba:  0x01c00ae1 Data dba:  0x01c00ae2
   Extent 7     :  L1 dba:  0x01c00ae1 Data dba:  0x01c00ae9
   Extent 8     :  L1 dba:  0x01c00af1 Data dba:  0x01c00af2
   Extent 9     :  L1 dba:  0x01c00af1 Data dba:  0x01c00af9
   Extent 10    :  L1 dba:  0x01c00b01 Data dba:  0x01c00b02
   Extent 11    :  L1 dba:  0x01c00b01 Data dba:  0x01c00b09
   Extent 12    :  L1 dba:  0x01c00b11 Data dba:  0x01c00b12
   Extent 13    :  L1 dba:  0x01c00b11 Data dba:  0x01c00b19
   Extent 14    :  L1 dba:  0x01c00b21 Data dba:  0x01c00b22
   Extent 15    :  L1 dba:  0x01c00b21 Data dba:  0x01c00b29
   Extent 16    :  L1 dba:  0x01c00b89 Data dba:  0x01c00b8b
   Extent 17    :  L1 dba:  0x01c00c09 Data dba:  0x01c00c0b
   Extent 18    :  L1 dba:  0x01c00c89 Data dba:  0x01c00c8b
   Extent 19    :  L1 dba:  0x01c00d09 Data dba:  0x01c00d0b
   Extent 20    :  L1 dba:  0x01c00d89 Data dba:  0x01c00d8b
   Extent 21    :  L1 dba:  0x01c00e09 Data dba:  0x01c00e0b
   Extent 22    :  L1 dba:  0x01c00e89 Data dba:  0x01c00e8b
  --------------------------------------------------------
 
   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x01c00ab2
 
End dump data blocks tsn: 8 file#: 7 minblk 2739 maxblk 2739
Start dump data blocks tsn: 8 file#: 7 minblk 3722 maxblk 3722  ----这是最后一块的内容,可以看到详细的使用情况
buffer tsn: 8 rdba: 0x01c00e8a (7/3722)
scn: 0x0002.c71a05e6 seq: 0x01 flg: 0x04 tail: 0x05e62001
frmt: 0x02 chkval: 0x57fc type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 1         parent dba:  0x01c00ab2   poffset: 21   
   unformatted: 2       total: 64        first useful block: 0     
   owning instance : 1
   instance ownership changed at 03/11/2005 01:09:05
   Last successful Search 03/11/2005 01:09:05
   Freeness Status:  nf1 0      nf2 0      nf3 1      nf4 12    
 
   Extent Map Block Offset: 4294967295
   First free datablock : 46    
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Inc #: 0 Objd: 67164
  HWM Flag: HWM Set
      Highwater::  0x01c00f09  ext#: 22     blk#: 128    ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 1024 
  mapblk  0x00000000  offset: 22   
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x01c00ec9  Length: 64     Offset: 0     
 
   0:FULL   1:FULL   2:FULL   3:FULL                    ---满的块是49个,unformatted的有两个,
   4:FULL   5:FULL   6:FULL   7:FULL                    ---50-75% free的有一个,75-100% free的有12个
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL   15:FULL
   16:FULL   17:FULL   18:FULL   19:FULL
   20:FULL   21:FULL   22:FULL   23:FULL
   24:FULL   25:FULL   26:FULL   27:FULL
   28:FULL   29:FULL   30:FULL   31:FULL
   32:FULL   33:FULL   34:FULL   35:FULL
   36:FULL   37:FULL   38:FULL   39:FULL
   40:FULL   41:FULL   42:FULL   43:FULL
   44:FULL   45:FULL   46:unformatted   47:unformatted
   48:75-100% free   49:75-100% free   50:FULL   51:75-100% free
   52:FULL   53:75-100% free   54:FULL   55:75-100% free
   56:50-75% free   57:75-100% free   58:75-100% free   59:75-100% free
   60:75-100% free   61:75-100% free   62:75-100% free   63:75-100% free
  --------------------------------------------------------
End dump data blocks tsn: 8 file#: 7 minblk 3722 maxblk 3722

计算的结果和前面dbms_space.space_usage过程取到的相同。
但是有一些问题我还没有明白,前面16块的长度是8,后面的为什么变成128,跟什么设置有关系?
前面的16块每一块都至少有一个块是Metadata类型的,为什么后来的块就不需要了呢?
看了半天,还是只摸了个皮毛,差的还远呀。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/51862/viewspace-180521/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-21

  • 博文量
    180
  • 访问量
    843870