Thanks Tom

Tom Kyte explained a problem on his blog that had been bugging me all summer long. There was this little gremlin in an application that was popping up every few weeks. I traced the problem down to a single query that seemed for whatever reason to loose its mind. I had figured out that the problems always happened after stats had been run on the tables. In fact, it had been discovered that doing stats again usually fixed the situation. There seemed to be something fishy with stats, but I knew things didn't work that way. However, I never made the next leap that Tom did. The problem as it turns out was caused by over binding. All the constants in the where clause were variables. The developers were using them to document their code. The table in question was very simple. Consisting of just two columns. An id and a status. The status field was highly skewed. 99% of the table had a Processed status and 1% had an Unprocessed status. There was even a histogram so Oracle would know that the field was skewed.

The query that was misbehaving looked like this

select *
from sometable
where status = c_unprocessed;

What I didn't realize is that there was another process that was slightly different.

select *
from sometable
where status = c_processed;

Now with the histogram, Oracle would peek at the variable value and know whether or not it should use an index. The plan would get stored to the SQL cache. It probably stayed there for weeks since the processes ran every couple of hours. The act of doing stats caused the plan to drop out of the SQL cache so that a new plan could be done. The problem is that only 1 plan is stored for both of those SQL statements. Whichever one runs first after the stats get done is the plan that makes it to the SQL cache. Bingo, that explained the crazy behavior.
The fix was very simple. Just remove those variables from the code.

select *
from sometable
where status = 'U';

select *
from
sometablewhere status = 'P';