试验结论：
1、当一张表自建立后只有新增基本不更新时（如存放历史记录的表），可以将pctfree参数尽量调低，这样表数据存储空间以及全表扫描时消耗都会更低；
2、但当表存在大量的update操作时，pctfree调低将会导致修改操作以及修改后表查询性能的性能下降，主要原因为产生了行迁移或行迁移
试验步骤： 一、创建试验表，分别设置pctfree为30与5 create table t_pctfree_30 ( name varchar2(20), created date ) tablespace users pctfree 30; create table t_pctfree_5 ( name varchar2(20), created date ) tablespace users pctfree 5; 二、插入随机字符串 begin for i in 1 .. 10000 loop insert into t_pctfree_30 values(dbms_random.string('u', 10),sysdate+s_test_id.Nextval/24/60/60); end loop; end; / begin for i in 1 .. 10000 loop insert into t_pctfree_5 values(dbms_random.string('u', 10),sysdate+s_test_id.Nextval/24/60/60); end loop; end; / 三、查看两张实验表占用空间情况 SQL> select s.segment_name,s.BYTES,s.blocks from user_segments s where s.segment_name in ('T_PCTFREE_5','T_PCTFREE_30'); SEGMENT_NAME -------------------------------------------------------------------------------- BYTES BLOCKS ---------- ---------- T_PCTFREE_5 327680 40 T_PCTFREE_30 393216 48 四、开启执行计划，查看查询性能消耗，可以发现PCTFREE为30的表比5的实验表全表扫描时，cpu消耗增加20%左右 SQL> select count(0) from T_PCTFREE_30; Execution Plan ---------------------------------------------------------- Plan hash value: 3114683171 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_PCTFREE_30 | 10000 | 13 (0)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 93 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(0) from T_PCTFREE_5; Execution Plan ---------------------------------------------------------- Plan hash value: 2627062867 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_PCTFREE_5 | 10000 | 11 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 77 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 五、对两站表进行update，查看修改时以及修改后性能对比，发现PCTFREE为5的实验表相对30的表性能消耗增大了 SQL> update T_PCTFREE_30 set name=dbms_random.string('u', 15); 10000 rows updated. Execution Plan ---------------------------------------------------------- Plan hash value: 3098004561 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 10000 | 117K| 13 (0)| 00:00:01 | | 1 | UPDATE | T_PCTFREE_30 | | | | | | 2 | TABLE ACCESS FULL| T_PCTFREE_30 | 10000 | 117K| 13 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 105 recursive calls 20486 db block gets 156 consistent gets 1 physical reads 4855444 redo size 843 bytes sent via SQL*Net to client 813 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 10000 rows processed SQL> update T_PCTFREE_5 set name=dbms_random.string('u', 15); 10000 rows updated. Execution Plan ---------------------------------------------------------- Plan hash value: 2342466916 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 10000 | 117K| 15 (0)| 00:00:01 | | 1 | UPDATE | T_PCTFREE_5 | | | | | | 2 | TABLE ACCESS FULL| T_PCTFREE_5 | 10000 | 117K| 15 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 326 recursive calls 30936 db block gets 4738 consistent gets 4 physical reads 5447700 redo size 843 bytes sent via SQL*Net to client 812 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 42 sorts (memory) 0 sorts (disk) 10000 rows processed SQL> select count(0) from T_PCTFREE_5; Execution Plan ---------------------------------------------------------- Plan hash value: 2627062867 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_PCTFREE_5 | 10000 | 15 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 53 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(0) from T_PCTFREE_30; Execution Plan ---------------------------------------------------------- Plan hash value: 3114683171 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_PCTFREE_30 | 10000 | 13 (0)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 46 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 六、查看数据行存储情况 1、对表进行分析统计 analyze table T_PCTFREE_5 compute statistics; analyze table T_PCTFREE_30 compute statistics; 2、查看数据行情况 SQL> select table_name,avg_row_len,num_rows,chain_cnt,chain_cnt/num_rows,pct_free from user_tables where table_name in ('T_PCTFREE_5','T_PCTFREE_30'); TABLE_NAME AVG_ROW_LEN NUM_ROWS CHAIN_CNT CHAIN_CNT/NUM_ROWS PCT_FREE ------------------------------ ----------- ---------- ---------------------------- ---------- T_PCTFREE_30 27 10000 0 0 30 T_PCTFREE_5 28 10000 2052 .2052 5
