行鏈接和行遷移

發布時間：2020-08-09 12:00:39 來源：ITPUB博客閱讀：167 作者：張沖andy 欄目：關系型數據庫

一、概述：
  如果你的Oracle數據庫性能低下，行鏈接和行遷移可能是其中的原因之一。我們能夠通過合理的設計或調整數據庫來阻止這個現象。

  行鏈接和行遷移是能夠被避免的兩個潛在性問題。我們可以通過合理的調整來提高數據庫性能。本文主要描述的是：
    什么是行遷移與行鏈接
    如何判斷行遷移與行鏈接
    如何避免行遷移與行鏈接

  當使用索引讀取單行時，行遷移影響OLTP系統。最糟糕的情形是，對所有讀取操作而言，增加了額外的I/O。行鏈接則影響索引讀和全表掃描。

  注：在翻譯行(row)時使用記錄來描述(便于理解)，如第一行，使用第一條記錄。

二、Oralce 塊
    操作系統塊的大小是操作系統讀寫的最小操作單元，也是操作系統文件的屬性之一。當創建一個數據庫時，選擇一個基于操作系統塊的
  整數倍大小作為Oracle數據庫塊的大小。Oracle數據庫讀寫操作則是以Oracle塊為最小單位，而非操作系統塊。一旦設置了Oracle數據塊的大小，
  則在整個數據庫生命期間不能被更改(除 Oracle 9i之外)。因此為Oracle數據庫定制合理的Oralce塊大小，象預期數據庫總大小以及并發用戶數這些
  因素應當予以考慮。
  數據庫塊由下列邏輯結構(在整個數據庫結構下)

SELECT x,d,e FROM row_mig_chain_demo WHERE x = 3;
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 2
--現在當我們通過主鍵索引掃描從記錄3的尾部提取數據時，這將增加table fetch continued row的值。因為需要從行的頭部和尾部獲取數據來組合。
--現在來看看全表掃描是否也有相同的影響。
SELECT * FROM row_mig_chain_demo;
X
----------
3
2
1
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 3
--此時table fetch continued row的值被增加，因為不得不對記錄3的尾部進行融合。而記錄1和2即便是存在遷移現象，但由于是全表掃描，
--因此不會增加table fetch continued row的值。
SELECT x,a FROM row_mig_chain_demo;
X
----------
3
2
1
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 3
--當需要提取的數據是整個表上的頭兩列的時候，此時table fetch continued row也不會增加。因為不需要對記錄3進行數據融合。
SELECT x,e FROM row_mig_chain_demo;
X
----------
3
2
1
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 4
--但是當提取列d和e的時候，table fetch continued row的值被增加。通常查詢時容易產生行遷移即使是真正存在行鏈接，因為我們的查詢
--所需的列通常位于表的前幾列。

八、如何鑒別行鏈接和行遷移

--聚合統計所創建的表，這也將使得重構整行而發生table fetch continued row
SELECT count(e) FROM row_mig_chain_demo;
COUNT(E)
----------
1
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 5
--通過analyze table來校驗表上的鏈接數
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
SELECT chain_cnt
FROM user_tables
WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
CHAIN_CNT
----------
3
--3條記錄是鏈接的。顯然，他們中的兩條記錄是遷移(記錄1，記錄2)和一記錄是鏈接(記錄3).
--實例啟動后的table fetch continued row的總數
--視圖v$mystat告訴我們自從實例啟動后，所有的表上共有多少次為table fetch continued row.
sqlplus system/<password>
SELECT 'Chained or Migrated Rows = '||value
FROM v$sysstat
WHERE name = 'table fetch continued row';
Chained or Migrated Rows = 31637
--上面的查詢結果表明，可能有1個表上存在行鏈接被fetch了31637次，也可能有31637個表，每個表上有一個行鏈接，每次fetch一次。也有
--可能是上述情況的組合。
--31637次也許是好的，也許是壞的，僅僅是一個值而已。
--這取決于
--數據庫啟動了多久？
--這個值占總提取數據百分比的多少行？
--假如它占據了你從表上fetch的0.001%，則無關緊要。
--因此，比較table fetch continued row與總提取的記錄數是有必要的
SELECT name,value FROM v$sysstat WHERE name like '%table%';
NAME VALUE
---------------------------------------------------------------- ----------
table scans (short tables) 124338
table scans (long tables) 1485
table scans (rowid ranges) 0
table scans (cache partitions) 10
table scans (direct read) 0
table scan rows gotten 20164484
table scan blocks gotten 1658293
table fetch by rowid 1883112
table fetch continued row 31637
table lookup prefetch client count 0

九、一個表上鏈接的行是多少？

--通過對表analyze后(未analyze是空值),可以從數據字典user_tales獲得鏈接的記錄數。
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
SELECT chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained,
avg_row_len, pct_free , pct_used
FROM user_tables
WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
---------- ----------- ----------- ---------- ----------
3 100 3691 10 40
--PCT_CHAINED 為100%,表明所有的行都是鏈接的或遷移的。

