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?

Improve this question
4
  • Are you able to retrieve data from remote2 on its own? (check with and without your <> criteria).
    – GregHNZ
    Commented Jul 3, 2013 at 6:57
  • Thanks for the ideas. I tried this and it works (much to my suprise): SELECT * FROM remote_table1@dblink iv, remote_table2@dblink il WHERE remote2.primary_key = remote1.primary_key_of_remote2 AND remote2.afield <> 'avoid this value'
    – Jeff
    Commented Jul 3, 2013 at 13:45
  • And tried the main query without the <> criteria and it doesn't work. That doesn't make any sense now.
    – Jeff
    Commented Jul 3, 2013 at 13:46
  • So... you can get data out of the remote database, but when you try to join two remote tables with local data it chokes? That's weird. Maybe try changing your query to use a join syntax? (i.e FROM R1 JOIN R2 On R1.pk = r2.pk where r2.afield <> 'avoid this') That'll tell the DB engine to separate join logic from the WHERE logic?? (feels hacky, might work)
    – GregHNZ
    Commented Jul 4, 2013 at 7:22

1 Answer 1

Reset to default
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.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.