Qlik Replicate- Performance issue for Oracle as source

[dineshkumarl]

Hi Team,

I am replicating from Oracle as source and currently running 3 CDC tasks. I was facing performance issues with Oracle log Miner, my Oracle queries were taking longer to execute. (For example: if a query took less than 5mins to execute took more than 30mins). Hence, I opted for Replicate Log Reader. However, the impact is still the same. The CPU utilization is 5% higher on an average after starting CDC tasks.  

Currently I am facing this issue for 3 CDC tasks and will be running 250+tasks in future. Kindly suggest how I can reduce the impact on my source system while running more number of CDC tasks. 

[Yadunandan]

Hello team,

You need to use LogStream staging task to overcome this issue.

Please refer the below link for more info on using LogStream.

https://help.qlik.com/en-US/replicate/November2022/Content/Replicate/Main/Log%20Stream%20Staging/int...

[dineshkumarl]

Hi Team,

We are reading single source transactional logs/redo logs (single Oracle db with multiple tasks). These tasks are built independently for each table.
I would like to understand Qlik Replicate's resource consumption on both Source and Target endpoints. My source is Oracle and Target is Apache Kafka. Is there any guidelines/pre-requisites/document and how we can reduce the impact.

[john_wang]

Hello @dineshkumarl ,

Every endpoint connection to the source Oracle database will read the REDO Logs once. If you have 2 tasks connect to the same DB, then read the REDO logs twice, and so on. In general, put all the tables in one single task if possible. I'm assuming you have about 250+ tables, it's fine to put all of them in one task to reduce the source database impact.

BTW, Replicate LogReader is highly recommended as it consumes less CPU than Oracle LogMiner access mode.

Hope this helps.

Regards,

John.

[dineshkumarl]

@john_wang  Thanks for your input.

Currently we have created a single CDC task for 3 tables. Is there a way that I can check for resource utilization of Qlik replicate while reading the redo logs? I have tried searching using keywords like 'Attunity', 'redo', 'repctl'. However, I wasn't able to locate any process with the same. Kindly help me in understanding what command Qlik replicate sends while reading the redo logs. This would help me in monitoring the resource utilization of Qlik replicate process specifically.

Thanks.

[john_wang · Accepted solution]

Hello @dineshkumarl ,

Thanks for your update.

In general, Replicate utilizes the Oracle client software connect to Oracle Server to read history data and get REDO log files to capture changes. So far there is no dedicated process spawned in Oracle Linux Server to serve Replicate but only a few of connections, these connections are used to get metadata, to read data, to transfer REDO log files etc. DBA may know many ways to monitor the resource ulitization by using monitor tools or query SQLs.  A sample query to list out the connections which are established by Qlik Replicate (for example):

SELECT S.SQL_ID,S.PROGRAM, S.USERNAME, S.OSUSER
FROM V$SESSION S
WHERE S.USERNAME IS NOT NULL
AND S.PROGRAM LIKE '%repctl%';

Hope it helps.

Regards,

John.

[dineshkumarl]

Hi Team,

May I know the average CPU utilization of Qlik Replicate for Oracle as source while using Oracle Log Miner and while using Replicate Log Reader? (for 30 core CPU and 16 core CPU) for a single CDC task containing multiple tables (I have added 3 tables currently) table count will increase in future.

[Dana_Baldwin]

Hi @dineshkumarl 

There are too many variables to consider to estimate resource utilization (number of tables in task, number of columns, number of LOB columns, activity on the source, etc.). This would be best determined by performing test cases in your pre-production environment. Please note that adding tables may not mean a linear increase in resource usage.

Thanks,

Dana

[maskabone]

Not sure if this is the best place to ask, but trying to find users who have used or currently use Qlik Replicate. How many transactions/s are you replicating, what is your throughput, any identifiable issues you had to troubleshoot, etc. Any help or insight is much appreciated.

[dineshkumarl]

@maskabone Kindly raise a new question for better visibility and more details on your requirements.