2 Replies Latest reply: Jul 1, 2014 2:46 AM by User13260331-Oracle RSS

    Paused - waiting for DDL to complete

    chuckers

      In our downstream server, after a batch job of some sort is executed in the upstream server, we're constantly thrown into a state of "PAUSED - WAITING FOR DDL TO COMPLETE", and it takes a really long time to complete. When I try & look up the SQL statement that Streams is sitting on, it's never a DDL statement:

       

      select  a.apply_name, a.server_id, q.sql_text sqltext

      from v$streams_apply_server a, v$sqltext q, v$session s

      where a.sid = s.sid and s.sql_hash_value = q.hash_value

      and s.sql_address = q.address and s.sql_id = q.sql_id

      order by a.apply_name, a.server_id, q.piece;

       

      So I'm a little puzzled on how to improve performance, or if it's even possible. If it matters, we have parallelism set to 1, because we were getting so many deadlock errors otherwise. This slowness from "..WAITING FOR DDL..." was present before that change, however.

       

      Thanks for your help,

      --=Chuck

        • 1. Re: Paused - waiting for DDL to complete
          Renu-Oracle

          Hi,

           

          Similar issue is reported in Bug which was suspended due to some reasons.

          You can try to ignore the problematic DDL transaction which is causing the issue.

           

          Thanks,
          Renu

          • 2. Re: Paused - waiting for DDL to complete
            User13260331-Oracle

            Hi ,

             

            Please confirm if your replication setup is DML only or both DDL and DML.

             

            Apply process will move this state "PAUSED - WAITING FOR DDL TO COMPLETE"  only when DDL operations are performed at Source database.

            It will catch up as soon as the DDL transaction is completed.

            Either you can ignore this transaction and manually perform DDL on target .

            Or

            Tag the DDL transaction so that it is not captured by Capture process and manually perform DDL on target .

            Or

            Wait for the Apply process to complete its task.

             

            Thanks,

            Lalitha