Impact on SourceDB side when CDC using LogMiner is executed

[iti-attunity-sup]

Impact on SourceDB side when CDC using LogMiner is executed

I would like to check how much load CDC using LogMiner is putting on SourceDB.
Please tell us about the impact of LogMiner on the performance of Oracle (SourceDB) CPU/IO, etc.

Best regards,
Hironori Fujihira

[kng]

Hello Team,
Good Day

Thanks for reaching Qlik Community!!

Replicate supports Logminer and it would be good to check with your DBA team on how the performance would be without the continous_mine option. Replicate does not actually run the Logminer but just uses the available data from Logminer and how the Logminer works is outside the scope of Replicate.

Since the Logminer is phasing out we do not recommend customers to continue using it and switch to Replicate Log Reader which is a better option.

Hope this helps!!

Regards,
shivananda

[SushilKumar]

Hello @kng 

Logminer is a utility which comes with oracle database. Logminer also allows you to trace SQL statements and monitor their execution, which can help identify performance bottlenecks and optimize database performance.

So it controlled by the Oracle itself. Qlik replicate is a tool who reads Changes from REDO/Archive logs and extract SQL by using its own engine when using Replicate log reader. 

When using Qlik replicate it recommend using Qlik replicate log reader so that QR have more control on the configuration and optimization rather than using Oracle Logminer which is control by Database configuration/Parameter. 

and it will impact Database performance sometimes. Hence not recommend

Regards,

Sushil Kumar 

[john_wang · Accepted solution]

Hello Fujihira-san @iti-attunity-sup ,

Thanks for reaching out to Qlik Community!

Oracle LogMiner is part of Oracle Database, enables apps to query online and archived redo log files through a SQL interface. Products like Qlik Replicate leverage Oracle LogMiner APIs to extract changes from these redo log files. As LogMiner operates within the Oracle database environment, it utilizes system resources such as CPU, I/O, and network bandwidth etc. Consequently, this can potentially impose additional load on the database server.

Determining the precise impact of LogMiner usage can be challenging as various factors may affect the LogMiner behavior, it depends on the workload and configuration, this overhead may be negligible or significant.

The potential impacts of utilizing Oracle LogMiner include:

1. Data volume: For migrations with a high volume of changes, LogMiner might have some I/O or CPU impact on the computer hosting the Oracle source database.
2. Supplemental logging settings: The use of LogMiner with minimal supplemental logging enabled does not have any significant performance impact on the instance generating the redo logs.
3. Filtering in the retrieving SQL: LogMiner may capture interesting changes only and discard the irrelevant tables changes (compares to Replicate Log Reader mode).
4. The Oracle database itself workload

In summary, perhaps conducting a stress test in a specific (given hardware, software, and data volumes etc) could help gauge its impact.
BTW, I happened to get a link from Oracle site:
How To Reduce The Performance Impact Of LogMiner Usage On A Production Database (Doc ID 1629300.1)

I'm encountering difficulty accessing the content as it requires signing in with a My Oracle Support Account.

Hope this helps.
John.

[Heinvandenheuvel]

The actual Logminer overhead on the source varies wildly from application to application. You really jusst want to try it for yourself, perhaps with a Replicate task sending the output to 'NULL'.

Logminer is discouraged for various reasons outlined by other already , notably that Replicate support for using it is deprecated with the most obvious example of this being: "Capturing changes from a PDB using Oracle LogMiner is not supported."

I do want to point out though that using Logminer can be very meaningful, if it works for the targetted application. The reason being the network load potentially being much reduced when using Logminer for a subset of all the changes. The Replicate log reads will have to transfer all of the REDO data over the wire to find out what is needed. This can be tens of megabytes per second all day every day, only to distill changes to a handful of tables perhaps amounting to a few megabytes per day. Using the Logminer that filtering for relevant changes takes place on the source, yes burning CPU there, but the actual log reading overhead is the same either way and the network transfer may go from hard to support to easy-peasy using Logminer.

If source DB overhead is a concern, and there are multiple tasks, then I urge you to look into LOGSTREAM tasks first and foremost.

Hein.

Hein.