[20130607]行遷移與ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt

lfree發表於2013-06-07
[20130607]行遷移與ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt

前一陣子,在ITPUB上討論避免行遷移的方法.想到ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.

當資料行發生大量的行遷移(migrate)時,對其訪問將會造成 I/O 效能降低,因為Oracle為獲取這些資料行的資料時,必須訪問更多的
資料塊(data block)。而一般常規的解決方法就是增加PCTFREE的設定,預留更多的空間給行記錄增長,但是又帶來另外的問題,由於
插入與修改是互動進行的,設定PCTFREE太大,會導致每個資料塊的記錄很少(很快達到PCTFREE的限制),磁碟空間浪費。而設定
PCTFREE太小,依舊會出現行遷移的情況。而使用ALTER TABLE MINIMIZE RECORDS_PER_BLOCK命令,本來這個命令的作用是建立點陣圖索引
時減少點陣圖索引的大小,但同時也限制每個資料塊記錄數的數量,透過這種方式可以在生產系統中很好地解決應用系統中的行遷移問題。


結合自己以前遇到的一個問題,來講解這個命令:

1.測試環境:
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> drop table t purge ;
SQL> create table t as select rownum id ,mod(rownum,100)+1 id1,mod(rownum,200)+1 id2,cast(NULL as varchar2(10)) name from dual connect by level <=2e4;
Table created.

SQL> desc t
Name  Null?    Type
----- -------- -------------
ID             NUMBER
ID1            NUMBER
ID2            NUMBER
NAME           VARCHAR2(10)

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.

SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T';
TABLE_NAME   PCT_FREE   NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T                  10      20000         51            0          0          10

SQL> update t set name='test1test2' ;
20000 rows updated.

SQL> commit ;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.

SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T';
TABLE_NAME   PCT_FREE   NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T                  10      20000        250            0          0          21

--要看到行遷移,要使用analyze命令.大家要注意analyze,與dbms_stats分析AVG_ROW_LEN不一樣.

SQL> analyze table t compute statistics;
SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T';
TABLE_NAME   PCT_FREE   NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T                  10      20000        250            6      11490          28

--SQL> analyze table t list chained rows into chained_rows;
--注: 執行前要建立chained_rows表,最好不要放在system表空間.$ORACLE_HOME/rdbms/admin/utlchain.sql.

2.按照以上情況,要避免出現行遷移,要設定一個非常大的pct_free.(21-10)/21=.523809524,要設定pct_free=53才基本消除行遷移.
而使用ALTER TABLE MINIMIZE RECORDS_PER_BLOCK命令就很簡單.

--pct_free設定5,資料塊8k的資料塊,如何算出每塊應該放最大多少行記錄呢?
--建立一個新表T1再測試:

SQL> create table t1 pctfree 5 as select rownum id ,mod(rownum,100)+1 id1,mod(rownum,200)+1 id2,cast('test1test2' as varchar2(10)) name from dual connect by level <=2e4;
Table created.

SQL> SELECT   a, b, COUNT (*) c
    FROM (SELECT DBMS_ROWID.rowid_block_number (ROWID) a,
                 DBMS_ROWID.rowid_relative_fno (ROWID) b   FROM t1)
GROUP BY a, b ORDER BY a;

         A          B          C
---------- ---------- ----------
      1499          4        293
      1500          4        286
      1501          4        289
....      
      1798          4        277
      1799          4        279
      1801          4        276
      1802          4        278
      1803          4        278
.....
      1832          4        279
      1833          4        277
      1834          4        277
      1835          4        278
      1836          4        276
      1837          4        214

71 rows selected.

-- 不算block=1837的塊.這塊沒有填充滿資訊.

SQL> select min(c),max(c),avg(c) from (
SELECT   a, b, COUNT (*) c
    FROM (SELECT DBMS_ROWID.rowid_block_number (ROWID) a,
                 DBMS_ROWID.rowid_relative_fno (ROWID) b   FROM t1)
  5  GROUP BY a, b ORDER BY a) where c<>214;
    MIN(C)     MAX(C)     AVG(C)
---------- ---------- ----------
       276        293 282.657143

--根據這個情況選擇每塊放279條記錄比較合適.

SQL> drop table t1 purge;
Table dropped.

SQL> create table t1 pctfree 5 as select rownum id ,mod(rownum,100)+1 id1,mod(rownum,200)+1 id2,cast(NULL as varchar2(10)) name from dual connect by level <=279;
279 rows created.

--設定每塊放置的數量.
SQL> ALTER TABLE t1 MINIMIZE RECORDS_PER_BLOCK ;
Table altered.

SQL> delete from t1;
279 rows deleted.

SQL> commit ;
Commit complete.

