手动管理段的研究

通过试验对freelist,freelist group 以及手动管理段的管理有了初步的了解。[@more@]

建立手动管理的表空间

SQL> CREATE TABLESPACE eric DATAFILE
'/u01/app/oradata/orcl/eric01.dbf' SIZE 10M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL;

Tablespace created.

分别建立不同的freelist和freelist group的三个表

SQL> CREATE TABLE t_manual
TABLESPACE ERIC
STORAGE (
FREELISTS 1
FREELIST GROUPS 1
)
as select * from dba_objects where 1=2;

Table created.

SQL> CREATE TABLE t_manual_free2
TABLESPACE ERIC
STORAGE (
FREELISTS 2
FREELIST GROUPS 1
)
as select * from dba_objects where 1=2;

Table created.

SQL> CREATE TABLE t_manual_freegroup2
TABLESPACE ERIC
STORAGE (
FREELISTS 4
FREELIST GROUPS 2
)
as select * from dba_objects where 1=2;

Table created.

这三个表的block头ID
SQL> col segment_name for a20
SQL> select segment_name,file_id,block_id,blocks from dba_extents
2 where tablespace_NAME='ERIC';

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS
-------------------- ---------- ---------- ----------
T_MANUAL 9 9 128
T_MANUAL_FREE2 9 137 128
T_MANUAL_FREEGROUP2 9 265 128

SQL> alter system dump datafile 9 block 9;

System altered.

SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' trace_file_name
FROM (SELECT p.spid
7 8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
10 11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
14 15 16 WHERE NAME = 'user_dump_dest') d
17 /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/admin/orcl/udump/orcl_ora_3724.trc

dump出的来信息如下:
Start dump data blocks tsn: 9 file#: 9 minblk 9 maxblk 9
buffer tsn: 9 rdba: 0x02400009 (9/9)
scn: 0x0000.0014103b seq: 0x02 flg: 0x00 tail: 0x103b1002
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1

...

Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x0240000a ext#: 0 blk#: 0 ext size: 127
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 62154 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0240000a length: 127

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 9 file#: 9 minblk 9 maxblk 9

SEGMENT HEADER - UNLIMITED表示此块是segment的header

下面的信息表示freelists列表中没有数据块,是因为此表中没有任何的数据.插入一些数据再试试
#blocks in seg. hdr's freelists: 0
#blocks below: 0

SQL> insert into t_manual
2 select * from dba_objects where rownum<1000;

999 rows created.

SQL> commit;

Commit complete.

dump的信息如下:

Start dump data blocks tsn: 9 file#: 9 minblk 9 maxblk 9
buffer tsn: 9 rdba: 0x02400009 (9/9)
scn: 0x0000.00141180 seq: 0x12 flg: 0x00 tail: 0x11801012
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1
...
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x02400019 ext#: 0 blk#: 15 ext size: 127
#blocks in seg. hdr's freelists: 4
#blocks below: 15
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 62154 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0240000a length: 127

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 7
SEG LST:: flg: USED lhd: 0x02400015 ltl: 0x02400018
End dump data blocks tsn: 9 file#: 9 minblk 9 maxblk 9

表示位于freelist中的数据块有4个,在高水位标志下的数据块有15个,这里说明有11个块已用完,在HWM下有四个块是可用的,来看一下现在的HWM:

SQL> set serveroutput on;
SQL> exec show_space('T_MANUAL','SYS');
Free Blocks.............................4
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................112
Unused Bytes............................917504
Last Used Ext FileId....................9
Last Used Ext BlockId...................9
Last Used Block.........................16

PL/SQL procedure successfully completed.

HWM:128-112=16,那为何在dump时显示只有15个块?应该是这个块头也需要占用一个块,也就是datafile 9 block 9.

另外这张表的freelists=1,所以只能看到一个SEG LST.
lhd(list header)表示该list中第一个可用的block的dba(data block address),ltl(list tail)表示该list中最后一个可用的block的dba.且第一个空闲块头处记录了它之后的下一个空闲块的地址,而下一个空闲块又记录了再下一个空闲块的地址,由此依次记录,一直到最后一个空闲块,下面来验证一下:

lhd: 0x02400015 ltl: 0x02400018

先dump出第一个空闲块:
SQL> alter system dump datafile 9 block 21;

Block header dump: 0x02400015
Object id on Block? Y
seg/obj: 0xf2ca csc: 0x00.141180 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x2400016 ver: 0x01

看到fnx: 0x2400016就是下一个可用的块地址.

SQL> alter system dump datafile 9 block 22;

Block header dump: 0x02400016
Object id on Block? Y
seg/obj: 0xf2ca csc: 0x00.141180 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x2400017 ver: 0x01

fnx: 0x2400017这个块是下一个空闲块.

下面来dump出最后一个空闲块

SQL> alter system dump datafile 9 block 24;

