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

綜合： db_file_multiblock_read_count引數並非決定update的效能；
       其引數必定有個合理值，對於oracle；如不在其範圍內，則
       減少或增加皆可能會有損update的效能；

pct_free_pct_used,ini_trans之相關引數系統(一)

相關文章