pct_free_pct_used,ini_trans之相关参数系统(一)

最新推荐文章于 2023-12-28 17:27:45 发布

clg10051

最新推荐文章于 2023-12-28 17:27:45 发布

阅读量593

点赞数

文章标签：数据库

pct_free,pct_used,ini_trans各参数语义：

PCTFREE integer
pctfree参数必须位于0-99之间，指定数据块中用于为将来更新表记录预留的空间比例.

默认是10.如为0意味着整个数据块用于插入新数据。

特别要注意的是，alter index重新变更其pctfree时，仅能在modify_index_default_attrs子句
指定其值及在split_partition_clause子句（注：此2子句请查阅官方手册）

Restriction on the PCTFREE Clause When altering an index, you can specify this parameter
only in the modify_index_default_attrs clause and the split_partition_clause.

--我们逐一测试.直至真义

假如不显式指定pct_free,pct_used,ini_trans会如何
SQL> create table t_storage(a int);

Table created

--未显式指定其值分别为10,null,1
SQL> select ut.PCT_FREE,ut.PCT_USED,ut.INI_TRANS from user_tables ut where table_name='T_STORAGE';

PCT_FREE   PCT_USED INI_TRANS
---------- ---------- ----------
        10                     1

--分别变更上述的参数值，其参数值又是什么景象呢
SQL> alter table t_storage pctfree 20;

Table altered

--经查变更为更新后的值
SQL> select ut.PCT_FREE,ut.PCT_USED,ut.INI_TRANS from user_tables ut where table_name='T_STORAGE';

PCT_FREE   PCT_USED INI_TRANS
---------- ---------- ----------
        20                     1

SQL> alter table t_storage initrans 2;

Table altered

--在oracle11g r2中pct_used已废弃
SQL> select ut.PCT_FREE,ut.PCT_USED,ut.INI_TRANS from user_tables ut where table_name='T_STORAGE';

PCT_FREE   PCT_USED INI_TRANS
---------- ---------- ----------
        20                     2

--变更表的pctfree
SQL> alter table t_storage pctfree 0;

Table altered

SQL> select ut.table_name,ut.PCT_FREE,ut.PCT_USED,ut.INI_TRANS from user_tables ut where table_name='T_STORAGE';

TABLE_NAME                       PCT_FREE   PCT_USED INI_TRANS
------------------------------ ---------- ---------- ----------
T_STORAGE                               0                     2

SQL> insert into t_storage values(1);

1 row inserted

SQL> commit;

Commit complete

--用dbms_rowid查表记录所属的数据块及文件
SQL> select dbms_rowid.rowid_block_number(rowid) from t_storage;

DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------
                        231446

SQL> select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','T_STORAGE') from t_storage;

DBMS_ROWID.ROWID_TO_ABSOLUTE_F
------------------------------
                            10

--获取上述数据块的trace
SQL> alter system dump datafile 10 block 231446;

System altered

--trace文件相关内容
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
end_of_block_dump

--更新表
SQL> update t_storage set a=100000000000;

1 row updated

SQL> commit;

Commit complete

--更新后获取数据块的trace文件
SQL> alter system checkpoint;

System altered

SQL> alter system dump datafile 10 block 231446;

System altered

block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c6 0b
end_of_block_dump

--插入新记录
SQL> insert into t_storage values(99);

1 row inserted

SQL> commit;

Commit complete

SQL> alter system checkpoint;

System altered

--插入新记录后确保新记录与之前插入记录在同一个数据块上
SQL> select dbms_rowid.rowid_block_number(rowid) from t_storage;

DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------
                        231446
                        231446

SQL> select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','T_STORAGE') from t_storage;

DBMS_ROWID.ROWID_TO_ABSOLUTE_F
------------------------------
                            10
                            10
--新增的记录已反应在trace中
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c6 0b
tab 0, row 1, @0x1f8c
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 64
end_of_block_dump

小结：
1,如表pctfree配置为0，则在表所属的数据块可以继续插入insert数据

---用plsql大批量插入数据填充满上述表所属的数据块
SQL> begin
2 for i in 1..1000000 loop
3 insert into t_storage values(i);
4 if mod(i,10000)=0 then
5 commit;
6 end if;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed

--基本每个数据块可存储的记录数为733条
SQL> select dbms_rowid.rowid_block_number(rowid),count(1) from t_storage where dbms_rowid.rowid_block_number(rowid)=231446 group by dbms_rowid.rowid_block_number(rowid)
2 ;

DBMS_ROWID.ROWID_BLOCK_NUMBER(   COUNT(1)
------------------------------ ----------
                        231446        733

--如果把pctfree设置为非0，我猜可能每个数据块存储的记录数会少于733条吧

--清空表
SQL> truncate table t_storage;

Table truncated

alter table t_storage pctfree 50;

--重新插入数据到表
SQL> begin
2 for i in 1..1000000 loop
3 insert into t_storage values(i);
4 if mod(i,10000)=0 then
5    commit;
6 end if;
7 end loop;
8 end;
9 /


PL/SQL procedure successfully completed

--呶，看到没，加大pctfree后,果不其然,每个数据块占用的记录条数直线下降。由700多减至300多
SQL> select dbms_rowid.rowid_block_number(rowid),count(1) from t_storage where dbms_rowid.rowid_block_number(rowid)=231446 group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(   COUNT(1)
------------------------------ ----------
                        231446        366

小结：pctfree配置直接影响数据块存储记录记录的多少

pctfree用于为table的现存记录更新所用,可否理解为pctfree越大，则update更块呢，因为数据块中的可用空间很大，
这样oracle update table时获取可能空间就很容易了.
而越小，是否update就更慢呢

SQL> truncate table t_storage;

Table truncated

--设置pctfree为10
SQL> alter table t_storage pctfree 10;

Table altered

--插入数据
SQL> set time on
21:27:26 SQL> set timing on
21:27:29 SQL>
21:27:33 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_storage values(i);
           4 if mod(i,10000)=0 then
           5    commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 29.75 seconds

--更新所用22秒左右
21:28:02 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 22.23 seconds

--加大pctfree更新效率又表现如何呢
21:30:54 SQL> truncate table t_storage;

Table truncated

Executed in 0.125 seconds

21:31:12 SQL> alter table t_storage pctfree 50;

Table altered

Executed in 0 seconds

--插入数据用29秒左右
21:32:27 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_storage values(i);
           4 if mod(i,10000)=0 then
           5    commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 29.89 seconds

--更新用时27秒左右
21:32:57 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 27.175 seconds

小结：加大pctfree时,占用的数据块增多。导致update用时更多。

--害怕上述小结不周全，继续加大pctfree，是否会得到与小结相同的结论呢
21:34:24 SQL> truncate table t_storage;

Table truncated

21:36:23 SQL> alter table t_storage pctfree 90;

Table altered

Executed in 0 seconds

--插入用时30秒左右
21:37:51 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_storage values(i);
           4 if mod(i,10000)=0 then
           5    commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 30.061 seconds

--更新用时占用31秒左右
21:38:22 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 31.356 seconds

--我们再以相反的方面测试，减少pctfree为5，大家想想是什么结果
21:39:26 SQL> truncate table t_storage;

Table truncated

Executed in 51.168 seconds

21:41:58 SQL> alter table t_storage pctfree 5;

Table altered

Executed in 0 seconds

--插入用时29秒左右
21:43:12 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_storage values(i);
           4 if mod(i,10000)=0 then
           5    commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 29.655 seconds

--更新用时30秒左右
21:43:41 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 30.592 seconds

小结：看来oracle update效率不能由pctfree一个参数决定，并非说pctfree大了，update就快;返之则慢；
      你看pctfree为5,update用时30秒左右，而pctfree为50,update用时却为27秒左右；再看pctfree为
      90时，update花费了31秒左右；
      （并非pctfree大了，update花费的时间就更少了）

那么update还由哪些因素决定呢？

自上述的测试可知，pctfree越大，占用的数据块越多，即update时扫描的数据块就要越多；
那么要是加大db_file_multiblock_read_count参数的值，是不是就更快了呢

--其参数含义为：每次oracle io可读取的最大数据块个数
21:54:13 SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128

--说明可以直接变更,不用重启库
21:54:24 SQL> alter system set db_file_multiblock_read_count=200;

System altered

Executed in 0.359 seconds

--看看在此参数为100时update性能如何
21:56:19 SQL> alter system set db_file_multiblock_read_count=100;

System altered

Executed in 0.031 seconds

21:56:32 SQL> truncate table t_storage;

Table truncated

Executed in 0.796 seconds

--插入用时29秒左右
21:57:26 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_storage values(i);
           4 if mod(i,10000)=0 then
           5    commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 29.859 seconds

--更新用时20秒左右
21:57:56 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 20.186 seconds

---再看看当其参数为250时，会不会更新性能有个大变脸呢
21:59:24 SQL> truncate table t_storage;

Table truncated

Executed in 1.092 seconds

21:59:30 SQL> alter system set db_file_multiblock_read_count=250;

System altered

Executed in 0.062 seconds

--插入用时28秒左右
22:00:48 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_storage values(i);
           4 if mod(i,10000)=0 then
           5    commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 28.236 seconds

--更新用时26秒左右
22:01:16 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 26.598 seconds

小结：发现加大了其参数，update用时反而增加了。何原因？
1，此参数与硬件的io有直接的关系；
2，此参数增加过大，会否采用某个默认值呢

哪我们把此参数减至一个合理值150,看看表现吧
22:04:55 SQL> alter system set db_file_multiblock_read_count=150;

System altered

Executed in 0.031 seconds

22:05:02 SQL> truncate table t_storage;

Table truncated

Executed in 1.467 seconds

--插入用时30秒左右
22:05:36 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_storage values(i);
           4 if mod(i,10000)=0 then
           5    commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 30.811 seconds

--更新用时26秒左右
22:06:07 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 26.489 seconds

其参数用时
100     20.186
150     26.489
250     26.598

自上述表格可推知，其参数越大，更新用时会更长；不符合我之间的推论啊；
那么是不是其参数越小，更新用时会更短呢

22:07:03 SQL> truncate table t_storage;

Table truncated

Executed in 0.858 seconds

22:12:27 SQL> alter system set db_file_multiblock_read_count=50;

System altered

Executed in 0.14 seconds

--插入用时31秒左右
22:13:42 SQL> begin
           2   for i in 1..1000000 loop
           3   insert into t_storage values(i);
           4   if mod(i,10000)=0 then
           5     commit;
           6   end if;
           7   end loop;
           8   end;
           9 /

PL/SQL procedure successfully completed

Executed in 31.995 seconds

--更新用时36秒左右
22:14:15 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 36.067 seconds

小结：此参数小了，并非更新用时会变短；反而更长了；

--我不放心，继续减少此参数值
22:15:23 SQL> truncate table t_storage;

Table truncated

Executed in 1.138 seconds

22:17:08 SQL> alter system set db_file_multiblock_read_count=30;

System altered

Executed in 0.047 seconds

--插入花费27秒左右
22:18:14 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_storage values(i);
           4 if mod(i,10000)=0 then
           5    commit;
           6 end if;
           7 end loop;
           8 end;
           9 /

PL/SQL procedure successfully completed

Executed in 27.223 seconds
--更新用时20秒左右
22:18:43 SQL> update t_storage set a=88;

1000000 rows updated

Executed in 20.608 seconds