流同步机制优化（二）

流同步机制建立已经快要一年半了，原本执行五、六个小时的过程，目前执行时间已经需要十多个小时左右。如果不对其进行优化，将会影响正常业务。

流同步机制优化（一）：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 | | | | |
IXDBA.NET技术社区
| 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.

至此优化目的已经到达，再次运行时执行时间已经不到半个小时。