ogg replicat同步很慢

lypflash

GGSCI (RT3-MES-DATABASE) 369> !
info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     R_T_SJ1     17:05:36      00:00:00   


GGSCI (RT3-MES-DATABASE) 370> lag r_t_sj1

Sending GETLAG request to REPLICAT R_T_SJ1 ...
Last record lag: 61501 seconds.

检查target端数据库，很空闲，
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 5153 02-Aug-13 05:00:16 54 3.5
End Snap: 5154 02-Aug-13 06:00:18 51 3.8
Elapsed:   60.03 (mins)     
DB Time:   144.69 (mins)     

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time   8,561   98.6   
db file scattered read 8,750,922 474 0 5.5 User I/O
db file sequential read 155,511 128 1 1.5 User I/O
log file parallel write 3,487 48 14 .6 System I/O
log file sync 1,343 28 21 .3 Commit

为了优化replicat进程，新增参数
BATCHSQL，
但并没有什么效果，请刘大帮忙分析下

lypflash

GGSCI (RT3-MES-DATABASE) 373> send R_T_SJ1  status

Sending STATUS request to REPLICAT R_T_SJ1 ...
  Current status: Processing data
  Sequence #: 1663
  RBA: 238129306
  258 records in current transaction

lypflash

问题解决，是SQL语句造成同步缓慢

lypflash

具体解决办法：
1，查找是什么样的SQL

select sid||' '||event from v$session where event not like 'SQL%' AND EVENT not like 'rdbms%';

select sql_text,sql_id from v$sqlarea where address in (select sql_address from v$session where sid=5437)

2,查看长SQL的执行计划，发现执行计划很好，但不是正确的。
select sql_text, sql_id, sql_profile, executions,child_number, plan_hash_value
  from v$sql where sql_id='6ctbmfmnmcsnf'
3，绑定执行计划使用SQLT脚本。
4，问题解决