Sometimes transactions executed on a particular table can cause Post to slow down considerably.
Two common examples are an out of sync on a table or a large DELETE transaction on a table that has no key.
In case of out of sync, slowness can be caused due to no key existing on the target table, too much or logging activity on errlog.sql and event log as well as caching of rowids.
In case of DELETEs, slowness can be due to the # of rows involved in the transaction or due to no key existing on the target table.
massive out of sync or no key on table causing full table scan
WORKAROUND:
1. Can choose to add an appropriate index on target if the table on target is missing key or indexes.
Run the following query to find out which column is suitable to create an index on. Should analyze the table first to refresh the oracle statistics.
select column_name, num_distinct from dba_tab_columns where table_name=<problem_table> order by num_distinct;
sp_ctrl> stop post
Pick the column(s) with most number of num_distinct values to create an index.
Then add this index name to the hints file under $SP_SYS_VARDIR/data/hints.SID on target
owner.table_name owner.index_name
sp_ctrl> start post
shareplex post will now use this index hints when it executes sql statement on this table.
2. If its not feasible to tune the table at the moment, choose to ignore transactions on this table to speed up post and resync this problem table later, then follow these steps.
Set the following parameter to cause post to discard messages routed to a specific objectid (where objectid is from source table):
sp_ctrl> stop post
sp_ctrl> set param SP_OPO_DISABLE_OBJECT_NUM objectid (replace objectid with the actual objectid of the table)
sp_ctrl> start post
To restore the object back to replication,
sp_ctrl> stop post
sp_ctrl> reset param SP_OPO_DISABLE_OBJECT_NUM
sp_ctrl> start post
The following is the information on the above parameter:
SP_OPO_DISABLE_OBJECT_NUM
This parameter prevents Post from posting replicated DML and DDL operations for a table, based on the source tables Oracle object ID. Set this parameter if a source tables data is invalid or corrupted, or to skip the table data from replicating to the target database(s). Setting this parameter prevents further replication activity on the target table. The Post process discards all replicated messages for this table from the post queue, and the messages do not accumulate in the queue.
Use the parameter with caution. If it is enabled and DDL or DML is executed for the source table, the target data and the SharePlex object cache will be out of date because the changes are not posted. If there are dependencies on the table, such as a foreign key in other tables outside the replication configuration, disabling posting will prevent the dependencies from being satisfied.
SP_OPO_DISABLE_OBJECT_NUM is disabled by default. To enable it, issue the set param command on the target system using the object ID of the source table as the value, as shown in the following example.
sp_ctrl(sysB)> set param SP_OPO_DISABLE_OBJECT_NUM 12345
When ready to begin posting to the target table again, reset SP_OPO_DISABLE_OBJECT_NUM so that it will assume the default value of NULL.
Default: NULL (off)
Range of valid values: any positive integer
Takes effect: when Post is restarted
Note: one object can be ignored at a time.
