Session # is blocked by session #, wait for blocker to release lock. (4369811)

Session # is blocked by session #, wait for blocker to release lock.

Post is idle and there is no apparent error beside warnings about blocked sessions.
   Poster: session is blocked by session, They are in different threads. Will wait for blocker to release lock.

You notice a high number of splex sessions in the Target database and Post is backlogged.

Example:
[32771] 12/06/21 20:42 Notice: session=5 is blocked by session=2. They are in different threads. Will wait for blocker to release lock. [sp_opst_mt (for o.<sid>-o.<sid> queue <queue_name>)/30439]
 

In this example, one session is blocked by another session.
Normally, if the first session gets enough time to complete it's transaction, the blocked sessions will resolve itself.
However, if the INITRANS is too small on the Target table or index, it may also cause blocked sessions.
 

Potential resolutions:

1. Check if the problematic table and it's indexes has a large enough INITRANS value.
    It is 1 by default. We recommend  setting it to at least 10 for problematic tables and and 20 for indexes.

       SQL> select table_name, ini_trans from user_tables;
       SQL> alter table TABLE_NAME initrans {value};
   
       SQL> select index_name, ini_trans, max_trans from user_indexes;
       SQL> alter index INDEX_NAME rebuild initrans {value};
   
    Note: Stop Post when altering the INITRANS value.

2. Check the parameter SP_OPO_THREADS_MAX to make sure it is 128 or greater.
    At a minimum, the value of SP_OPO_THREADS_MAX should be set to the default value of 128.
    If it is decreased, then the chances of deadlocks will increase.

    SP_OPO_THREADS_MAX
    This parameter is used primarily in testing to view the behavior of a SQL thread when it is handling several subqueues.
    The minimum number of threads is 4, which enables the Post process to create the main thread, the timekeeper thread, the signal waiter thread, and one SQL thread.
       Default: 128 threads
       Range of valid values: 4 threads (minimum) to 1024 threads (maximum)
       Takes effect: when Post is restarted

3. Check the parameter SP_OPO_MAX_OEXN_TIME to make sure it is 900 or greater.
    At a minimum, the value of SP_OPO_MAX_OEXN_TIME should be set to the default value of 900 seconds.
    If it is decreased, then it will reduce Post resources and may cause blocked sessions.

    SP_OPO_MAX_OEXN_TIME:
    This parameter controls the maximum duration, in seconds, that an SQL thread may spend in a call to OCIStmtExecute
    before the timekeeper thread defaults to a deadlocked position and forces the Multi-threaded Post process to exit.
       Default: 900 [seconds]
       Range of valid values: any positive integer
       Takes effect: when Post is restarted