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

首先需要定位最主要的问题，经过检查发现，流的应用阶段占用的时间并不长，和以前相比也没有很明显的变化。而主要的时间消耗在一个检查流运行状态的SQL上。

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 | TABLE ACCESS FULL | LOGMNR_RESTART_CKPT$ | | | |
| 4 | TABLE ACCESS FULL | LOGMNR_LOG$ | | | |
------------------------------------------------------------------------------

Note: rule based optimization, PLAN_TABLE' is old version

12 rows selected.

从执行计划上可以看出，NESTED LOOP连接且两个表均为全表扫描，等价于一个笛卡儿积。

而且两张表的数据量都不小：

SQL> SELECT COUNT(*) FROM SYSTEM.LOGMNR_RESTART_CKPT$;

COUNT(*)
----------
4120512

SQL> SELECT COUNT(*) FROM SYSTEM.LOGMNR_LOG$;

COUNT(*)
----------
10230

SQL> SELECT 4120512*10230 FROM DUAL;

4120512*10230
-------------
4.2153E+10

正是由于大数据量加上很不理想的执行计划，导致这个SQL成为整个流同步机制的瓶颈。目前，这个SQL的执行已经需要八、九个小时。

这个SQL是一个流状态检查过程的一部分，是METALINK上提供的。怀疑作者在写这个SQL的时候没有考虑性能的因素，或者没有考虑数据量增长的情况。

现在问题变得比较简单了，只需要优化这个SQL就可以了。

简单分析之后，存在以下几种办法：

一、去掉这部分SQL语句：由于是状态检查SQL，和流同步本身关系不是很大，直接去掉也不会造成太大的影响。不过，这个状态检查信息还是比较有用的，只要是有优化的可能，还是尽可能保留，这只是最后迫不得已的办法。

二、优化这个查询：

优化查询也有很多方案：比如重写这个查询，改变优化模式为CBO等。

这个查询已经很简单了，而且查询连接条件都不是单纯的等于连接，所以重写查询的意义不大。由于没有可用的索引，改变优化模式的意义也不大，最多是将NESTED LOOP改变为MERGE JOIN。

目前主要问题是执行计划的问题，两张表进行NESTED LOOP，且都是全表扫描。如果建立合适的索引是的索引扫描来替代全表扫描，应该可以较大的提高性能。

但是对SYSTEM用户的数据字典表建立索引，仍然是一个相对比较危险的动作。

三、减少数据量

这两张表的数据库目前很大，尤其是SYSTEM.LOGMNR_RESTART_CKPT$表，数据量已经达到了400万，而这张表记录的是自STREAM建立以来所有的CHECKPOINT信息。通过减少表中数据量的方法，同样可以使得查询速度提高。