Error received in the Event Log and Multi Threaded Post (MTP) is slow:
"Notice: Operation is not blocked -- just taking longer than expected.
Make sure proper index is being used. [sp_opst_mt ]
Then run sp_ctrl>show sql and get the TableName ."
The operation on the following tables took longer than the 5 seconds expected to post.
1. Verify that the proper index is being used.
2. Check the init_trans on the target table and index. If it is set to 1, then it init_trans value on the target table and indexes may need to be increased if there are concurrent transactions on this table.
To increase init_trans to a higher number for the affected tables and indexes, check to see if there is ITL waits on the underlying tables/indexes.
You can run this query below
select object_name,statistic_name, value from
V$SEGMENT_STATISTICS where
object_name like '%XXXXX%'
and statistic_name='ITL waits';
If the value for ITL waits is greater than 0 then it indicates ITL waits, then it maybe necessary to increase of the init_trans.
You can run qview and read the messages to the first commit and see how many subques are touching the same table.
If there are 10 subques accessing the same table, then you want to consider increasing init_trans to 10 on both tables and indexes
