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.
