The multi-threaded Post (MTP) is the only mode of Post available starting with version 6.0 and up. In MTP, the Post has multiple threads instead of single thread, whereby each thread has one or more oracle connections which constructtheir own SQL on target database.
General information.
The MTP creates multiple threads on the target database for applying the changes (transactions, DDL, etc). This solution explains the criteria used for formulating such threads.
The number of threads are displayed by the sp_ctrl command show post thread in 6.x. You can use show post session in 7.x.
show post detailgives asnapshot of the MTP at a given point in time. This time shown below associated with Last operation posted is from the source database:
Last operation posted:
Redo log: 103054 Log offset: 50725680
DELETE from OWNER.TABLE_NAME at 03/13/08 00:06:18
In general, the number of threads created by MTPare roughly equal to the # of concurrent sessions on the source database.The messages are assigned with a subque# based on oracle session. This means transactions performed by one user on source are assigned with the same subque#.
The maximum number oftotalthreads by default is 64, controlled by SP_OPO_THREADS_MAX parameter. There is a main thread,a timer thread and a signal thread. That leaves 61 sql threads available for source sessions. The main thread will read the messages in post queue and dispatch the message toa new thread for each subque.In 6.x, one sqlthread may have multiplesubque messagesassigned to it if the total # of subques exceed 64. In 6.x, shareplex does not use session pooling thus it will try to open a connection to oracle for each new subque message. However in 7.x, there will only be one session assigned to each thread. When a session commits, theoracle connectioncan be reusedby another session/subque and can be attached to a different thread. There will be no limit of threads in 7.x and usually it starts with 4 threads.
The sp_ctrl command show sql displays the last sqlexecuted bythe various threads of the MTP. In single-threaded Post, there is only one SQL displayed since there is only one thread.
