流复制同步机制优化

krrish 2010-05-06

展开全文

流复制同步机制优化

数据库知识学习 2009-04-20 14:11:18 阅读42 评论0 字号：大中小

流复制运行了半年了，现在速度越来越慢，如果不对其进行优化，将会影响正常业务。

首先要定位问题，经检查发现，其主要的时间消耗在一个检查流运行状态的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

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | |

| 1 | SORT UNIQUE | | | | |

| 2 | NESTED LOOPS | | | | |

------------------------------------------------------------------------------

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，和流同步本身关系不是很大，直接去掉也不会造成太大的影响。不过，这个状态检查信息还是比较有用的，只要是有优化的可能，还是尽可能保留，这只是最后迫不得已的办法。

二、优化这个查询：

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

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

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

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

三、减少数据量

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

设置方法：exec DBMS_CAPTURE_ADM.SET_PARAMETER ('STREAM_CAPTURE', '_checkpoint_frequency','100');

exec dbms_capture_adm.alter_capture(capture_name =>'STREAM_CAPTURE',CHECKPOINT_RETENTION_TIME=>10);

收缩空间
alter table system.LOGMNR_RESTART_CKPT$ enable row movement;
alter table system.LOGMNR_RESTART_CKPT$ shrink space ;
alter table system.LOGMNR_RESTART_CKPT$ disable row movement;