Enabling of the init.ora parameter optimizer_use_sql_plan_baselines can slow down Post during lar (4301021)

During a large batch UPDATE on source, the Post is very slow on target and the following messages are observed in the *opo* log:

opo      2010-03-23 10:28:57.160387 15680 3 3: BreakOperation: session 2 is taking too long (5 seconds): mqseq=3119606776 mid=6371101 subque=2

opo      2010-03-23 10:28:57.162199 15680 3 3: BreakOperation: Operation is not blocked -- just taking longer than expected on "owner"."tablename". Make sure proper index is being used

opo     

.

.

2010-03-23 14:21:23.571261 12062 1 1: SyncOpWait: Main thread waiting for syncop.done for 60 seconds

opo      2010-03-23 14:21:27.445189 12062 1 LogTransaction: sid 1100 splex session 2 messages in queue 1182704 status ACTIVE ublk(03/23/10 14:21:26) urec(1) log_io(7) physical_io(57) cr_get(3) cr_change(42)

opo      2010-03-23 14:22:28.043773 12062 1 1: SyncOpWait: Main thread waiting for syncop.done for 120 seconds

Enabling of the parameter optimizer_use_sql_plan_baselines is causing Post to slow down.

By default the following Oracle parameters are enabled on target in Oracle 11g:

optimizer_capture_sql_plan_baselines

optimizer_use_sql_plan_baselines

The parameter optimizer_capture_sql_plan_baselines enables Oracle to evaluate the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occuring in the system.

The parameter optimizer_use_sql_plan_baselines enables the use of SQL plan baselines.

Together, these two parameters may not directly call Oracle Optimizer and only use Optimizer to identify the best execution plans and store them in the database for future use.

The problem occurs when there is a large UPDATE on source whereby Shareplex constructs individual UPDATE for every row and each of these UPDATE on target tries to use the plan baselines. This slows down Post considerably. The parameter optimizer_use_sql_plan_baselines should be disabled at least for the time being so that the Post will run fine. The parameter can be disabled on the fly and does not require database restart. Once disabled, the Optimizer would be used (as is done in pre Oracle 11g). The parameter can be re-enabled once the Post queue is drained.

The solution applies to specific situation only and there can be other reasons for the Post problem of slowness.