goldengate 目的端replicat程式 執行緩慢的問題

goldengate 目的端replicat程式 執行緩慢的問題

現實中的時間已經過了1個小時了，但是去看goldengate的replicat 檢查點（info replicat 程式名）卻向前移動了不到1分鐘。


解決步驟：
1.ps -ef |grep /u02/ggs獲得 replicat 程式的程式id， 假設查詢出來的程式id為12345

2. ps -ef | grep 12345，獲得goldengate 執行復制的程式的程式id（主要是看beq連線），假如此程式的id是12000

3.使用語句查詢出12000的程式，正在做什麼。。

使用sql查詢出正在執行的sql
SELECT sql_text FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID =( select s.sid from v$session s,v$process p where s.paddr=p.addr and p.spid='31903')) ORDER BY piece ASC SELECT sql_text FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID =( select s.sid from v$session s,v$process p where s.paddr=p.addr and p.spid='31903')) ORDER BY piece ASC SELECT sql_text FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID =( select s.sid from v$session s,v$process p where s.paddr=p.addr and p.spid='31903')) ORDER BY piece ASC SELECT sql_text FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID =( select s.sid from v$session s,v$process p where s.paddr=p.addr and p.spid='31903')) ORDER BY piece ASC SELECT sql_text FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID =( select s.sid from v$session s,v$process p where s.paddr=p.addr and p.spid='31903')) ORDER BY piece ASC


4. goldengate 執行的sql如下（被加了/*+ RESTRICT_ALL_REF_CONS */） ：


UPDATE /*+ RESTRICT_ALL_REF_CONS */ "JNRKXX"."ZK_ZZRK"

SET "ZXXID1" = :a0,"ZK_BXH" = :a1,"ZK_ZKYRID" = :a2,"ZK_GMSFHM" = :a3,"ZK_XM" = :a4,"ZK_XB" = :a5,"ZK_CSRQ" = :a6,
"ZK_MZ" = :a7,"ZK_WHCD" = :a8,"ZK_SG" = :a9,"ZK_HYZK" = :a10,"ZK_FWCSID" = :a11,"ZK_FWCS" = :a12,
"ZK_ZY" = :a13,"ZK_DQLX" = :a14,"ZK_CKXZQH" = :a15,"ZK_HJDXZ" = :a16,"ZK_YZBM" = :a17,"ZK_TMTZ" = :a18,
"ZK_LXFS" = :a19,"ZK_YHKLB" = :a20,"ZK_ZZSY" = :a21,"ZK_ZZCS" = :a22,"ZK_JSZH" = :a23,"ZK_JSZSFSY" = :a24,
"ZK_ZZDZXQID" = :a25,"ZK_ZZDZXQ" = :a26,"ZK_ZZDZID" = :a27,"ZK_ZZDZXZ" = :a28,"ZK_WGDZXQID" = :a29,
"ZK_WGDZXQ" = :a30,"ZK_WGDZID" = :a31,"ZK_WGDZXZ" = :a32,"ZK_FZGX" = :a33,"ZK_FZYYID" = :a34,
"ZK_FZGMSFHM" = :a35,"ZK_FZXM" = :a36,"ZK_ZRRFL" = :a37,"ZK_ZZRYRID" = :a38,"ZK_ZZRXM" = :a39,
"ZK_ZZRGMSFHM" = :a40,"ZK_ZZRXB" = :a41,"ZK_ZZRLXFS" = :a42,"ZK_ZZBH" = :a43,"ZK_ZZZQFRQ" = :a44,
"ZK_ZZZYXQX" = :a45,"ZK_ZZZJZRQ" = :a46,"ZK_SFBZ" = :a47,"ZK_LJRQ" = :a48,"ZK_FHSJ" = :a49,
"ZK_HHSJ" = :a50,"ZK_HCQK" = :a51,"ZK_HCLXFS" = :a52,"ZK_SHDW" = :a53,"ZK_ZXYY" = :a54,
"ZK_ZXRQ" = :a55,"ZK_ZXQX" = :a56,"ZK_TBR" = :a57,"ZK_TBRQ" = :a58,"ZK_TBDWID" = :a59,
"ZK_TBDWBH" = :a60,"ZK_TBDW" = :a61,"ZK_HCQTQK" = :a62,"ZK_RXCJZT" = :a63,"ZK_FHZT" = :a64,
"ZK_FHBH" = :a65,"ZK_XQBH" = :a66,"ZK_SHDWID" = :a67,"ZK_SFZZP" = :a68,"ZK_BM" = :a69,
"ZK_NL" = :a70,"ZK_XMPY" = :a71,"ZK_RYLB" = :a72,"ZK_FWCSBH" = :a73,"ZK_BZLB" = :a74,
"ZK_ZZDZ" = :a75,"ZK_WGDZ" = :a76,"ZK_TBRYRID" = :a77,"ZK_ZKXM" = :a78,"ZK_ZKFZXM" = :a79,
"ZK_ZKZRRXM" = :a80,"ZK_FZLXDH" = :a81,"ZK_ZP" = :a82,"ZK_SHDWBH" = :a83,"ZK_WGDZXQBM" = :a84,
"INSERTORUPDATE" = :a85,"ZK_ZZMM" = :a86,"ZK_SFZX" = :a87,"ZK_SBST" = :a88,"ZK_GLDW" = :a89,
"ZK_GLDWID" = :a90,"ZK_GLDWBM" = :a91,"ZK_TC" = :a92,"ZK_LXSJLY" = :a93,"ZK_XXLY" = :a94


