流同步機制優化（二）

流同步機制建立已經快要一年半了，原本執行五、六個小時的過程，目前執行時間已經需要十多個小時左右。如果不對其進行優化，將會影響正常業務。

流同步機制優化（一）：http://yangtingkun.itpub.net/post/468/309034

上一篇文章以前確定了幾個優化方向，其中方法三的風險相對較小，也比較易於執行。

但是事情並非如此簡單，通過查詢METALINK發現，清除資訊還要面臨兩個問題：

首先是一個bug：Bug 3953131 - Altering first_scn of Streams capture results in ORA-30036 / ORA-1562。

本來問題已經夠複雜的了，就不希望bug再來搗亂，不過根據bug的描述似乎是由於清除的資料量太大造成的。那麼分多次執行，每次清除部分資料，應該可以避免這個問題。

第二個問題是，似乎直接使用DBMS_CAPTURE_ADM包的ALTER_CAPTURE過程對9i不適應。在METALINK文章裡面談到，這個方法可以在10g中使用，而在9i中必須使用Oracle提供的另外一個包。這個包單獨提供下載，而且是加密的。在SYS使用者下執行這個包，給人一種很沒有底的感覺。雖然這個包是METALINK提供的。

觀察指令碼中的註釋資訊，發現如果需要提高過程的執行速度，那麼仍然需要在LOGMNR_RESTART_CKPT$表中增加索引。

既然橫豎要新增索引，不如先按照方案二嘗試優化SQL。如果優化效果明顯，就可以避免執行那個加密的包了。

SQL> CONN SYSTEM
Enter password:
Connected.
SQL> CREATE INDEX IND_LOGM_LOG ON LOGMNR_LOG$(FIRST_CHANGE#, NEXT_CHANGE#);

Index created.

SQL> CREATE INDEX IND_LOGM_REST_CKPT_CKPTVALID ON LOGMNR_RESTART_CKPT$(CKPT_SCN, VALID);

Index created.

SQL> CONN STRMADMIN
Enter password:
Connected.
SQL> EXPLAIN PLAN FOR
2 SELECT DISTINCT (A.CKPT_SCN)
3 FROM SYSTEM.LOGMNR_RESTART_CKPT$ A
4 WHERE A.CKPT_SCN <= :1
5 AND A.VALID = 1
6 AND EXISTS
7 (
8 SELECT *
9 FROM SYSTEM.LOGMNR_LOG$ L
10 WHERE A.CKPT_SCN BETWEEN L.FIRST_CHANGE# AND L.NEXT_CHANGE#
11 )
12 ORDER BY A.CKPT_SCN DESC
13 ;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost |
------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | |
| 1| SORT UNIQUE | | | | |
| 2| NESTED LOOPS | | | | |
| 3| INDEX RANGE SCAN| IND_LOGM_REST_CKPT_CKPTVALID | | | |
| 4| INDEX RANGE SCAN| IND_LOGM_LOG | | | |
-------------------------------------------------------------------------------

Note: rule based optimization, PLAN_TABLE' is old version

12 rows selected.

至此優化目的已經到達，再次執行時執行時間已經不到半個小時。

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