RE: Plan Instability
- From: Herald ten Dam <Herald.ten.Dam@xxxxxxxxxxxxxxx>
- To: "kennethnaim@xxxxxxxxx" <kennethnaim@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Mar 2010 07:56:20 +0100
Hi,
I had the same with Oracle 11.1 on AIX. We must use FORCE because of a third
party. Some queries also went from seconds to almost an hour. Our solution was
to set OPTIMIZER_MODE from FIRST_ROWS to ALL_ROWS. This made the plan go from
Nested Loops to Hash Joins. This way we went back to seconds.
Herald ten Dam
Superconsult.nl
________________________________
Van: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] namens
Kenneth Naim [kennethnaim@xxxxxxxxx]
Verzonden: dinsdag 23 maart 2010 7:41
Aan: oracle-l@xxxxxxxxxxxxx
Onderwerp: 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:
