OGG複製程式延遲高，優化方法一(使用索引)

綠茶有點甜發表於2020-08-10

日常運維過程中，可能發現OGG同步程式延遲很高；

本篇介紹其中的一種方式。

OGG複製程式，或者說同步程式及通過解析ogg trail檔案，輸出dml語句，在目標庫執行dml操作，那麼延遲高可能性其一、執行dml操作效率太低。本篇不考慮併發過高或其它原因。本次只考慮是執行update or delete的時候SQL效率執行太差！

導致OGG複製程式延遲很高。

GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                                                                    

REPLICAT    RUNNING     RP10        00:00:00      00:00:03  
延遲說明參考
https://www.onekbit.com/ViewBlog/blog/BID20200408100342

一、Time Since Checkpoint 過高

指ogg的extract或replicat程式產生最近的一個檢查點，再從這個檢查點到目前為止有多長時間沒有更新了，即最近一個檢查點與當前系統時間的時間差。該值可以通過info看到是在不斷變化(特別是當處理長會話時，會持續增長，直到處理完該長會話)。

對於複製程式來說，如果Time Since Chkpt 延遲有2個小時，說明這個程式存在2個小時檢查點未更新，也就說明有一個或多個組合成的大事務，執行了2個小時，並未執行成功？？？

正常情況下，OGG遇到異常報錯，導致OGG程式中斷Time Since Chkpt 50個小時後，解決報錯後，啟動該程式，一般來說2分內，會執行成功最少一個事務，會寫入新的檢查點，延遲的50個小時，會自動轉換為lag at chkpt 50小時延遲。

異常情況或者說需要優化調整的情況是，啟動程式，發現time since chkpt 延遲一直遞增，不減少。說明存在事務未執行完畢。

實際遇到的情況1，程式同步4個表，其中一個表很大10G，並且目標端無主鍵！！！

因此目標端執行一條update sql執行效率非常低。如何處理？？？

1.表存在主鍵
select   *   from   user_cons_columns

  where   constraint_name   =   (select   constraint_name   from   user_constraints

              where   table_name   =   'BST_FAVORITE'  and   constraint_type   ='P');   
2.對OGG複製程式新增引數指定主鍵列
map source_owner.table_name target target_owner.table_name;
新增引數

map source_owner.table_name target target_owner.table_name，keycols(primary_column_name);

3.對於不存在主鍵的表呢？？？
select count(*) from xxx; 得到表的數量，如果表很大，不執行最好。
通過dba_tab_columns 根據NUM_DISTINCT 得到最多distinct的列，及選擇性好的列。

SQL> select COLUMN_NAME,NUM_NULLS,NUM_DISTINCT,to_char(LAST_ANALYZED,'yyyy-mm-dd') as "date" from dba_tab_columns where owner='cc' and table_name='cc' order by 3;
結合表的索引列
select a.uniqueness 索引型別,b.index_name 索引名稱,b.column_name 欄位 from user_indexes a ,user_ind_columns b

where a.table_name=b.table_name and a.index_name = b.index_name

and a.table_owner=upper('SAPSR3') and a.table_name='ANLU' order by a.uniqueness desc;

如果存在索引，並且選擇性足夠好，雖然不是主鍵列，但是可以直接使用keycols指定；

如果不存在索引？或者索引的選擇性不夠好，可以新增一個選擇性好的索引，隨後使用keycols進行指定。

！！！缺陷或者風險在於，因為是非主鍵，及時選擇性好，如果存在null值,還是無法使用該索引，OGG 程式對應資料庫session 執行SQL還是慢。但是正常情況下可以解決該問題。

並且存在一個疑問，如果假設源端修改了一條記錄，但是選擇性好的column_name 在目標端對應2條記錄，並且ogg複製程式使用keycols引數指定？那麼目標端是修改更新2條記錄？還是1條記錄呢？？？

後續進行測試。

二、Lag at Chkpt

lag是複製程式處理最後一條記錄的作業系統時間和此條記錄在trail檔案中記錄的時間戳的差值，這裡需要注意的是lag延遲只有在檢查點更新時才會更新，所以這個值不是實時更新的，具有一定的離散性，實際上應該理解成最後一個檢查點的最後一條記錄與當前系統時間的時間差。

借鑑

 Replicat負責資料的入庫，一般速度相對於主extract和data pump較慢，容易產生較大延遲。當replicat出現延遲後，需要對程式進行調優或者拆分，具體步驟參照本文件上一節。一般調優完成後，在日常業務狀態下應當不存在較大延遲（一般幾秒到一分鐘以內）；
當出現批處理時，可以允許一定的延遲，一般以不影響第二天的正常業務為準 – 例如，如果批處理每天早上4點前結束，可以控制延遲在2小時以內。

        因此，首先需要確定OGG複製所允許的最大延遲在日常業務和批處理時的目標是什麼，然後一旦達不到此目標就要依據上上面介紹的方法進行效能的調優。

自我理解：按照我們實際運維的情況，OGG同步資料也是根據業務要求分級別的。

例如OGG鏈路1，是用於資料包表生成，那麼必須保證業務每天在8點~10點之間是OGG無延遲，否則延遲高哪怕是10分鐘，也可能導致報表不準確，因此這種OGG鏈路的複製不允許在7~10點存在明顯延遲。

例如OGG鏈路2，用於OGG災備環境，重要性沒那麼重要，因此保證OGG複製程式1天內或者3天內資料同步即可。

站在運維的角度，需要結合實際情況考慮OGG重要程度，進行評估。如果不影響業務，純粹的延遲可以忽略。

實際遇到延遲50小時。
本次講述通過索引加快OGG同步方式；

2.1 定位OGG複製程式在oracle資料庫中的Session


$ps -ef|grep RP10
PID
20  RP10 ······
$ps -ef|grep 20
PID
60864  LOCAL=NO   [OGG Session process]

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

       SID    SERIAL# SQL_ID         PROGRAM
------------------------------------------------
      2276    37851 2j664   oracle@cc (TNS V1-V3)

2.2 定位造成OGG複製程式延遲過高的SQL

通過ash檢視，查詢1天內這個OGG程式 session ，都在執行什麼sql ,event資訊。 ash檢視間隔1s取樣 active session 1次。因此捕捉到的次數越多，說明消耗花費的時間越多。
select sql_id,event,BLOCKING_SESSION,CURRENT_OBJ#,count(*) from v$active_session_history where SAMPLE_TIME>sysdate-1 and SESSION_ID=2276  and SESSION_SERIAL#=37851
 group by sql_id,event,BLOCKING_SESSION,CURRENT_OBJ# order by 5,4;
SQL_ID          EVENT                                   BLOCKING_SESSION CURRENT_OBJ#   COUNT(*)
------------- ---------------------------------------------------------------- ---------------- ------------ ----------
1n7zz8wb86jpw                                                   0         16
088mh1tws6wtm                                                  -1         19
2jt8ttg6b42b4                                                   0         75
9y2087cvvr4r9                                                   0        115
1n7zz8wb86jpw                                                  -1       4575
2cc4                                                           -1      18877
9y2087ccc9                                                     -1      62497
10 rows selected.

