Re: SQL may execute in wrong schema

  • From: "Nilo Segura" <nilosegura@xxxxxxxxx>
  • To: rjamya@xxxxxxxxx
  • Date: Wed, 20 Sep 2006 13:36:08 +0200

Yes, several of your applications were hit very badly by this bug. It causes
severe logical data corruption as the
results of your SQL manipulations end up on different schemas, and there was
no single indication of the problem (not ORA-600 etc ).

The bug was escalated to severity one in Oracle. Development  produced an
internal test patch that we tested one week ago if memory
serves well (I'm on holidays right now). The patch did fix the problem
according to our tests.

To clarify the problem: imagine that you have the same application that is
deployed in several database accounts in the same database server. In the
shared pool, you have  cursors with the same SQL stmt (including table names
etc). The only
difference is that the cursors are owned by different users.....   However,
under certain "special" conditions (heavy load etc), Oracle gets totally
confused and can not tell one cursor from another....  So modifications done
by user PETER ended up in the tables of user JOHN.

Nasty indeed....

There was an initial workaround ...

_kks_use_mutex_pin=false. (This setting disables new mutex functionality
switched on in in is was set to false.

But apparently it had some other unforeseen consequences, that is why the only "safe" workaround was to fully qualify all the object names with the owner name in the application code... .to avoid the "confusion"....

As I said, this affected several CERN grid and  physics experiments
applications, this problem was "very visible" and generated a lot of "noise"

Now we are waiting for the official patch to be published. The CPU July
patch I think does not touch the fixed part of the code (so I think they are
compatible, to be confirmed). I do not know if it will be added to the
incoming (my guess is that it will not, it is too late), so another
patch for will be needed.

And remember, this problem only happens in the conditions I described above,
we have quite a number of installations and they run very well.

Nilo Segura
Oracle support - IT/DE
CERN - Geneva

On 9/20/06, rjamya <rjamya@xxxxxxxxx> wrote:

This morning I found this bug which " can occur much more easily " in onwards and the workaround is so funny.

Essentially this bug happens when you have more than one schema with
identical table/view names and two users are executing same SQL but in
different schema. Sometimes the SQL will execute in the _wrong_

  There is no simple workaround which can avoid this fully.
  The issue can be avoided by prefixing object names with the schema name.
  eg: In the above example change the SQL to use
    "select mycol from A.mytable" for user A and
    "select mycol from B.mytable" for user B.

  If SQL cannot be changed then it can help to reduce shared pool
  load (as the problem occurs when cursors are reloaded having
  been aged out / invalidated). Using DBMS_SHARED_POOL.KEEP for
  affected cursors can also help.:

BUG# 5458753

Got RAC?

Nilo Segura
Oracle Support - IT/DES
CERN - Geneva

Other related posts: