An interesting side-effect of blogging (rather than writing up a more formal document for my web site) is that it feels perfectly reasonable to throw out some odd observations without necessarily having to supply an explanation or solution.
So here’s the bare bones of an oddity relating to distributed queries that someone emailed me a couple of days ago:
INST1 - 220.127.116.11: Insert data into table X and commit INST2 - 10.2.0.1: select from X@DB1,dual;
INST2 does not necessarily see the data from INST1, although if you wait at least three seconds, then it will see the data.
Note particularly that the query from INST2 joins two tables; if you don’t do a join then INST2 will see the data.
If you repeat the query INST2 will see the data on the second execution
If you issue a (redundant) commit or rollback before querying then INST2 will see the data.
If you wait about 60ms before querying INST2 may see the data.
If you wait for 3 seconds then INST2 will see the data.
The three seconds example makes me think it is related to re-synching SCNs between distributed databases – but which process is responsible for doing this ? But the fact that the failure only happens with a join seems very odd.
I passed this oddity to the Oak Table to see if they had any comments – apart from reproducing the general case, a couple of them came back with a couple of associated phenomena.
One Oakie came up with an example where the two databases were running under different operating systems – one HP-UX, the other AIX – the problem simply did not appear.
Another Oakie came up with the idea of truncating the table on the first instance – and it still took the second instance some time before it noticed that the data had disappeared.
Maybe there’s something on Metalink, or in the latest patch releases that explains this. But there isn’t enough time to research every little oddity. But if anyone happens to have an explanation (or a bug number) for this anomaly, feel free to post it in and tell the rest of us.