Re: Optimizer Instability on 10.2.0.2

From: <mccmx_at_hotmail.com>
Date: 20 Apr 2007 06:26:57 -0700
Message-ID: <1177075617.332730.257450@l77g2000hsb.googlegroups.com>

>
> This could be sufficient to switch the optimizer
> from a nested loop join to a hash join. That being
> the case, you just need to have a sufficiently busy
> system that the plan get aged out from time to time,
> and you get this type of switch on the re-optimisation.
>

I agree with all of you points and I was investigating along those lines yesterday afternoon. However what I couldn't work out was that the slow process found the query in the cache (zero misses) at 13:00 and the fast process didn't find the query in the cache (1 miss) at 13:30 but I could see the SQL cached in the shared pool at 15:00 with a first_load_time of 06:00. So I cant explain how one run would find it and the other wouldn't. Any thoughts..?

Thanks for your time...

Matt Received on Fri Apr 20 2007 - 08:26:57 CDT