Re: SQL may execute in wrong schema

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 10.2.0.2). in 10.2.0.1 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 10.2.0.3 (my guess is that it will not, it is too late), so another patch for 10.2.0.3 will be needed.

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

Nilo Segura
Oracle support - IT/DE
CERN - Geneva
Switzerland.

On 9/20/06, rjamya <rjamya_at_gmail.com> wrote:
>
> This morning I found this bug which " can occur much more easily " in
> 10.2.0.2 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_
> schema.
>
> "Workaround:
> 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
>
> Raj
> ----------------------------------------------
> Got RAC?
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Nilo Segura
Oracle Support - IT/DES
CERN - Geneva
Switzerland

--
http://www.freelists.org/webpage/oracle-l