2

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) 

SELECT fields 
FROM local_table1, local_table2, ... (more tables)
     remote_table1@dblink remote1, remote_table2@dblink remote2 
WHERE
      ...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?

Jeff
  • 215
  • 1
  • 3
  • 9

1 Answers1

1

We learned that in rare circumstances a distributed transaction fails and leaves some kind of "half lock" on one side of the distributed transaction. Because the other side was gone it never resolved. At least one of the common tools that looks up locks didn't see these locks. However, we were able to find them (I think we queried v$lock, I don't remember, it's been over a year since it last happened). To fix it we determined what process was running that session and our DBA killed the OS process with a kill -9. It was a rare problem and I haven't seen it in about 18 months, so it's possible that it is only something that happens on earlier versions of Oracle . (However, it usually happened only once or twice a year at most, so it could be we've just been lucky.)

Jeff
  • 215
  • 1
  • 3
  • 9