早上巡檢時(shí)發(fā)現(xiàn)OGG目標(biāo)端一個(gè)事務(wù)處理時(shí)間非常長，直接導(dǎo)致REPLICAT進(jìn)程嚴(yán)重延遲：- GGSCI (sv890n01) 13> info rads_1
- REPLICAT RADS_1 Last Started 2012-12-10 09:46 Status RUNNING
- Checkpoint Lag 01:33:16 (updated 11:35:14 ago)
- Log Read Checkpoint File ./dirdat/pa000021
- 2012-12-11 01:55:10.313205 RBA 1753940084
- SQL> select table_name,num_rows from dba_tables where table_name='GT_MXYE_NEW';
通過 v$session , v$sql 定位到當(dāng)前sql為
- DELETE FROM "ADS"."GT_MXYE_NEW" WHERE "ZQDM" = :b0 AND "GTTGZH" = :b1 AND "JYSDM" = :b2 AND "YE" = :b3 AND "DQDM" = :b4 AND "SXDM" = :b5 AND ROWNUM = 1
通過 v$transaction.start_time 發(fā)現(xiàn)事務(wù)的開始時(shí)間與前面的延時(shí)是一致的，并且這條sql已經(jīng)執(zhí)行了50萬次，在源庫查詢發(fā)現(xiàn)晚上"ADS"."GT_MXYE_NEW"這個(gè)表刪除了500萬條記錄，sql為
- DELETE FROM GT_MXYE_NEW A WHERE EXISTS ( SELECT 1 FROM ADB_ZW_GTTGYEBDMX B WHERE 1=1 AND A.ZQDM=B.ZQDM AND A.GTTGZH=B.GTTGZH AND A.JYSDM=B.JYSDM AND B.KSRQ=:B1 )
該表沒有主鍵和唯一約束，目標(biāo)端把源端的sql拆分成了500萬條再執(zhí)行，查看執(zhí)行計(jì)劃
- SQL> explain plan for DELETE FROM "ADS"."GT_MXYE_NEW" WHERE "ZQDM" = :b0 AND "GTTGZH" = :b1 AND "JYSDM" = :b2 AND "YE" = :b3 AND "DQDM" = :b4 AND "SXDM" = :b5 AND ROWNUM = 1;
- Explained.
- SQL> @?/rdbms/admin/utlxpls.sql
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 3603936140
- -------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------------
- | 0 | DELETE STATEMENT | | 1 | 41 | 629 (1)| 00:00:08 |
- | 1 | DELETE | GT_MXYE_NEW | | | | |
- |* 2 | COUNT STOPKEY | | | | | |
- |* 3 | TABLE ACCESS BY INDEX ROWID| GT_MXYE_NEW | 1 | 41 | 629 (1)| 00:00:08 |
- |* 4 | INDEX RANGE SCAN | IDX_GT_MXYE_NEW | 13477 | | 87 (2)| 00:00:02 |
- -------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(ROWNUM=1)
- 3 - filter("GTTGZH"=:B1 AND "JYSDM"=:B2 AND "DQDM"=:B4 AND "SXDM"=:B5 AND
- "YE"=TO_NUMBER(:B3))
- 4 - access("ZQDM"=:B0)
- 19 rows selected.
kill replicat 后在目標(biāo)端新建復(fù)合索引（官方文檔：KILL REPLICAT 后當(dāng)前事務(wù)回滾并記錄檢查點(diǎn)，下次重啟進(jìn)程不會丟失數(shù)據(jù)）：
- GGSCI (sv890n01) 15> KILL REPLICAT RADS_1
- Sending KILL request to MANAGER ...
- Killed process (26318) for REPLICAT RADS_1
- GGSCI (sv890n01) 16> info all
- Program Status Group Lag at Chkpt Time Since Chkpt
- MANAGER RUNNING
- REPLICAT ABENDED RADS_1 01:33:16 11:40:01
創(chuàng)建復(fù)合索引再看執(zhí)行計(jì)劃
- SQL> create index ads.ind_gt_mxye_ogg on ads.GT_MXYE_NEW(ZQDM,GTTGZH,JYSDM,YE,DQDM,SXDM) TABLESPACE TS_ADC_INDEX nologging;
- Index created.
- SQL> explain plan for DELETE FROM "ADS"."GT_MXYE_NEW" WHERE "ZQDM" = :b0 AND "GTTGZH" = :b1 AND "JYSDM" = :b2 AND "YE" = :b3 AND "DQDM" = :b4 AND "SXDM" = :b5 AND ROWNUM = 1;
- Explained.
- SQL> @?/rdbms/admin/utlxpls.sql
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 415052804
- --------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------
- | 0 | DELETE STATEMENT | | 1 | 41 | 3 (0)| 00:00:01 |
- | 1 | DELETE | GT_MXYE_NEW | | | | |
- |* 2 | COUNT STOPKEY | | | | | |
- |* 3 | INDEX RANGE SCAN| IND_GT_MXYE_OGG | 1 | 41 | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(ROWNUM=1)
- 3 - access("ZQDM"=:B0 AND "GTTGZH"=:B1 AND "JYSDM"=:B2 AND
- "YE"=TO_NUMBER(:B3) AND "DQDM"=:B4 AND "SXDM"=:B5)
- 17 rows selected.
重新啟動REPLICAT進(jìn)程，運(yùn)行了11個(gè)小時(shí)還沒運(yùn)行完的事務(wù)15分鐘內(nèi)執(zhí)行完畢，再看延時(shí)情況
- GGSCI (sv890n01) 29> info all
- Program Status Group Lag at Chkpt Time Since Chkpt
- MANAGER RUNNING
- REPLICAT RUNNING RADS_1 09:33:16 00:00:07
由于當(dāng)前事務(wù)已經(jīng)提交并且寫了檢查點(diǎn)，相應(yīng)的延時(shí)都發(fā)生了變化。
總結(jié)：源端的sql和目標(biāo)端的sql不一定是一致的，原因有可能是OGG的內(nèi)部機(jī)制，也有可能是該表沒有外鍵或者唯一約束，也有可能是REPLICAT進(jìn)程參數(shù)設(shè)置不當(dāng)造成的，需要進(jìn)一步學(xué)習(xí)。
