When Post processes an UPDATE, by default it builds a where clause that not only includes the key value but also includes the before values (pre-image) of the column that has changed. This additional checking is an added overhead in replication. This article delves on a way to reduce this extra overhead.
The parameter SP_OPO_REDUCED_KEY offers a novel way to speed up Post when it is involved with UPDATEs. This is subject to some caveats explained below. Here is how it works:
Set the parameter to 2 on target sp_ctrl:
sp_ctrl>set param SP_OPO_REDUCED_KEY [queue <queue_name>] 2
sp_ctrl>stop post [queue <queue_name>]
sp_ctrl>start post [queue <queue_name>]
From this point on the Post will not check for the pre-image of the row it is trying to UPDATE for ascertaining that it is operating upon the correct row. It will simply locate the target row based on the key value of the UPDATE that occurred on source and set the column values on target appropriately. For example:
UPDATE owner.table_name set column1 = <value1>, [set column2 = <value2>], ……….where <key_column> = <value>;
Without the parameter in place, it will issue UPDATE as below:
UPDATE owner.table_name set column1 = <value1>, [set column2 = <value2>], ……….where <key_column> = <value> AND columnA = <value1> [AND columnB = <value2>…………….]
(all clauses following AND represent the additional conditions used to locate the correct target row based upon the pre-image before updating that row.
The additional checking done by Post in the process of locating the correct row before it UPDATEs it represents the extra overhead involved. When you do away with that extra checking, it can speed up Post. In essence the Post simply locates the target row on the basis of the key value for the row that was UPDATEd on source. It does not check the values of the other columns for ascertaining that the pre-image of the column is what it expects before updating the target row.
Here are the caveats involved:
It works on the premise that the source row has the true data. The replication is based on this premise so this is a valid assumption to make. Only rare situations would cause this assumption to be false (for example the ORA-1801 received when invalid data is inserted on a DATE field on source using mechanisms that do not require such validity checks. However, when Post submits such invalid data to the target database, it will be discarded unless the target also allows applying such invalid data by circumventing such integrity checks.
The table in question need to have a primary or unique key at a minimum, otherwise the parameter will not work.
The parameter can only be applied at a queue level at a minimum so you cannot apply it selectively at a table level.
The main reason the pre-image is verified before operating upon a row is to ensure that the integrity of target is maintained and not direct user activity, triggers or other factors are present that would result in out of sync on target. If such out of sync are present, the UPDATEs are destined to fail with the conventional method of checking pre-image prior to applying UPDATEs. When you do away with such checking by setting the parameter SP_OPO_REDUCED_KEY to 2, you are masking the problem though in the end the table will remain in sync with respect to the source.