Block header dump: 0x02400018
Object id on Block? Y
seg/obj: 0xf2ca csc: 0x00.141180 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

fnx的值为0x0,表示下面没有可用的空闲块,也就是表明这是freelists中最后的一个空闲块.

下面再来看看freelist为2的情况:

SQL> insert into t_manual_free2
2 select * from dba_objects where rownum<1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 9 block 137;

System altered.

dump出来的信息如下:

Start dump data blocks tsn: 9 file#: 9 minblk 137 maxblk 137
buffer tsn: 9 rdba: 0x02400089 (9/137)
scn: 0x0000.00141b40 seq: 0x12 flg: 0x00 tail: 0x1b401012
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1

...
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x02400099 ext#: 0 blk#: 15 ext size: 127
#blocks in seg. hdr's freelists: 4
#blocks below: 15
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 62155 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0240008a length: 127

nfl = 2, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
SEG LST:: flg: USED lhd: 0x02400095 ltl: 0x02400098
End dump data blocks tsn: 9 file#: 9 minblk 137 maxblk 137

同样,位于freelist中的数据块有4个,在高水位标志下的数据块有15个.
不同的是有三个SEG LST列表了.这里第一个SEG LIST是MFL(Mater free list或segment free list),后面两个是PFL(Process free list).关于MFL和PFL的具体说明可以参考:itpub丛书P180.

最后来看freegroups 2 freelists 4的那张表.

SQL> insert into t_manual_freegroup2
2 select * From dba_objects where rownum<1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 9 block 265;

System altered.

dump信息如下:
Start dump data blocks tsn: 9 file#: 9 minblk 265 maxblk 265
buffer tsn: 9 rdba: 0x02400109 (9/265)
scn: 0x0000.00141c69 seq: 0x07 flg: 0x00 tail: 0x1c691107
frmt: 0x02 chkval: 0x0000 type: 0x11=DATA SEGMENT HEADER WITH FREE LIST BLKS - UNLIMITED
Hex dump of block: st=0, typ_found=1
...
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 125
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x0240011b ext#: 0 blk#: 15 ext size: 125
#blocks in seg. hdr's freelists: 0
#blocks below: 15
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 62156 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0240010c length: 125

nfl = 4, nfb = 3 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 9 file#: 9 minblk 265 maxblk 265

看到这个块的信息:DATA SEGMENT HEADER WITH FREE LIST BLKS - UNLIMITED
由于freegroups>1,所以这个segment header是super master free list或者segment master free list,后面紧随的block是freelist group block.

dump出紧随super master free list的第一个块.

SQL> alter system dump datafile 9 block 266;

Start dump data blocks tsn: 9 file#: 9 minblk 266 maxblk 266
buffer tsn: 9 rdba: 0x0240010a (9/266)
scn: 0x0000.00141060 seq: 0x02 flg: 0x04 tail: 0x10601602
frmt: 0x02 chkval: 0x4196 type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
Hex dump of block: st=0, typ_found=1
...
blocks in free list = 0 ccnt = 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
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 9 file#: 9 minblk 266 maxblk 266

由于freelist为4,所以第一个SEG LST为MFL,后面是四个SEG LST为PFL.这个freelistgroup中没有空闲块.

再dump出紧随super master free list的第二个块.

SQL> alter system dump datafile 9 block 267;

Start dump data blocks tsn: 9 file#: 9 minblk 267 maxblk 267
buffer tsn: 9 rdba: 0x0240010b (9/267)
scn: 0x0000.00141c69 seq: 0x12 flg: 0x04 tail: 0x1c691612
frmt: 0x02 chkval: 0x419f type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
Hex dump of block: st=0, typ_found=1
...
blocks in free list = 4 ccnt = 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
SEG LST:: flg: USED lhd: 0x02400117 ltl: 0x0240011a

这个子的freelist group上有4个空闲块分布在最后一个PFL上.

总结一下:

freelist gropu 1 freelists为1时:一个segment 只有一个MFL,如果每个进程都从MFL中读取空闲块,对于MFL的争用就会引发问题.

freelist gropu 1 freelists为N时:为了减少对MFL的竞争,这时一个segment有一个MFL和N个PFL,空闲块分布在各个PFL中,如果PFL的空闲块用完了,会从MFL中获取新的空闲块,MFL用完了会从其它的PFL中找空闲块,所有的PFL的空闲块都用完了,就要提高HWM,HWM无法提高的话就要分配新的 extent.

freelist gropu M freelists为N时:虽然多个free list可以缓解多个并发session同时更新一个segment时对data block的争用,但是如果只有一个Main free list,那么从Main Free List把block转移到Process Free List这个环节上仍然会出现争用,此时多个freelistgroup发挥效果的时候了.但是多个freelist group会不可避免地产生空间的浪费.

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

转载于:http://blog.itpub.net/271283/viewspace-1012594/