Plan Instability

I have been called in to help on a 10.2.0.4 instance on AIX with cursor
sharing set to force due to rare use of bind variables. I have one query
which goes from running in 5 seconds or less to taking over an hour and
erroring when it runs out of 15gb of temp space. The issue is caused by one
step in the plan where a hash join is replaced with a nested loop. I need to
stabilize this plan but cannot use a stored outline because the query has
both literal variables and bind variables so the literals come through even
with cursor sharing set to force. I cannot change the code as it is complied
in a delivered binary (am working with the vendor but I need a solution
asap). I have stopped gathering stats (all involved tables/indexes have
accurate stats), removed histograms, set dynamic sampling back to the
default level of 2 so neither plan is affected by it. I wanted to "fix" the
stats that cause the plan to change but after running a 10053 trace on a 9
table join query I couldn't figure out which stat was the culprit. The only
other item I can think of that would cause the plan to change is bind
variable peeking, so my question is what else can cause a plan to change?

 

Thanks,

Ken

Other related posts: