本文通过实验探讨了Oracle数据库中pctfree参数对表存储和查询性能的影响。当pctfree设置较低时，适用于只增不改的历史记录表，可以节省空间和扫描成本。然而，如果表存在大量更新操作，pctfree较低会导致行迁移，从而降低修改和查询性能。实验结果显示，pctfree为30的表在全表扫描时CPU消耗较pctfree为5的表增加约20%，且在update操作后，性能消耗更大。
试验结论：
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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31479729/viewspace-2199261/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31479729/viewspace-2199261/
扫一扫
987
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
