I have a query that's been in use for years and today it started hanging endlessly. I took the query apart and found the piece causing it to hang is a simple join on a primary key that should run like lightning. If I remove problem table the query runs in less than a second.
Synthetically it looks like this: (The problem table is remote2)
FROM local_table1, local_table2, ... (more tables)
remote_table1@dblink remote1, remote_table2@dblink remote2
...clauses to tie the local tables together with each other and remote_table1...
remote2.primary_key = remote1.primary_key_of_remote2
AND remote2.afield <> 'avoid this value'
It's acting like it stuck on some kind of DB lock, but neither myself nor the DBA can find it. Is there some kind of special queuing mechanism used by remote database links that is locking this up?