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

日常運維過程中，可能發現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  
延遲說明參考
//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速度。

 

Tags:

VirMach 便宜 VPS

QNews

QNews