As many of us are returning to work today, I wanted to share an interesting situation from last week to re-energize you for the short week! I've mentioned that we've hit a nasty database bug which affected our 11.2.0.2 environment, and still affects the database even after upgrading to 11.2.0.3, so we've created an alert for it and know how to handle those situations. The other day, we had another situation where it appeared that the inactive transaction branch bug was now affecting internal database transactions which did not go out over a database link at all. Very interesting, and very scary prospect!
I did some poking around but couldn't really come up with anything proving that this session wasn't going out over a database link, so I talked with one of our top developers about it since he usually has some really great assistance and guidance on issues like these, and he said he'd take a look. A little while later, he came back to me and just about knocked my socks off; the session was not internal, and it was going over a database link. We had saw the following script in gv$sqlarea:
select sequenceX.nextval from dual
which doesn't appear to be using any database links, right? Well the developer had found that the object had a synonym created, with the entry in DBA_SYNONYMS pointed across the database link so when we looked into the gv$sqlarea on that database with the same sql_id we found the same SQL being run! This proves out that what appeared to be a local session was in fact NOT a local session after all, and that is why it was possible for the session to be stuck in the "inactive transaction branch" wait event class.
No comments:
Post a Comment