SQL> insert into t1  select rownum id ,mod(rownum,100)+1 id1,mod(rownum,200)+1 id2,cast(NULL as varchar2(10)) name from dual connect by level <=2e4;
20000 rows created.

SQL> commit ;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.

SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T1';
TABLE_NAME   PCT_FREE   NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T1                  5      20000         76            0          0          10

SQL> update t1 set name='test1test2' ;
20000 rows updated.

SQL> commit ;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.

SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T1';
TABLE_NAME   PCT_FREE   NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T1                  5      20000         76            0          0          21

--可以發現blocks沒有增加,基本確定沒有行遷移.

SQL> analyze table t1 compute statistics;
Table analyzed.

SQL> select table_name,pct_free,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len from dba_tables where wner=user and table_name='T1';
TABLE_NAME   PCT_FREE   NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
T1                  5      20000         76            4          0          25


3.如何遷移的問題:
實際上正常的移植就是像前面那樣,但是確實比較麻煩,存在一些問題,如果生產系統表很大,很繁忙,這樣肯定不行的.一般要使用線上重定義表.

我下面要講的是我生產系統遇到的問題,就是我已經執行ALTER TABLE t1 MINIMIZE RECORDS_PER_BLOCK ;了命令,依舊出現行遷移,說明每
塊的數量依舊太多.  我不想再像前面的操作,直接操作裡面的基表.實際上使用10046跟蹤,可以發現修改的就是sys.tab$的spare1欄位 .
spare1=32768+每塊的行數-1.注意:修改後要重新整理共享池,簡單一點先執行ALTER TABLE t1 NOMINIMIZE RECORDS_PER_BLOCK;也可以.取消
這個特性.(以下僅僅作為測試,不要在生產系統做這些操作!!!)

--以sys使用者執行如下:(繼續前面的操作)
ALTER TABLE scott.t1 NOMINIMIZE RECORDS_PER_BLOCK;
UPDATE SYS.tab$  SET spare1 = 32768+260-1
 WHERE (obj#, dataobj#) IN (SELECT object_id, data_object_id FROM dba_objects   WHERE wner = 'SCOTT' AND object_name = 'T1');
COMMIT ;

--這樣由於塊中存在行號超出260的記錄,建立點陣圖索引出現如下錯誤.
SQL> create bitmap index i_t1_id2 on t1(id2);
create bitmap index i_t1_id2 on t1(id2)
                                *
ERROR at line 1:
ORA-28604: table too fragmented to build bitmap index (16778715,264,264)

--建立b-tree索引沒有問題.
SQL> create  index i_t1_id1 on t1(id1);
Index created.

SQL> create  index i_t1_id2 on t1(id2);
Index created.

SQL> select * from t1 where id1=42 and id2=42;
select * from t1 where id1=42 and id2=42
              *
ERROR at line 1:
ORA-00600: internal error code, arguments: [qerbtRop:rowidIllegal], [], [], [], [], [], [], [], [], [], [], []
--這個就是我生產系統遇到的問題在春節前的事情,而且走這個計劃非常特殊,僅僅在做年報表的時候才執行類似的執行計劃.

--使用提示正常,如下
select /*+ full(t1) */* from t1 where id1=42 and id2=42;
select /*+ index(t1 ,i_t1_id1) */* from t1 where id1=42 and id2=42;
select /*+ index(t1 ,i_t1_id2) */* from t1 where id1=42 and id2=42;

--建立如下索引後也正常.
SQL> create  index i_t1_id1_id2 on t1(id1,id2);
SQL> drop index i_t1_id1_id2;

SQL> explain plan for select * from t1 where id1=42 and id2=42;
Explained.

SQL> @dp
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 2192997210
---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |     1 |    19 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1       |     1 |    19 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |          |       |       |            |          |
|   3 |    BITMAP AND                    |          |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | I_T1_ID2 |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | I_T1_ID1 |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("ID2"=42)
   7 - access("ID1"=42)
20 rows selected.

--可以發現執行計劃使用了BITMAP CONVERSION FROM ROWIDS以及BITMAP AND等操作.
--要徹底解決執行move表,在重新建立索引,問題才能解決,說明不能偷這個懶,生產系統最好不要避免這種非常規操作.

ALTER TABLE T1 MOVE TABLESPACE users;
alter index i_t1_id1 rebuild;
alter index i_t1_id2 rebuild;

SQL> alter index i_t1_id1 rebuild;
Index altered.

SQL> alter index i_t1_id2 rebuild;
Index altered.

SQL> select * from t1 where id1=42 and id2=42;
        ID        ID1        ID2 NAME
---------- ---------- ---------- --------------------
        41         42         42 test1test2
...
--以此文作為回憶.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-763315/,如需轉載,請註明出處,否則將追究法律責任。

相關文章