Oracle Database Discussions

Hi all.

We have a simple query, which joins four simple tables (of about 30.000 rows each) and contains some simple expressions. The performance of running this query varies extremely between two values - it either takes 4 seconds or 1.000 seconds... We run 11g on a Linux server.

If we run the same query 20 times in a row, 4 or 5 of the runs returns the data in 4 seconds and the other 15 or 16 runs returns the same data in 1.000 seconds. There is no input to the query, and the database is not modified by anyone else, and yet the performance so drastically alters back and forth between these extremes. Normally, the first call always take 4 seconds and the second call always 1.000 seconds, after that it's random as far as we can see.

We have enabled logging, and can see that Oracle switches back and forth between two radically different execution plans - one of the plans produces the result in 4 seconds (at a total "cost" of 82273) and the other plan returns the same result in 1.000 seconds (at a total "cost" of 30838477).

We are completely lost in trying to identify the cause of this behaviour. We have tried modifying the query, and some modifications into a semantically identical (but different) query gives a constant 4 seconds response time. However, we are afraid of running in to the same problem again if we do not know what caused it this time.

Can anyone hint what on earth could cause Oracle to fluctuate back and forth in this manner? Since the query, the schema and the data is identical between invocations, why does a second invocation of the same query run in 1.000 seconds when the first only took 4? And why do repeated calls take (apparently at random) 4 OR 1.000 seconds? What can cause the cost-based optimizer to fluctuate like this?

If the actual query, schema and/or execution plans are requested to answer that question, I will post them as needed. I didn't want to clutter the first post.

Grateful for all and any input.