goldengate table replication performance issue

[Roshan]

Hello Erman,

can you please advise why below SQL is taking time?

gq93yz451asdg UPDATE /*+ restrict_all_ref_cons */ "CBSOGG"."CB_BILL_INV_ACC_LIST" SET "ACCOUNT_CODE_N"=:1 , "ACCOUNT_LINK_CODE_N"=:2 , "SUBSCRIBER_CODE_N"=:3 , "SUBSCRIBER_CATEGORY_V"=:4 , "SUBSCRIBER_SUB_CATEGORY_V"=:5 , "SERVICE_CODE_V"=:6 , "BILL_CYCLE_FULL_CODE_N"=:7 , "BATCH_ID_V"=:8 , "SUB_BATCH_ID_N"=:9 , "HEIRARCHIAL_OPTION_V"=:10 , "STATEMENT_GROUP_CODE_N"=:11 , "PARENT_GROUP_CODE_N"=:12 , "GROUP_DISCOUNT_CODE_N"=:13 , "BILLING_REGION_V"=:14 , "CURRENCY_CODE_V"=:15 , "PROCESSED_STAGE_ID_V"=:16 , "ERROR_STAGE_ID_V"=:17 , "PROCESSED_STAGE_SEQ_N"=:18 , "ERROR_STAGE_SEQ_N"=:19 , "CONFIRM_ROLBK_OPTN_V"=:20 , "CREDIT_NOTE_OPTN_V"=:21 , "PRINT_OPTN_V"=:22 , "PRINT_CNT_N"=:23 , "CHARGE_FROM_DATE_D"=:24 , "CHARGE_TILL_DATE_D"=:25 , "ERR_REF_N"=:26 , "INVOICE_NUM_V"=:27 , "RELEASE_FLG_V"=:28 , "DUNNING_SCHDL_CODE_V"=:29 , "RISK_CATEGORY_V"=:30 , "CR_DAYS_N"=:31 , "TAX_POLICY_V"=:32 , "TAX_OPTN_V"=:33 , "EXCLUDE_LPF"=:34 , "BILL_REQ_ID_N"=:35 , "BATCH_SEQ_N"=:36 , "LOT_NUMBER"=:37 , "FILE_INDEX"=:38 , "GROUP_ID_V"=:39 , "SUB_GROUP_ID_V"=:40 , "RESTRICTION_DATE_DT"=:41 WHERE "ACCOUNT_CODE_N"=:42 AND "ACCOUNT_LINK_CODE_N"=:43 AND "SUBSCRIBER_CODE_N"=:44 AND "SUB_BATCH_ID_N"=:45 AND "CHARGE_FROM_DATE_D"=:46 AND "CHARGE_TILL_DATE_D"=:47 AND "INVOICE_NUM_V"=:48 and rownum < 2



All indexes are created.

Thanks,

Roshan

[Roshan]

Please find attached the healthcheck report.

health13JUNE22.html

[ErmanArslansOracleBlog]

Please define that "taking time" phrase of yours?
The optimizer cost of that SQL is 328.. 139588 rows processed and it is executed several times..
Maybe it is doing FTS?
Did you the explain plan? In the source and target? They both the same? Anything missing there?

For that hint, I mean restrict_all_ref_cons , we have some bugs and performance issues reported in Oracle Support.. you may want to check those as well .. But it seems -> after creating required indexes, on tables and foreign keys, replication performance becomes better --even in those cases..

[Roshan]

By generating explain plan you mean enable the tracing and tkprof?

alter session set events '955 trace name errorstack level 10';
alter session set events '10046 trace name context forever, level 12';

Yes both source and target are same except I used KEYCOLS in replicat file
MAP CBS_CORE.CB_BILL_INV_ACC_LIST,TARGET CBSOGG.CB_BILL_INV_ACC_LIST,KEYCOLS(ACCOUNT_CODE_N, ACCOUNT_LINK_CODE_N, SUBSCRIBER_CODE_N, SUB_BATCH_ID_N, CHARGE_FROM_DATE_D, CHARGE_TILL_DATE_D, INVOICE_NUM_V) ;

[ErmanArslansOracleBlog]

Just check by connecting to the database.. Using TOAD's explain plan maybe..
Of course you can always enable to trace to generate the detailed info about the execution plan.

But remember my previous update ->

Please define that "taking time" phrase of yours?
The optimizer cost of that SQL is 328.. 139588 rows processed and it is executed several times..
Maybe it is doing FTS?
Did you the explain plan? In the source and target? They both the same? Anything missing there?