topic Re: Bad performance in Qlik Replicate

Bad performance

christophelemmens — Tue, 06 Jun 2023 08:25:14 GMT

We get a bad performance it the APPLY_TARGET even if we put timeouts in milliseconds.

Than still it takes 4 seconds at lowest before the target gets updated. Both source and target are oracle 19c.

I'll share log and excel with header__timestamp so you can see the details and the delay.

What can we do to improve performance ?

Re: Bad performance

SwathiPulagam — Tue, 06 Jun 2023 14:57:49 GMT

Hi @christophelemmens ,

Below is the article link for Troubleshooting Qlik Replicate Latency and Performance Issues :

https://community.qlik.com/t5/Official-Support-Articles/Troubleshooting-Qlik-Replicate-Latency-and-Performance-Issues/ta-p/1929456

Thanks,

Swathi

Re: Bad performance

Steve_Nguyen — Tue, 06 Jun 2023 15:05:32 GMT

@christophelemmens , this could be from your task : Change Processing Tuning, task setting.

try to play around on the numbers:

https://community.qlik.com/t5/Official-Support-Articles/General-understanding-of-Qlik-Replicate-Change-Processing-Tuning/ta-p/1937341

https://community.qlik.com/t5/Official-Support-Articles/Transaction-Offload-Tuning-guidance-for-Replicate/ta-p/1908420

https://community.qlik.com/t5/Qlik-Replicate/Attunity-Replicate-Batch-Tuning-Force-apply-batch-memory/td-p/1810847

Re: Bad performance

Steve_Nguyen — Tue, 06 Jun 2023 15:14:41 GMT

this could be from your task : Change Processing Tuning, task setting.

try to play around on the numbers:

https://community.qlik.com/t5/Official-Support-Articles/General-understanding-of-Qlik-Replicate-Change-Processing-Tuning/ta-p/1937341

https://community.qlik.com/t5/Official-Support-Articles/Transaction-Offload-Tuning-guidance-for-Replicate/ta-p/1908420

https://community.qlik.com/t5/Qlik-Replicate/Attunity-Replicate-Batch-Tuning-Force-apply-batch-memory/td-p/1810847

Re: Bad performance

Heinvandenheuvel — Tue, 06 Jun 2023 16:01:02 GMT

Thanks for providing all the details.

This should be a high performance task - only inserts to the __CT table, no updates, no lobs, modest row (35 columns most less than 100 chars, 5 between 1500 and 2000.

I'm not sure how to consolidate the two times just 24 rows added with all the changes available. Is it possible that there was a lot of extra activity in the Redo/Archive log while you started the test? I recommend PERFORMANCE TRACE to actually see the Redo volume and read time as well as a clear source vs target latency picture.

There is a target apply issue for sure. Those 6 second for 24 rows kinda makes no sense.

11:39:41 [TARGET_APPLY ]T: Truncate statement TRUNCATE TABLE "CDC_DBA"."attrep_changesD581E6FF_2000001" 11:39:41 [TARGET_APPLY ]T: Going to run insert statement, from seq 1 to seq 24 INSERT /*+ PARALLEL("CDC_DBA"."ZHD01__ct") */ INTO "CDC_DBA"."ZHD01__ct" 11:39:47 [TARGET_APPLY ]T: After running insert statement (oracle_endpoint_bulk

Look at the times above, from selected log lines. At 11:39:41 the helper table attrep_changesD581E6FF_2000001 is truncated, ready to receive fresh changes, changes are loaded to that tables in that same second and Replicate is ready to ask the Oracle to move the changed from the helper table to the targetted __CT table. At that point all the data to be inserted is already on the target and it is strictly a Oracle target-DB only action needed to be done but reported to have taken 6 seconds (ditto in the other example in the log).

That's an Oracle problem, not Replicate. Try to use all the Oracle performance tools on Query plans / resource consumption and so on to figure out what is happening. The Oracle Enterprice manager can be very helpful in doing this, or enable trace events for the Replicate apply session.

I recommend you take a copy or two of that attrep_changesD581E6FF_2000001 table on target, copy the target __CT table, truncate that. Repeat the insert query manually (replacing the :1 and :2 in "seq" >= :1 and "CDC_DBA"."attrep_changesD581E6FF_2000001"."seq" <= :2 " with actual values like 1 and 24, and fix the table names in "INSERT /*+ PARALLEL("CDC_DBA"."ZHD01__ct") */ INTO "CDC_DBA"."ZHD01__ct"
" and "FROM "CDC_DBA"."attrep_changesD581E6FF_2000001"

How long does that take to run on it's own? It should be less than 1/2 second IMHO.

Veel plezier gewenst :-)!

Hein.