WHERE "ZXXID1" is NULL AND "ZK_BXH" = :b1 AND "ZK_ZKYRID" = :b2 AND "ZK_GMSFHM" = :b3
AND "ZK_XM" = :b4 AND "ZK_XB" = :b5 AND "ZK_CSRQ" = :b6 AND "ZK_MZ" = :b7
AND "ZK_WHCD" = :b8 AND "ZK_SG" = :b9 AND "ZK_HYZK" = :b10 AND "ZK_FWCSID" = :b11
AND "ZK_FWCS" is NULL AND "ZK_ZY" is NULL AND "ZK_DQLX" = :b14 AND "ZK_CKXZQH" = :b15
AND "ZK_HJDXZ" = :b16 AND "ZK_YZBM" is NULL AND "ZK_TMTZ" is NULL AND "ZK_LXFS" is NULL
AND "ZK_YHKLB" is NULL AND "ZK_ZZSY" = :b21 AND "ZK_ZZCS" = :b22 AND "ZK_JSZH" is NULL
AND "ZK_JSZSFSY" is NULL AND "ZK_ZZDZXQID" = :b25 AND "ZK_ZZDZXQ" = :b26 AND "ZK_ZZDZID" = :b27
AND "ZK_ZZDZXZ" = :b28 AND "ZK_WGDZXQID" = :b29 AND "ZK_WGDZXQ" is NULL AND "ZK_WGDZID" = :b31
AND "ZK_WGDZXZ" is NULL AND "ZK_FZGX" = :b33 AND "ZK_FZYYID" = :b34 AND "ZK_FZGMSFHM" = :b35
AND "ZK_FZXM" = :b36 AND "ZK_ZRRFL" is NULL AND "ZK_ZZRYRID" = :b38 AND "ZK_ZZRXM" is NULL
AND "ZK_ZZRGMSFHM" is NULL AND "ZK_ZZRXB" is NULL AND "ZK_ZZRLXFS" is NULL AND "ZK_ZZBH" = :b43
AND "ZK_ZZZQFRQ" = :b44 AND "ZK_ZZZYXQX" = :b45 AND "ZK_ZZZJZRQ" = :b46 AND "ZK_SFBZ" = :b47
AND "ZK_LJRQ" = :b48 AND "ZK_FHSJ" is NULL AND "ZK_HHSJ" is NULL AND "ZK_HCQK" is NULL
AND "ZK_HCLXFS" is NULL AND "ZK_SHDW" is NULL AND "ZK_ZXYY" = :b54 AND "ZK_ZXRQ" is NULL
AND "ZK_ZXQX" is NULL AND "ZK_TBR" = :b57 AND "ZK_TBRQ" = :b58 AND "ZK_TBDWID" is NULL
AND "ZK_TBDWBH" = :b60 AND "ZK_TBDW" = :b61 AND "ZK_HCQTQK" is NULL AND "ZK_RXCJZT" = :b63
AND "ZK_FHZT" = :b64 AND "ZK_FHBH" is NULL AND "ZK_XQBH" = :b66 AND "ZK_SHDWID" = :b67
AND "ZK_SFZZP" is NULL AND "ZK_BM" is NULL AND "ZK_NL" is NULL AND "ZK_XMPY" = :b71
AND "ZK_RYLB" = :b72 AND "ZK_FWCSBH" is NULL AND "ZK_BZLB" is NULL AND "ZK_ZZDZ" is NULL
AND "ZK_WGDZ" is NULL AND "ZK_TBRYRID" is NULL AND "ZK_ZKXM" is NULL AND "ZK_ZKFZXM" is NULL
AND "ZK_ZKZRRXM" is NULL AND "ZK_FZLXDH" = :b81 AND "ZK_ZP" = :b82 AND "ZK_SHDWBH" is NULL
AND "ZK_WGDZXQBM" is NULL AND "INSERTORUPDATE" is NULL AND "ZK_ZZMM" is NULL
AND "ZK_SFZX" is NULL AND "ZK_SBST" = :b88 AND "ZK_GLDW" is NULL AND "ZK_GLDWID" is NULL
AND "ZK_GLDWBM" is NULL AND "ZK_TC" is NULL AND "ZK_LXSJLY" is NULL AND "ZK_XXLY" is NULL
AND ROWNUM = :"SYS_B_0"


''5.結合網上的帖子說，dba_objects 中看到此表的num_rows是0行，而昨晚匯入了680萬行。想到這裡，就明白了，執行計劃錯誤，而導致了update被加hint，於是stop goldengate的複製程式，exec dbms_stats.gather_table_stats('user_name','table_name')重新收集此表的統計資訊，然後啟動goldengate的複製程式，之後，goldengate複製程式的檢查點推進就比較正常了。[@more@]