OGG复制进程延迟高，优化方法三，进程拆分(可以理解为并行) 一、参数说明,测试使用的是19.1 OGG版本参数 二、实操
OGG复制进程延迟高，我们前面讲述的方法及场景是,sql update or delete 操作,修改少量记录但是SQL使用低效的索引甚至是全表扫描，导致复制进程延迟很高，通过创建索引和固定或收集统计信息让Oracle SQL选择好的索引，从而加快复制速度。
但是如果一个表大量的insert 操作呢？？？ insert 走啥索引，只是写数据！！！ 并发很高，如何加快写入呢？？？
本篇文章讲述通过OGG 参数将Oracle 一个表拆分为多个进程，类似并行的套路加快速度。
@RANGE Use the @RANGE function to divide the rows of any table across two or more Oracle GoldenGate processes. It can be used to increase the throughput
of large and heavily accessed tables and also can be used to divide data into sets for distribution to different destinations. Specify each range
in a FILTER clause in a TABLE or MAP statement. @RANGE is safe and scalable. It preserves data integrity by guaranteeing that the same row will always be processed by the same process group. To
ensure that rows do not shift partitions to another process group and that the DML is performed in the correct order, the column on which you base
the @RANGE partitioning must not ever change during a process run. Updates to the partition column may result in "row not found" errors or
unique-constraint errors. @RANGE computes a hash value of the columns specified in the input. If no columns are specified, the KEYCOLS clause of the TABLE or MAP statement
is used to determine the columns to hash, if a KEYCOLS clause exists. Otherwise, the primary key columns are used. Oracle GoldenGate adjusts the total number of ranges to optimize the even distribution across the number of ranges specified. Because any columns can be specified for this function, rows in tables with relational constraints to one another must be grouped together into
the same process or trail to preserve referential integrity. Note: Using Extract to calculate the ranges is more efficient than using Replicat. Calculating ranges on the target side requires Replicat to
read through the entire trail to find the data that meets each range specification.
二、实操
2.1 定位缓慢SQL
套路没变，尽量快速一点。
SQL> select sql_id,count(*) from v$active_session_history where SAMPLE_TIME >sysdate-1 and SESSION_ID=1585 and SESSION_SERIAL#=79
group by sql_id order by 2; SQL_ID COUNT(*) ------------- ---------- gtbj8txbkf9n5 1 1tvzus967pxky 4 d8n0wrf6fmrkz 4 3dbvkp2hd4f85 7 29 43gz5209aq7wv 62 g68nmt0ww3xjm 85973
SQL> select * from table(dbms_xplan.display_cursor('g68nmt0ww3xjm'));
INSERT INTO "CC"."666"
("ID","1","2","3","4","5","S
END_WAY","SEND_VALUE","AC_CODE","AC_NAME","AC_TYPE","REPERTORY_ID","MECH
ANISM_CODE","ACCOUNT_ID","IS_PROVINCE","USER_ID","PHONE","PROVINCE_CODE"
,"ORG_NO","CONS_NO","RECORD_ST ······
观察下SQL是否存在异常event null
SQL> select event,count(*) from v$active_session_history where SAMPLE_TIME >sysdate-1 and SESSION_ID=1585 and SESSION_SERIAL#=79 and sql_id='g68nmt0ww3xjm' group by event order by 2;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
log file switch completion 1
SQL*Net more data from client 2
gc current multi block request 4
enq: FB - contention 4
gc current request 5
gc current grant congested 13
gc current grant 2-way 1045
1090
db file sequential read 83812
9 rows selected.
2.2 观察表涉及的约束名称,是否存在主键。
select owner,constraint_name,constraint_type,table_name from dba_constraints where owner='O' and table_name='I_LOW' OWNER CONSTRAINT_NAME C TABLE_NAME -------------------- ------------------------------ O O_PK P I_LOW O SYS_C0011517 C I_LOW 可以发现ID是表的主键列！！！ select * from dba_cons_columns where owner='O' and table_name='I_LOW' CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION -------------------- ------------------------------ ------------------------------ ------------------------------ ---------- SYS_C0011517 I_LOW ID O_PK I_LOW ID 1
2.3 将表拆分为6个进程！
GGSCI > stop rep_w
GGSCI > info rep_w
Log Read Checkpoint File /ogg/dirdat/666/fj000000082
2020-07-01 13:41:33.002883 RBA 771547003
>dblogin USERID OGG, PASSWORD cc add checkpointtable ogg.chekpoint_rep_1 add checkpointtable ogg.chekpoint_rep_2 add checkpointtable ogg.chekpoint_rep_3 add checkpointtable ogg.chekpoint_rep_4 add checkpointtable ogg.chekpoint_rep_5
GGSCI >add rep rep_f1,exttrail /ogg/dirdat/666/fj checkpointtable ogg.chekpoint_rep_1
GGSCI >edit param rep_f1
replicat rep_f1
setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)
USERID OGG, PASSWORD cc
REPORT AT 01:59
reportrollover at 02:00
DISCARDFILE ./dirrpt/rep_f1.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
GETUPDATEBEFORES
ALLOWDUPTARGETMAP
grouptransops 5000
batchsql opsperbatch 2000 opsperqueue 2000
-- HANDLECOLLISIONS
MAP O.I__W, TARGET O.I_W,filter(@range(1,6,ID)), RESOLVECONFLICT(UPDATEROWMISSING(DEFAULT, OVERWRITE));
GGSCI>alter replicat rep_f1,extseqno 82,extrba 771547003
以此类推， 主要就是加上filter(@range(1,6,column_name)) 如下展示！ 拆分后，OGG延迟00:00:00！！！ OK
MAP O.I__W, TARGET O.I_W,filter(@range(1,6,ID))
MAP O.I__W, TARGET O.I_W,filter(@range(2,6,ID))
MAP O.I__W, TARGET O.I_W,filter(@range(3,6,ID))
MAP O.I__W, TARGET O.I_W,filter(@range(4,6,ID))
MAP O.I__W, TARGET O.I_W,filter(@range(5,6,ID))
MAP O.I__W, TARGET O.I_W,filter(@range(6,6,ID))