2 Replies Latest reply on Jul 17, 2012 7:04 AM by 891920

    Synchronous streams and multi-record DML operation


      I have set up Bi-directional synchronous streams replication. Replication works great except for one detail - replicating transactions, which have over ~500 DML operations, are passed to other side extremely slowly.

      Let’s say, I have a table Employees which has 3500 records.

      Now if I try such update: update employees set name = name || '_test'; It takes about *2 hours* for changes to replicate to other side.

      If I split this UPDATE into 3500 separate transactions, it takes only *10 seconds* to propagate all these changes:

      update employees set name = name || '_test' where id = 1
      update employees set name = name || '_test' where id = 2
      update employees set name = name || '_test' where id = 3500

      Anyone has any ideas why larger transactions are so slow to propagate? I really need some help, so if you have any ideas, please post them.

      DB version: Oracle Database 11g Release
      Streams: Bi-directional, Synchronous

      Thank you,

      Edited by: Giedrius on Jun 19, 2012 7:59 AM
      Noticed that slowness is caused by large transactions, not by large multi-record update
        • 1. Re: Synchronous streams and multi-record DML operation
          Balazs Papp
          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.
          • 2. Re: Synchronous streams and multi-record DML operation

            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.