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 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@xxxxxxxxx> 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?
--
//www.freelists.org/webpage/oracle-l
--
Nilo Segura
Oracle Support - IT/DES
CERN - Geneva
Switzerland
Other related posts: