The task starts slowly

[XiaoyongLin]

I started a task and the wait time was very long，The log contents are as follows

execute table description query 'select * from "SJZBQ_SS"."DZDZ_HWXX_DZFP"'

The amount of data in this table is very large，Why does replicate scan the full table data？

source database:oracle

target database:oracle

task mode:Incremental synchronization

Wait for a response online

[Dana_Baldwin]

Hi @XiaoyongLin 

I see you also opened a severity 1 production down case for this. Copying my response here:

By default, Replicate will run a "select *" on all tables in the task for the full load phase. Whether that results in a full table scan on the source depends on how the tables are indexed and how Oracle determines the best way to fetch the data is.

You might consider using the parallel load option, see here for details:

https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedEMReplicate/Custom...

Parallel load would also help to avoid ORA-01555 snapshot too old errors when handling large tables.

Thanks,
Dana

[Dana_Baldwin]

Hi @XiaoyongLin 

Also: Since you have mentioned "task mode:Incremental synchronization" I'm assuming it's a CDC only task and you are seeing select* from running in the DB during the initial metadata refresh step.
Could you please let us know the Replicate version you are on? We had similar issue in 2022.11 SP04 or lower and was fixed in later Service pack.

Thanks,

Dana

[XiaoyongLin]

Hi @Dana_Baldwin 

My Replicate version is 2022.5.0.652,This is a production task, I can't do an upgrade now, How can I solve this problem without upgrading?

[Heinvandenheuvel]

What is " "task mode:Incremental synchronization"  ?? I installed the May 2023 release just to make sure I did not miss anything new. That release does not add a new taks mode, it does remove the 'cloud' task mode.

>> Why does replicate scan the full table data？

It does NOT at that stage. That logging tekst is somewhat misleading. Replicate is executing the 'describe_only' option. It actually executes that multiple times (hmmm... not optimal 🙂 often in a a single second.  See below.

Please check the timestamps around that statement it should take any time. Next check where the real 'gap'  / delay is happening

Please consider attaching a (  portion(s)  of the  )  log for the reptask_xxx.log  as .txt with a couple of lines around one of the 'select *' lines and sectons where you think it spends a lot of time.

Hein.

https://docs.oracle.com/cd/A97630_01/appdev.920/a96584/oci04sql.htm

OCI_DESCRIBE_ONLY. This mode is for users who wish to describe a query prior to execution. Calling OCIStmtExecute() in this mode does not execute the statement, but it does return the select-list description.

[XiaoyongLin]

This task has not written data since 03:53:10:824043，the sql statement "SELECT * FROM "SJZBQ_SS"."DZDZ_HWXX_DZFP""  has been running on the target database。

[Dana_Baldwin]

Hi @XiaoyongLin 

Please respond to the initial questions on your support case and attach a diagnostics package for the task. If it is the metadata check that is taking time, I do not know of a way to avoid that or speed it up. It might help to ensure the indexes are not fragmented and that the statistics are up to date. Please check with the source/target DBA for those items.

Thanks,

Dana

[Heinvandenheuvel]

>> This task has not written data since 03:53:10:824043，the sql statement "SELECT * FROM "SJZBQ_SS"."DZDZ_HWXX_DZFP"" has been running on the target database。

Did it ever write data? Did you see any "Unload finished for segment ..." with more than 0  "rows sent"

I don't think that query runs against the target (when doing drop + create). What makes you think is does? Show us!

Can you see SQL activity on source (or target) corresponding to the Replicate PID/Thread?

Please show a few more lines around/before 03:53:10:824043 as requested.

I believe is was 'slow', but did it ever finish? Did it ever work at all? Which Replicate version Which Oracle versions?

Thanks,

Hein.