分析以及测试PCTFREE和PCTUSED参数是如何协调工作的

测试 目的：为了更深入的理解PCTFREE和PCTUSED参数是如何协调工作的

测试前提：要使得PCTFREE和PCTUSED参数起作用，其表空间的管理必须是MANUAL，所以新创建了一个表空间是segment space management manual(默认方式）类型的，创建代码如下：

CREATE TABLESPACE tbs_daimin
   DATAFILE 'tbs_daimin.dat' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 100M
   segment space management manual;

注：为什么需要segment space management manual？

       因为在segment space management auto(ASSM)情况下pctfree, pctused 被忽略，而在 segment space management manual情況下pctfree, pctused 才会被使用，并且可以看到Oracle是如何使用可用列表（Free List)来管理段中的空闲数据块。

下面是测试过程：

1、新建一张表：

create table T
(
  X NUMBER,
  Y CHAR(1000) default 'x'
);

 2、新建一个存储过程，下面的存储过程是用来测试在对表T进行插入数据以及更新数据的情况下ORALCE对

freelist的管理以及PCTFREE和PCTUSED参数的值是如何影响它工作的。

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)
authid current_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 );
dbms_output.put_line('l_free_blks'||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;
/*dbms_output.put_line('l_free_blks'||l_free_blks);
dbms_output.put_line('l_total_blocks'||l_total_blocks);
dbms_output.put_line('l_unused_blocks'||l_unused_blocks);*/

dbms_output.put_line(l_free_blks||'on FREELIST,'||
                       to_number(l_total_blocks-l_unused_blocks-1)||
                       'used by table');
end;

假设块的大小是8K，使用默认的PCTFREE=10%，PCTUSED=40%，这样分析如下：

每行记录的大小为：

(2+1) bytes for X +(1000+3)bytes for Y=1006 bytes for each row

freelist所需要的最少空间为：

8*1024*10%=820 bytes

当第一次使用这个块时插入数据直到插入的数据占整个块的空间到80%时，则不允许再插入，只能更新；

然后当对数据更新时，当更新存放的数据为比原来所占空间小的，则释放空闲的空间到freelist中去，freelist在块中所占的空间越来越大，直到当数据所占的空间小于整个块的40%时，才允许重新插入新的数据，然后循环上面的过程；

按照上面的计算每行的大小为1006 bytes,计算出来一个块最多可以容纳7行记录：

(8*1024-820)/1006=7.33

由于每个块至少需要使用40%的空间，所以当更新4条记录中的Y字段可以释放1006*4 bytes到freelist中，然后改块又重新可以被使用来插入数据   。

测试1、设置pctfree=10%，pctused=40%：
开始调用show_space存储过程：
先插入10条数据：
begin
for i in 0..10
  loop
  show_space('T');
 insert into t(x,y) values(i,to_char(i,'00'));
 commit;
end loop;
end;
下面显示Free Blocks，used Blocks的变化情况：
Free Blocks.............................0
l_free_blks0
0on FREELIST,0used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1 --在第八条记录输入时由于一个块存放不下，则需要新增加一个块拉存放数据
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table

再更新10条数据中Y字段为空
begin
for i in 0..10
loop
  show_space('T');
  update t set y =null where x=i;
commit;
end loop;
end;
下面显示Free Blocks，used Blocks的变化情况：
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................2   --在第4个更新时则将第一个块又可以重新投入使用放入freelist中，可以重新插入数据
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table

测试2、修改表的定义，设置pctfree=10%，pctused=80% ：
重新定义表T如下：
create table T
(
  X INTEGER,
  Y CHAR(1000) default 'x'
)
tablespace TBS_DAIMIN
  pctfree 10
  pctused 80
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
 
重新调用存储过程执行如下：
先插入10条数据：
begin
for i in 0..10
  loop
  show_space('T');
 insert into t(x,y) values(i,to_char(i,'00'));
 commit;
end loop;
end;
下面显示Free Blocks，used Blocks的变化情况：
Free Blocks.............................0
l_free_blks0
0on FREELIST,0used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,1used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................1
l_free_blks1
1on FREELIST,2used by table

再更新10条数据中Y字段为空
begin
for i in 0..10
loop
  show_space('T');
  update t set y =null where x=i;
commit;
end loop;
end;
下面显示Free Blocks，used Blocks的变化情况：
 Free Blocks.............................1  --在第1个更新时则将第一个块又可以重新投入使用放入freelist中，可以重新插入数据
l_free_blks1
1on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table
Free Blocks.............................2
l_free_blks2
2on FREELIST,2used by table

结论：
1、当PTCUSED参数很低时，导致只有当删除所有行的数据以后，块才会进入FREELIST；
2、当PTCUSED参数很高时，假设一些数据行只是暂时的"缩小"，但是在这个暂时的"缩小"过程中，有新的数据插入，然后造成当需要更新这些块上的新行和旧行时，就不会有足够的空间允许他们增大，他们就是做行迁移；
总之：
当PTCUSED参数和FREELIST是很关键的，一方面，避免许多的行迁移，另一方面，避免浪费太多空间。
需要观察所使用的对象，描述将如何使用它们，然后为这些值的设定提出一个符合逻辑的计划。单凭经验的方法来设定很可能会失败。要根据如何使用来设定。
可以考虑(记往高和低是相对的):
1）高PTCFREE，低PTCUSED：用于插入许多将要更新的数据，并且更新经常会增加行的大小。这样插入后的块上保留了许多空间(高PCTFREE0)，在块返回到自由列表之前，块必须几乎是空的(低PCTUSED)；
2）低PTCFREE，高PTCUSED：用于倾向于对表只使用insert 或delete，或者如果update，update也只是会使行变小；