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.
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.
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.
