OGG復(fù)制進(jìn)程延遲不斷增長(zhǎng)怎么辦

小編給大家分享一下OGG復(fù)制進(jìn)程延遲不斷增長(zhǎng)怎么辦，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

成都創(chuàng)新互聯(lián)專(zhuān)注為客戶(hù)提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站設(shè)計(jì)制作、做網(wǎng)站、商水網(wǎng)絡(luò)推廣、重慶小程序開(kāi)發(fā)、商水網(wǎng)絡(luò)營(yíng)銷(xiāo)、商水企業(yè)策劃、商水品牌公關(guān)、搜索引擎seo、人物專(zhuān)訪(fǎng)、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；成都創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供商水建站搭建服務(wù)，24小時(shí)服務(wù)熱線(xiàn)：13518219792，官方網(wǎng)址：www.aaarwkj.com

-bash-3.2$ ogg

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO

Solaris, sparc, 64bit (optimized), Oracle 11g on Apr 22 2013 15:23:39

GGSCI (ODSDB) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

JAGENT STOPPED

REPLICAT RUNNING RP10 00:00:00 00:00:03

REPLICAT RUNNING RP7 00:00:06 00:00:03

REPLICAT RUNNING RPS1 42:41:14 00:00:01

REPLICAT RUNNING RPS2 00:00:00 00:00:10

REPLICAT RUNNING RPS2A 00:00:00 00:00:00

REPLICAT RUNNING RPS2B 00:00:00 00:00:00

REPLICAT RUNNING RPS3 00:00:00 00:00:02

REPLICAT RUNNING RPS3A 00:00:00 00:00:03

REPLICAT RUNNING RPS3B 00:00:00 00:00:02

REPLICAT RUNNING RPS4 00:00:00 00:00:07

REPLICAT RUNNING RPS4A 00:00:00 00:00:07

REPLICAT RUNNING RPS4B 00:00:00 00:00:07

REPLICAT RUNNING RPS4C 00:00:00 00:00:07

REPLICAT RUNNING RPS4D 00:00:00 00:00:08

REPLICAT RUNNING RPS4E 00:00:00 00:00:07

REPLICAT RUNNING RPS4F 00:00:00 00:00:07

REPLICAT RUNNING RPS4G 00:00:00 00:00:07

REPLICAT RUNNING RPS4H 00:00:00 00:00:07

REPLICAT RUNNING RPS4I 00:00:00 00:00:08

REPLICAT RUNNING RPS4J 00:00:00 00:00:07

跟他去了終端旁邊，發(fā)現(xiàn)確實(shí)有個(gè)rps1進(jìn)程延遲有點(diǎn)高?？粗M(jìn)程狀態(tài)是RUNNING，但是根據(jù)我維護(hù)六年ogg來(lái)看，有可能是假象，那么怎么判斷呢？

1，查看ggserr.log

2，查看dirrpt下面該進(jìn)程的dsc文件

3，info 進(jìn)程名多次，看看RBA是否有變化

經(jīng)過(guò)以上幾步rps1進(jìn)程狀態(tài)是正常的，延遲高問(wèn)題出在哪里？view params rps1看到該進(jìn)程只有一張表。那我們看看這個(gè)進(jìn)程到底卡在哪里了

SQL> !ps -ef |grep rps1 |grep -v grep

orao 12169 11012 0 Jun 07 ? 614:58 /odogg/baseogg/replicat PARAMFILE /odsogg/baseogg/dirprm/rps1.prm REPORTFILE /

SQL> !ps -ef |grep 12169 |grep -v grep

orao 12170 12169 1 Jun 07 ? 2315:30 oracleodb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

orao 12169 11012 0 Jun 07 ? 614:58 /odogg/baseogg/replicat PARAMFILE /odsogg/baseogg/dirprm/rps1.prm REPORTFILE /

SQL> select s.sid,s.SERIAL#,sql_id from v$process p,v$session s where p.addr=s.paddr and p.spid=12170;

SID SERIAL# SQL_ID

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

5146 113 5099dwmx3s4mf

SQL> /

SID SERIAL# SQL_ID

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

5146 113 dzubcf8jm69yx

SQL> /

SID SERIAL# SQL_ID

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

5146 113 dzubcf8jm69yx

SQL> /

SID SERIAL# SQL_ID

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

5146 113 dzubcf8jm69yx

詢(xún)問(wèn)了一下維護(hù)過(guò)該系統(tǒng)的同事，說(shuō)是不是統(tǒng)計(jì)信息太舊。順著思路往下走走

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where table_name='TF_F_USER_REVGRPTAG';

OWNER TABLE_NAME LAST_ANALYZED

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

TTT TF_F_USER_REXXX 2017-06-26 00:20:56

SQL> select table_name,num_rows from dba_tables where owner='TTT' and table_name='TF_F_USER_REXXX';

TABLE_NAME NUM_ROWS

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

TF_F_USER_REXXX 409269832

SQL> select count(*) from TTT.TF_F_USER_REXXX;

COUNT(*)

----------

409955510

接下來(lái)我們看看該sql執(zhí)行計(jì)劃是不是走錯(cuò)了？

OGG復(fù)制進(jìn)程延遲不斷增長(zhǎng)怎么辦

兩條執(zhí)行計(jì)劃，很明顯137632316運(yùn)行了5400次但是執(zhí)行時(shí)間卻遠(yuǎn)遠(yuǎn)高于第二個(gè)執(zhí)行計(jì)劃。

OGG復(fù)制進(jìn)程延遲不斷增長(zhǎng)怎么辦

用sql profile綁定一下執(zhí)行計(jì)劃

SQL> @coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

Enter value for 1: dzubcf8jm69yx

PLAN_HASH_VALUE AVG_ET_SECS

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

2320424968 .004

137632316 21.119

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 2320424968

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID : "dzubcf8jm69yx"

PLAN_HASH_VALUE: "2320424968"

查看是否綁定成功

SQL>select name from dba_sql_profiles;

NAME

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

coe_dzubcf8jm69yx_2320424968

SYS_SQLPROF_015d075ccdd40000

coe_a5hgtqfq09tcu_229988255

在將內(nèi)存中的該sql語(yǔ)句清除出去

SQL>select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'UPDATE "TTT"."TF_F_USER_RExxxx" SET "PARTITION_ID" = :a27,"USER_ID" = :a28%';

ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS

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

00000017673EA5E8 4198240878 100 0

00000017CF3E0000 897328139 673 0

00000017982D40D0 1588244011 8 0

000000049D04B1A8 590555101 10 0

00000017AA525230 4271852353 2 0

0000001777FC6A20 3666847391 735 0

00000017B9154A58 1180290247 1 0

7 rows selected.

SQL>alter session set events '5614566 trace name context forever';

Session altered.

SQL>exec dbms_shared_pool.purge('00000017673EA5E8,4198240878','C');