log里面报错

They are in different threads. Will wait for blocker to release lock.  (posting from cdsync1, queue CDDBSync01, to bjora) [module opo]

Notice   2011-04-06 10:12:38.270818 20391 3221846944 Poster: session=2 is blocked by session=3. They are in different threads. Will wait for blocker to release lock.  (posting from cdsync1, queue CDDBSync01, to bjora) [module opo]

查看qstatus

sp_ctrl (bjsync:2300)> qstatus

Queues Statistics for bjsync

Name:  CDDBSync01 (o.cdsync1-o.bjora) (MTPost queue)

Number of messages:    6872035 (Age    175442 min; Size       3203 mb)

Backlog (messages):    6872030 (Age     18131 min)

一直都在增加

重启一下post

情况依旧

查看了一个support

Title

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

Problem Description

Your MTP (Multi Thread Post) goes idle and there's no apparent error beside warnings about blocked sessions.

For example:

[32771] 12/06/06 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.-o.queue )/30439]

Cause

In this example, one session is blocked by another session. Normally, if first session get enough time to complete it's transaction, the blocked sessions will resolve itself. However, if the initrans is too small, this will also cause blocked sessions.

Resolution

For this example, there's a few thing you need to check and change if need be:

1. Check if the problematic table and it's indices has a large enough initrans. It's 1 usually by default. We recommend 10-20 for your problematic tables and indices.

2. Check your SP_OPO_THREADS_MAX to make sure it's => 64. The value of SP_OPO_THREADS_MAX should be set to the default value of 64. If it is decreased, then chances of deadlocks will increase.

3. In Shareplex version 5.3.2 and newer, we introduce 2 Shareplex parameter to reduce the number of block session warning notice, SP_OPO_MAX_OEXN_TIME & SP_OPO_MAX_QUEUE_SIZE. If you get the blocked sessions warning too frequently, we recommending setting SP_OPO_MAX_OEXN_TIME to 180 and SP_OPO_MAX_QUEUE_SIZE to 500, this will give the MTP more time and resources to avoid blocked session errors.

SP_OPO_MAX_OEXN_TIME: This parameter controls the maximum duration, in seconds, that a 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 value 60

SP_OPO_MAX_QUEUE_SIZE: This parameter controls the maximum size (in messages) of the waiting queue for the SQL threads established for multi-threaded Post. The size of the waiting queue is 0 when the main thread has not dispatched any messages to the SQL thread. Default value 100

Note: The parameters specified are tuning parameters, so values given are suggested values and may need to be adjusted according

在查看oracle 里面的阻塞进程发现一个

并且长期阻塞，找到该进程 kill以后问题得到解决