十、列出鏈接行

  當使用analyze table中的list chained rows子句能夠列出一個表上的鏈接行。該命令的結果是將所有的鏈接上存儲到一個由list chained rows子句
  顯示指定的表中。這些結構有助于決定是否將來有足夠的空間實現行更新。

  創建CHAINED_ROWS 表
  創建一個用于存儲analyze ... list chained rows命令結果的表，可以執行位于$ORACLE_HOME/rdbms/admin目錄下的UTLCHAIN.SQL或UTLCHN1.SQL 腳本。
  這個腳本會在當前schema下創建一個名為chained_rows的表
    create table CHAINED_ROWS (
      owner_name         varchar2(30),
      table_name         varchar2(30),
      cluster_name       varchar2(30),
      partition_name     varchar2(30),
      subpartition_name varchar2(30),
      head_rowid         rowid,
      analyze_timestamp date
    );

當chained_rows表創建后，可以使用analyze table命令來指向該表作為輸出。

十一、如何避免行鏈接和行遷移
      增加pctfree能夠幫助避免行鏈接。如果我們為塊留下更多的可用空間，則行上有空間滿足將來的增長。也可以對那些有較高刪除率的表采用重新組織
  或重建表索引來避免行鏈接與行遷移。如果表上有些行被頻繁的刪除，則數據塊上會有更多的空閑空間。當被插入的行后續擴展，則被插入的行可能會
  分布到那些被刪除的行上而仍然沒有更多空間來用于擴展。重新組織表則確保主要的空閑空間是完整的空塊。

      ALTER TABLE ... MOVE 命令允許對一個未分區或分區的表上的數據進行重新分配到一個新的段。也可以分配到一個有配額的不同的表空間。該命令也允許
  你在不能使用alter table的情形下來修改表或分區上的一些存儲屬性。也可以使用ALTER TABLE ... MOVE 命令中的compress關鍵字在存儲到新段時使用壓縮選項。

[sql] view plain copy

<code class="language-sql">1. ALTER TABLE MOVE
使用alter table move 之前首先統計每個塊上的行數.
SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
FROM row_mig_chain_demo
GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
Block-Nr Rows
---------- ----------
2066 3
--現在消除表上的行鏈接，使用alter table move來重建row_mig_chain_demo表到一個新段，指定一些新的存儲選項。
ALTER TABLE row_mig_chain_demo MOVE
PCTFREE 20
PCTUSED 40
STORAGE (INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0);
Table altered.
--在alter table move之后再次統計每一塊上的行數
SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
FROM row_mig_chain_demo
GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
Block-Nr Rows
---------- ----------
2322 1
2324 1
2325 1
2. 重建表上的索引
--移動一個表將使得表上記錄的rowid發生變化。這將引起表上的索引被置為unusable狀態。基于該表使用索引的DML語句將收到ORA-01502 錯誤。
--因此表上的索引必須被刪除或重建。同樣地，表上的統計信息也會變得無效。因此統計信息在表移動之后也應當重新收集。
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
ERROR at line 1:
ORA-01502: index 'SCOTT.SYS_C003228' or partition of such index is in unusable state
--表上的主鍵必須被重建
ALTER INDEX SYS_C003228 REBUILD;
Index altered.
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
Table analyzed.
SELECT chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained,
avg_row_len, pct_free , pct_used
FROM user_tables
WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
---------- ----------- ----------- ---------- ----------
1 33.33 3687 20 40
--如果表包含LOB 列，用戶可以指定該命令連同LOB數據段以及LOB索引段(同該表相關)一起移動(move)。
--當未指定時，則LOB數據段以及LOB索引段不參與移動。
</code>

十二、檢測所有表上的行連接與行遷移

可以通過CHAINED_ROWS 表獲取所有表上的行鏈接與行遷移。

1.創建chained_rows表
cd $ORACLE_HOME/rdbms/admin
sqlplus scott/tiger
@utlchain.sql
2.ananlyze 所有表/或指定表
SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS INTO CHAINED_ROWS;'
FROM user_tables
/
ANALYZE TABLE ROW_MIG_CHAIN_DEMO LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE DEPT LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE EMP LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE BONUS LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE SALGRADE LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE DUMMY LIST CHAINED ROWS INTO CHAINED_ROWS;
Table analyzed.
3.查看行鏈接的rowid
SELECT owner_name,
table_name,
count(head_rowid) row_count
FROM chained_rows
GROUP BY owner_name,table_name
/
OWNER_NAME TABLE_NAME ROW_COUNT
------------------------------ ------------------------------ ----------
SCOTT ROW_MIG_CHAIN_DEMO 1

通過該方式可以快速的定位一個表上有多少行鏈接問題的。如果行鏈接或行遷移較多，則應當基于該表增加pctfree的值或重建該表。

十三、結論：
   行遷移影響OLTP系統使用索引讀取單行。最糟糕的情形所對所有的讀都增加額外的I/O。而行鏈接則影響索引讀和全表掃描。
   行遷移通常由update操作引起
   行鏈接通常有insert操作引起
   基于行鏈接或行遷移的查詢或創建(如索引)由于需要更多的I/O將降低數據庫的性能
   調試行鏈接或行遷移使用analyze 命令，查詢v$sysdate視圖
   移出行鏈接或行遷移使用更大的pctfree參數或使用alter table move命令

十四、關于作者

原文鏈接： The Secrets of Oracle Row Chaining and Migration

Martin Zahn, Akadia AG, Information Technology, CH-3672 Oberdiessbach
EMail: martin dot zahn at akadia dot ch

12.09.2007: Updated for Oracle 10.2

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

行鏈接和行遷移

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

行鏈接和行遷移

猜你喜歡

最新資訊

相關推薦

相關標簽