select * from table(dbms_xplan.display_cursor('2ccb4'));
SQL> select * from table(dbms_xplan.display_cursor('9ycc'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    xx, child number 0
-------------------------------------
DELETE FROM "W"."DT"  WHERE "U_ID"
= :b0 AND 多個列  ······ AND "C_SORT"
= :b18 AND ROWNUM = 1
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3610703515
----------------------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |                   |       |       | 24025 (100)|       |
|   1 |  DELETE         | DT |       |       |        |       |
|*  2 |   COUNT STOPKEY     |                   |       |       |        |       |
|*  3 |    TABLE ACCESS FULL| DT |     1 |  3312 | 24025   (1)| 00:04:49 |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=1)
   3 - filter(("U_ID"=:B0 AND 多個列······ "O_SORT"=TO_NUMBER(:B18))) 35 rows selected. 

距離，兩個SQL 同一個表，都是delete操作，幾乎相同。 執行幾乎全表掃描，表10G，當然慢了，對吧？  執行刪除1條記錄，需要訪問10G的資料。。。不慢才怪。

2.3 建立索引，加快OGG程式同步速度。

查詢表相關索引,不存在。
select index_owner,index_name,table_name,column_name,column_position from dba_ind_columns where table_name='DT' order by index_name,column_position;
null

查詢列的選擇性

select * from dba_tab_col_statistics where table_name='DT' order by column_name;
```````
OWNER                   TABLE_NAME              COLUMN_NAME             NUM_DISTINCT LOW_VALUE
------------------------------ ------------------------------ ------------------------------ ------------ ----------------------------------------------------------------
HIGH_VALUE                                DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE AVG_COL_LEN HISTOGRAM
---------------------------------------------------------------- ---------- ---------- ----------- --------- ----------- --- --- ----------- ---------------
W                           DT                     U_ID                    0
                                      0         0         0 10-JUL-20         YES NO        0 NONE
······
19 rows selected.

SQL> select count(*) from "W"."DT";
  COUNT(*)
----------
    724643

GGSCI > stop Ogg_process

！注意，本次知道OGG對應是災備，不存在大量相關業務，慎用no_invalidate>false
exec dbms_stats.gather_table_stats(ownname=>'W',tabname=>'DT',cascade=>true,degree=>8,estimate_percent=>10,no_invalidate=>false) ;

再次查詢

OWNER                   TABLE_NAME              COLUMN_NAME             NUM_DISTINCT LOW_VALUE
------------------------------ ------------------------------ ------------------------------ ------------ ----------------------------------------------------------------
HIGH_VALUE                                DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE AVG_COL_LEN HISTOGRAM
---------------------------------------------------------------- ---------- ---------- ----------- --------- ----------- --- --- ----------- ---------------

W                           DT                     U_ID      59346 3030303039366339386466663436356439636636653430663366323138383365 

6666666561356132333564363430616462303462643861393832383734303361 .00001685 0 1 08-AUG-20 70833 YES NO 32 NONE 19 
rows selected.

exec dbms_stats.gather_table_stats(ownname=>'W',tabname=>'DT',cascade=>true,degree=>8,estimate_percent=>10,no_invalidate=>false) ;

查詢列的選擇性

select OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,LAST_ANALYZED from dba_tab_col_statistics where table_name='DT' order by NUM_DISTINCT; 
OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS LAST_ANAL 

------------------------------ ------------------------------ ------------------------------ ------------ ---------- 
xx O_SORT 49853 0 08-AUG-20
xx U_ID   60063 0 08-AUG-20 19 
rows selected. 


select count(*) from ( select distinct U_ID,O_SORT from xx.xx);
 COUNT(*) 
---------- 
692924 
create index xx.xxon xx.cc(U_ID,O_SORT) parallel 6; 
alter index xx.xx parallel 1;

exec dbms_stats.gather_table_stats(ownname=>'W',tabname=>'DT',cascade=>true,degree=>8,estimate_percent=>10,no_invalidate=>false) ;

GGSCI (obcdb36) 3> start ogg_process

SQL> select * from table(dbms_xplan.display_cursor('2jt8ttg6b42b4'));
 ----------------------------------------------------------------------------------
 Plan hash value: 969284704 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------------------------------------- 
| 0 | DELETE STATEMENT | | | | 2 (100)| | 
| 1 | DELETE | cc| | | | | 
|* 2 | COUNT STOPKEY | | | | | | 
|* 3 | TABLE ACCESS BY INDEX ROWID| cc| 1 | 191 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_ID_SORT | 1 | | 2 (0)| 00:00:01 

至此，OGG使用索引加快了資料同步delete速度。

OGG複製程式延遲高，優化方法二(存在索引),SQL選擇不好的索引
2020-08-28
優化索引SQL
MySQL Slave延遲很大優化方法
2016-12-23
MySql優化
MySQL 8 複製（三）——延遲複製與部分複製
2020-02-12
MySql
mysql主從延遲複製
2019-01-29
MySql
PostgreSQL中的複製延遲
2023-04-20
SQL
MySQL 5.7 延遲複製配置
2016-05-04
MySql
高吞吐低延遲Java應用的垃圾回收優化
2018-10-25
Java優化
OGG 複製程式拆分
2014-07-03
MySQL之從複製延遲問題排查
2018-09-06
MySql
MySQL主從複製延遲解決方案
2023-03-16
MySql
故障分析 | MySQL 異地從庫複製延遲案例一則
2022-03-05
MySql
記一次 MySQL 主從複製延遲的踩坑
2017-07-16
MySql
主從複製延遲推薦解決方案
2020-12-09
MongoDB的複製延遲和網路分割槽
2012-10-02
MongoDB
【MySQL】效能優化之延遲關聯
2014-06-07
MySql優化
mysql同步(複製)延遲的原因及解決方案
2019-05-13
MySql
如何利用PostgreSQL的延遲複製實現災備
2019-02-18
SQL
MySQL主從複製延遲原因及處理思路
2018-05-22
MySql
mysql的主從複製資料延遲問題
2017-06-24
MySql
一個複合索引的優化案例
2016-04-15
索引優化
SQL優化案例一則--複合索引沒有被使用
2014-02-11
SQL優化索引
什麼是高延遲檔案傳輸？為什麼要使用高延遲檔案傳輸
2022-10-09
複合索引與函式索引優化一例
2014-08-25
索引函式優化
Mysql 非同步複製延遲的原因及解決方案
2020-11-10
MySql非同步
SUM優化（複合索引）
2014-08-25
優化索引
ogg 併發複製程式自阻塞
2019-08-27
MySQL 效能優化：效能提升 50%，延遲降低 60%
2016-09-01
MySql優化
在Linux中，mysql 如何減少主從複製延遲？
2024-06-22
LinuxMySql
mysql的主從複製延遲問題--看這一篇就夠了
2021-07-02
MySql
查詢中讓優化器使用複合索引
2012-12-12
優化索引
OGG複製程式掛起abended處理
2011-12-03
Sql Server實時監控釋出訂閱複製Replication、subscription有多少延遲的方法
2022-01-06
SQLServer
主從延遲調優思路
2024-04-15
OGG 指定csn啟動複製程式的另一種方式
2015-03-20
MariaDB 10之並行複製–延遲測試結果薦
2014-07-01
並行
啟動優化之動態庫延遲載入
2018-12-10
優化
3.官方優化建議3.1網路延遲
2018-03-01
優化
Web效能優化系列（3）：如何延遲載入JS
2015-04-16
Web優化JS