[20130607]行遷移與ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt
[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.測試環境:
--要看到行遷移,要使用analyze命令.大家要注意analyze,與dbms_stats分析AVG_ROW_LEN不一樣.
--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再測試:
-- 不算block=1837的塊.這塊沒有填充滿資訊.
--根據這個情況選擇每塊放279條記錄比較合適.
--設定每塊放置的數量.
--可以發現blocks沒有增加,基本確定沒有行遷移.
3.如何遷移的問題:
實際上正常的移植就是像前面那樣,但是確實比較麻煩,存在一些問題,如果生產系統表很大,很繁忙,這樣肯定不行的.一般要使用線上重定義表.
我下面要講的是我生產系統遇到的問題,就是我已經執行ALTER TABLE t1 MINIMIZE RECORDS_PER_BLOCK ;了命令,依舊出現行遷移,說明每
塊的數量依舊太多. 我不想再像前面的操作,直接操作裡面的基表.實際上使用10046跟蹤,可以發現修改的就是sys.tab$的spare1欄位 .
spare1=32768+每塊的行數-1.注意:修改後要重新整理共享池,簡單一點先執行ALTER TABLE t1 NOMINIMIZE RECORDS_PER_BLOCK;也可以.取消
這個特性.(以下僅僅作為測試,不要在生產系統做這些操作!!!)
--以sys使用者執行如下:(繼續前面的操作)
--這樣由於塊中存在行號超出260的記錄,建立點陣圖索引出現如下錯誤.
--可以發現執行計劃使用了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上討論避免行遷移的方法.想到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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 表、索引遷移表空間alter table move索引
- v$lock之alter table drop column與alter table set unused column區別系列五
- Alter table for ORACLEOracle
- alter table move 和 alter table shrink space的區別
- alter table move 與shrink space的區別
- 【效能優化】消除行遷移 table fetch continued row優化
- table/index/LOBINDEX遷移表空間Index
- 使用prebuilt table 方式遷移資料UI
- Oracle 11g alter table move與shrink spaceOracle
- 【效能最佳化】消除行遷移 table fetch continued row
- mysql的ALTER TABLE命令MySql
- oracle alter table詳解Oracle
- alter table using indexIndex
- 查詢行遷移及消除行遷移(chained rows)AI
- 【備份恢復】行遷移與行連結
- Oracle 行遷移 & 行連結的檢測與消除Oracle
- 行遷移測試
- Oracle資料庫中資料行遷移與行連結Oracle資料庫
- alter table語法增補(一）
- ALTER TABLE MOVE | SHRINK SPACE區別
- 行遷移和行連結
- Oracle行遷移實驗Oracle
- 清除行遷移的例子
- ALTER TABLE MOVE和SHRINK SPACE區別
- 清除行遷移和行連結
- 行遷移(鏈化行)問題
- MySQL alter table時執行innobackupex全備再看Seconds_Behind_MasterMySqlAST
- 行遷移_行連結的介紹
- 如何消除行連結和行遷移
- alter table move跟shrink space的區別
- alter table engine=memory ERROR 1114Error
- [20160726]行連結行遷移與ITL槽.txt
- [20180327]行遷移與ITL浪費.txt
- 實驗：行遷移與分析語句 row migration and analyze statements
- 模擬Oracle行遷移和行連結Oracle
- pctused, pctfree, pctincrease , 行遷移 & 行連結
- 行遷移和行連結的檢測
- 查詢表存在大量行遷移