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