Possible cache corruption - please help

We have a very strange situation that occurs reasonably frequently (avg 1-2 times per day), but we haven't been able to reproduce under 'controlled' conditions. Basically the symptom is programs that have been working fine, all of a sudden start returning incorrect results. The 'solution' is to flush the shared pool (ALTER SYSTEM FLUSH SHARED_POOL). Once we do that, our programs start behaving themselves again. The Oracle instance that gives the problem contains 3 schemas that are identical in structure - but obviously differ in content. When programs start returning 'incorrect' results, it appears the results are coming from the wrong schema. For example, a statement is issued in schema A, but it returns data from Schema B.

We make extensive use of external functions (ie written in C), and I suspect these may be the culprits as it seems whenever a statement starts returning incorrect results, it contains one of those external functions.

I have done some limited analysis of the shared_pool size, and believe it to be large enough, but wouldn't want to stake my life on it. However, I would have thought that a shared pool that was too small would have performance implications - not result in spurious results.

Any help or insight into our problem would be much appreciated.

Steve Baldwin

P.S.
Could you CC any reply to my e-mail address - steven.baldwin_at_hancorp.com.au Thanks. Received on Tue Sep 14 1999 - 18:39:04 CDT