manual segment space management(文档的验证）

Oracle maintains one or more free lists—lists of data blocks that have been allocated for that segment’s extents and have free space greater than PCTFREE. These blocks are available for inserts. When you issue an INSERT statement, Oracle checks a free list of the table for the first available data block and uses it if possible. If the free space in that block is not large enough to accommodate the INSERT statement, and the block is at least PCTUSED, then Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.

建立一个手动空间管理的表空间

create tablespace mmt datafile '/u01/oradata/wilson/mmt01.dbf' size 10m reuse extent management local uniform size 1m segment space management manual;

建立一个表

create table freelist_test (id number(4),name varchar(4000)) tablespace mmt;

select dbms_metadata.get_ddl('TABLE','FREELIST_TEST') from dual;

CREATE TABLE "DZ"."FREELIST_TEST"

( "ID" NUMBER(4,0),

"NAME" VARCHAR2(4000)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "MMT"

db_block_size=8192K Pctfree 10% 8192*10%=819.2k ,Pctused 40% 8192*40%=3276.8k

下面我插入3条数据，

insert into freelist_test values (1,rpad('a',4000,'*'));

insert into freelist_test values (2,rpad('a',4000,'*'));

insert into freelist_test values (3,rpad('a',4000,'*'));

commit;

select dbms_rowid.rowid_block_number(rowid) bno, id from freelist_test;

BNO ID

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

10 1

11 2

12 3

按计算应该有3个block在freelist上面

analyze table freelist_test compute statistics;

select table_name,blocks, empty_blocks,num_freelist_blocks from dba_tables where owner = 'DZ' and table_name = 'FREELIST_TEST';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS

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

FREELIST_TEST 3 124 1

结果发现就有一个block在freelist上面，下面看看那个block在freelist上

insert into freelist_test values (4,'a');

select dbms_rowid.rowid_block_number(rowid) bno, id from freelist_test;

BNO ID

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

10 1

11 2

12 3

12 4

发现block12在freelist上面。下面更新一下把这几个块放在freelist上面

delete from freelist_test where id = 4;

update freelist_test set name = 'a' where id =1;

update freelist_test set name = 'a' where id =2;

update freelist_test set name = 'a' where id =3;

update freelist_test set name = rpad('a',4000,'*') where id =1;

update freelist_test set name = rpad('a',4000,'*') where id =2;

update freelist_test set name = rpad('a',4000,'*') where id =3;

commit;

analyze table freelist_test compute statistics;

select table_name,blocks, empty_blocks,num_freelist_blocks from dba_tables where owner = 'DZ' and table_name = 'FREELIST_TEST';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS

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

FREELIST_TEST 3 124 3

分析后发现这3个快重新回到了freelist上面。下面测试文档上红色的这句话。看看把前面的3个block都从freelist上面取下来

insert into freelist_test values (3,rpad('a',4000,'*'));

commit;

analyze table freelist_test compute statistics;

select table_name,blocks, empty_blocks,num_freelist_blocks from dba_tables where owner = 'DZ' and table_name = 'FREELIST_TEST';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS

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

FREELIST_TEST 4 123 1

发现freelist上面就有1个block了，下面看看那个块在freelist上面。

select dbms_rowid.rowid_block_number(rowid) bno, id from freelist_test;

BNO ID

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

10 1

11 2

12 3

13 3

13 4

发现就block 13在freelist上面。

这里验证了文档是对的，这也是manual segment space management的一个缺陷。

[@more@]

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

转载于:http://blog.itpub.net/22531894/viewspace-1027841/