idea From now on, please track this idea from the Ideation por... in Suggest an Idea

Speed the transactional replication to Oracle

Airbus — Wed, 23 Jun 2021 09:16:43 GMT

Hello,

As we need to ensure the First in, First out (FIFO) principle on some of our Replicate Flow, we need to use the transactionnal mode (batch optimized mode cannot ensure FIFO principle).

In order to explain the high resource consumption, our project team analyzed the Oracle DB logs and it appears that Replicate uses PL/SQL blocks and hard coded values/literals in order to catch and log exceptions. For Oracle endpoints, this leads to the recalculation of the total execution plan for each transaction according to your analysis. This is called hard parsing, and this is clearly resources consuming.

We identified 4 ways below to "force" soft parsing by Oracle (= sharing of information between transactions to reduce the execution plans calculation time) :

using bind variables in PL/SQL blocks instead of literals
using literals in local variables in the PL/SQL block
using literals inside dynamic SQL statements
using standalone simple SQL statements with literals or variables

It could be very usefull for Replicate flow with FIFO requirement to implement one of these solutions.

Best Regards,

Airbus MAPS team

Re: Speed the transactional replication to Oracle - Status changed to: Open - Collecting Feedback

Shelley_Brennan — Mon, 12 Jul 2021 13:19:28 GMT

Thank you for the suggestion. We will collect feedback from others on the Oracle Target endpoint and review internally as well.

Re: Speed the transactional replication to Oracle

LuigiFanuli — Mon, 19 Sep 2022 10:13:13 GMT

We are from Consorzio Operativo Monte dei Paschi di Siena and we are in the same condition of Airbus MAPS Team.

We need to use the transactional mode (batch optimized mode cannot ensure FIFO principle) and our analysys to solve the "hard parse" problem is the same.

We are interested to any solution to force "soft parse" by Oracle as explained by Airbus MAPS Teams.

Best regards,

Consorzio MPS DBA Team

From now on, please track this idea from the Ideation por...

Meghann_MacDonald — Wed, 02 Aug 2023 16:31:20 GMT

From now on, please track this idea from the Ideation portal.

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Re: Speed the transactional replication to Oracle - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 16:31:22 GMT