Using prepared SQL statements v7
When synchronization replication occurs, the changes recorded in the shadow tables are applied to the subscription tables in JDBC batch updates. In each batch, you can apply changes using either an individual SQL statement for each change, or you can can apply a set of changes using a single, prepared SQL statement. A prepared SQL statement is parsed and compiled only once, but you can execute it multiple times using different values for certain components of the SQL statement in each execution. A SQL statement that isn't prepared is parsed, compiled, and executed only once.
Prepared statements are useful only if the same type of SQL statement (INSERT, UPDATE
, or DELETE
) is executed repeatedly and consecutively with the same target table but with different values. If there's a sequence of consecutive changes that occur to the same table using the same operation such as inserting a set of rows into the same table populating the same columns, the publication server can apply these changes using a prepared statement. Otherwise, each change is applied with its own individual SQL statement.
A number of server configuration options control the characteristics of the JDBC batch along with if, when, and how often prepared statements are used.
defaultBatchUpdateMode
The defaultBatchUpdateMode
option controls whether the default mode uses individual SQL statements in the JDBC batch update (referred to as BUS
mode) or to use prepared SQL statements in the JDBC batch update (referred to as BUP
mode).
defaultBatchUpdateMode={BUS | BUP}
The default value is BUS
.
switchBatchUpdateMode
The switchBatchUpdateMode
option controls whether the publication server dynamically switches between BUS
mode and `BUP mode during the replication process. This switching depends on the type and sequence of updates it encounters in the shadow tables for the trigger-based method or the changeset stream for the log-based method.
switchBatchUpdateMode={true | false}
The default value is true.
Using the default settings of defaultBatchUpdateMode=BUS
and switchBatchUpdateMode=true
, the publication server starts out by applying updates with individual SQL statements. When it encounters a stream of consecutive changes that can all be processed in a single prepared statement, it switches to using prepared SQL statements.
Note
If you want a certain batch update mode used throughout all synchronization replications applied by a given publication server without switching update modes, set the defaultBatchUpdateMode
option to the desired mode in combination with switchBatchUpdateMode=false
. For example, if you want only prepared statements used, set the following options:
defaultBatchUpdateMode=BUP
switchBatchUpdateMode=false
Note
When Oracle is the subscription database, synchronization replication always occurs in BUP
mode as if the preceding two options were always set. The reason for this is so that large columns of TEXT data type from Postgres publications can successfully replicate to Oracle CLOB columns. In BUS
mode an individual Oracle SQL statement has a string literal maximum length of 4000 characters. This limitation doesn't occur for prepared SQL statements that are used in BUP
mode.
busBatchThresholdCount
The busBatchThresholdCount
option sets the number of consecutive updates of the same type that must be encountered in the shadow tables for the trigger-based method or the changeset stream for the log-based method before the publication server switches from BUS
mode to BUP
mode. This option applies if dynamic switching is permitted, that is switchBatchUpdateMode=true
.
busBatchThresholdCount=n
The default value for n is 5.
The number of consecutive changes using the same table and SQL statement type must exceed the specified value n before a prepared statement is used.
Setting this threshold to a low value encourages higher use of prepared statements. Setting it to a high value limits the use of prepared statements.
If changes to the publication were made using many SQL statements, where each statement affected more than one row, then it might help to lower busBatchThresholdCount
. Lowering this value encourages the use of prepared statements on the multiple shadow table rows resulting from each change on the publication.
bupBatchThresholdCount and bupBatchThresholdRepeatLimit
If you use BUP
mode but the number of updates using the same prepared statement is low, this configuration can cause frequent switches to a new prepared statement. In this case, it might be more helpful to use individual SQL statements (BUS
mode).
For example, the following sequence of updates are better processed in BUS
mode:
However, in the following sequence, it is better to use BUP
mode. Updates 1 through 3 are batched in one prepared statement, 4 through 7 in another prepared statement, 8 in its own prepared statement, and then 9 through 15 in one prepared statement.
Use the bupBatchThresholdCount
option with the bupBatchThresholdRepeatLimit
option to control the frequency of mode switches based on the volatility of expected update types to the publication.
bupBatchThresholdCount=m
The default value for m is 5.
bupBatchThresholdRepeatLimit=n
The default value for n is 10.
Each time the same prepared SQL statement is consecutively executed, an internal batch
counter is incremented. If this batch count falls below bupBatchThresholdCount
for the number of executions of a given prepared statement, then a second internal repeat
counter is incremented by one. If the repeat counter eventually reaches bupBatchThresholdRepeatLimit
, the update mode is switched from BUP
to BUS
.
Thus, if there are frequent, consecutive changes of prepared SQL statements (as measured against bupBatchThresholdRepeatLimit
), each of which is executed a small number of times (as measured against bupBatchThresholdCount
), then the mode of execution changes back to individual SQL statements instead of prepared statements.
Note
The publication server changes back to prepared statements when the threshold set by busBatchThresholdCount
is met.
The following example shows the processing of updates when bupBatchThresholdCount
is set to 3 and bupBatchThresholdRepeatLimit
is set to 4. A change to the “query domain” referred to in this example means a different statement type (INSERT
, UPDATE
, or DELETE
) or a different target table are encountered in the next update. This condition requires the use of a different prepared SQL statement.
At this point, the query domain is changed after the first two updates (change from table emp
to dept
), and the number of executions of the prior prepared statement (2) is less than bupBatchThresholdCount
, so the repeat counter is set to 1.
The query domain is changed again (change from table dept
to emp
), but this time the number of executions (4) for the same query domain (updates 3 through 6) exceeds bupBatchThresholdCount
. Thus the repeat counter is reset to 0.
The query domain is changed again (INSERT
statement to UPDATE
statement), and the number of executions (2) is less than bupBatchThresholdCount
, so the repeat counter is incremented to 1.
The query domain is changed between updates 10 and 11, between updates 11 and 12, and between updates 12 and 13. At this point, the repeat counter was incremented three more times to a value of 4. This now equals bupBatchThresholdRepeatLimit
, so processing is changed from BUP
mode to BUS
mode.