Single-threaded Post (STP) waiting for a long time on another non-Shareplex sesson. (4300387)

The Single-threaded Post (STP)   is not moving past a certain DML and appears to be hung for a very long time. When the view v$session_wait is queried, it is observed that STP is waiting for a lock to be released. Stopping and restarting Post or Shareplex is not helping.

STP will not process anything until the lock acquired by another process in Oracle is released.

Wait  until Oracle resolves the blocking session or the session completes its task. If this cannot wait, then the following steps can be tried as workaround:

1.  Switch the Post to multi-threaded mode (MTP) and there is a very good chance that the Post process will be able to get around the problem. The exact working of MTP is a Quest internal information but the multiple threads of Post are helping in this situation. Please see Solution # SOL33386 for details as switching modes require special considerations at times.

2.  The offending session showing up in v$session_wait can be killed at database level, however be sure to ascertain its implications beforehand. Killing that session will help Post move forward.

3. If the workaround suggested in para 1 and 2 are not feasible or do not help, then the current transaction being posted by Shareplex can be manually removed (read released) from the queue. This will cause out of sync. Another thing that can be done is to ignore posting on the table for which the DML is waiting to be posted.   Again, this will induce out of sync on that table.   Please contact support for further information regarding these two methods.

Starting with SharePlex 6.x, the single-threaded Post is deprecated. So all references about switching from single-threaded and multi-threaded Post and vice versa will not be applicable in current versions as Post only runs in multi-threaded mode.

This solution will only apply to Shareplex 5.x which can run on both STP and MTP and may encounter this issue when running in STP. Another thing to note is that the problem is at the database level and is not a STP problem.