This content has been marked as final. Show 2 replies
Streams processes have some default limits, that you have to override sometimes.
For example, if a long transaction generates more LCRs then a given number, the apply process will store these messages in a queue table = write on disk, and read back from there when the transaction is finished (its called spilling), instead of immediately applying it from memory, which makes the whole process significantly slower.
The LCR limit for spilling to be triggered is defined by the parameter: txn_lcr_spill_threshold
You should monitor the state and throughput of your streams processes (capture, propagation, apply), and you should see there if this is your problem.
Thank you for your answer.
I have selected view DBA_APPLY_PARAMETERS and it shows that TXN_LCR_SPILL_THRESHOLD is 10000 (default value). Slowliness stars to show with 500 updated records in one transaction. So apply should not spill messages. Also while transaction was being propagated I tried to select view DBA_APPLY_SPILL_TXN. It shows no records, so I guess there was no spilling.
I tried to monitor this slow propagation using Oracle Enterprise Manager. It shows that in source database process "propagation sender" is using most CPU and at that time on target database there are lots of wait events "SQL*Net more data from client" and insert is being made: insert into "STR_ADMIN"."APPLY_AIVIKS_TABLE" ... where APPLY_AIVIKS_TABLE is APPLY queue table.
I will remind that this is synchronous, bidirectional streams, on Oracle Standard One database, 11gR2.