Christian Bilien’s Oracle performance and tuning blog

March 14, 2007

SQL can execute in wrong schema (10.2)

Filed under: Off topic,Oracle — christianbilien @ 4:54 pm

Symptoms are: wrong results from SELECT AND data corruption for DML

Although this post is off-topic, I think it is worth sharing one of the nastiest Oracle bug I ever encountered (I am not one of the Oracle pioneer, but I have nonetheless seen a number of them in my life). This is documented in Note:392673.1, which says it is only appearing in 10.1, (for which the manifestations are very rare), but with a much higher chance of occurring in 10.2.

This problem can occur only if all of the following are true:

  • The SQL statement refers to some object without using a fully qualified object name
  • The unqualified object name resolves to a different underlying object for different users.
  • Sessions in different schemas execute IDENTICAL SQL sentences

The note says that the problem is related to reload of aged out cursors, and indeed I experienced it because statistics were calculated before the above conditions were met.

5 Comments »

  1. Christian,
    this bug has been around since auto-management of the SGA came out, or more precisely, since 9i. It’s apparently only fixed in 11 or 10.2.0.4.

    I find it astonishing that something like this has been without a fix for so long and folks don’t even complain about it. In fact, most are not even aware it exists!

    To me, it shows that the vast majority of databases out there are running a single schema or very close to it, or are in much earlier versions of the software. Either of these is not good news…

    Comment by Noons — November 29, 2007 @ 4:32 am

  2. Christian,
    this bug has been around since auto-management of the SGA came out, or more precisely, since 9i. It’s apparently only fixed in 11 or 10.2.0.4.

    I find it astonishing that something like this has been without a fix for so long and folks don’t even complain about it. In fact, most are not even aware it exists!

    To me, it shows that the vast majority of databases out there are running a single schema or very close to it, or are in much earlier versions of the software. Either of these is not good news…

    Comment by Noons — November 29, 2007 @ 4:33 am

  3. Noons,

    I must say I could not believe it either when I found out about this problem. Having said that, this application has been happily running before and after. The bug only showed up when the statistic calculations went thrashing the library cache.

    Thanks for your comment.

    Christian

    Comment by christianbilien — November 29, 2007 @ 3:37 pm

  4. Just dropping in to thank you for mentioning this. One of my customers have been suffering from this problem for a couple of weeks and until today all focus has been on hunting for bugs in the application. I notice the patch is listed in 10.2.0.3 and that it is not marked as a “particularily notable bug” even though it can result in very serious side-effects. This particular application is used by different organizations with their own schemas in the same database and we have experienced both SELECTs and INSERTs being performed in the wrong schema – especially in queries coming from Tuxedo. We just hope that all bad updates have been caught by constraints, but unfortunately we can’t be sure…

    Comment by Johan — December 6, 2007 @ 10:32 am

  5. Thanks Johan. Did you also had library cache invalidations ?

    Comment by christianbilien — December 6, 2007 @ 11:59 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: