Post slow with the messages in event log: " Failed to execute SQL on table <name>: (4300614)

Post slow with the messages in event log: " Failed to execute SQL on table <name>:

Post is slow and the following messages keep appearing in the event log:

11/05/06 14:59 Warning: 15033 - Failed to execute SQL on table <name>: ORA-01013: user requested cancel of current operation. [sp_opst (for o.PRG-o.ODSUP queue oraprod)/1439]
11/05/06 14:59 Notice: Oracle error: ORA-01013: user requested cancel of current operation. rowid:AAUtB8ANxAAA+WvAAa [sp_opst (for o.PRG-o.ODSUP queue oraprod)/1439]
11/05/06 14:59 Notice: Resolved internal lock for <name>: [sp_opst (for o.PRG-o.ODSUP queue oraprod)/1439]

The message is either informational or due to one of several reasons. Refer to Resolution.

There may be a couple of reasons for this:

A. This message is informational. The "user requested cancel of current operation on rowid AAAAAAAAAx" error is evidence of excessive rollback activity on the source database. This is when a user issues a ctrl c during execution of a script or a session was killed before a commit was issued. This is not a fatal error. Shareplex is designed to work through these rollbacks; however, there are times when the poster will become very slow due to this type of activity.

B. The Post may be very slow due to the following:

A long running transaction starts, followed by a second transaction at a later time. The second transaction doesn't return so the post process issues a query to see if the transaction is blocked and who is blocking it. If another post session is the blocking session that session is rolled back to allow the commit to happen and the rolled back session is reapplied; hence the message "Resolving internal lock.." and it usually accompanies the " user requested cancel of current operation" message. The 'user' is actually shareplex.

This problem can be caused by many things:

1. Due to row contention:.
This one is easiest to resolve. Running qview -p would able to handle this obreak. You just have to ensure that Post is stopped due to error, exit sp_ctrl and on the OS prompt while in /proddir/bin, issue qview -p. After the qview prompt returns, exit qview and go to sp_ctrl and start Post. Even if the problem is not resolved, qview -p will not do any damage.

2. Array insert that is failing due to a collision on the unique index:
For this, convert the unique index into a non-unique index (disable the unique constraint) and add the index into the hints file.

3. Init_trans on the index:
This is due to block contention on the index of the underlying table. The solution is to rebuild the index on target table with a different init_trans setting and restart post.

4. Bitmap index on target table. The solution is to disable them, at least for the time being (but do not recreate b-tree index as perhaps the b-tree index may not serve the purpose).

5. Init_trans on the table:
This is due to block contention on the table. The solution is to increase the init_trans on the target table. The table will have to be rebuild for the block to be re-migrated. So this cannot be done with ease.

You may not want to try option #5 until you are sure that the problem is not due to causes mentioned in 1 through 4. If none of these help, then Quest Support may have to investigate further.