RE: Plan Instability - Solution
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: "'Herald ten Dam'" <Herald.ten.Dam@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Mar 2010 23:37:23 -0400
We were at all_rows already, and they were considering switching to
first_rows which I stopped since the oltp screens might speed up but all the
batch type processes would suffer plus I try not to sledge hammers when
scalpels are needed. I also turned off bind peeking and was still having
issues with this particular query. I analyzed the particular nested loop
step and saw that the fist column in an a 4 column composite index had 400k
unique values out of 2 million rows which I surmised was causing it to
choose NL occasionally, so I recreated the index with a column that only had
41 unique values out of 4 million first which solved this issue.
Thank you,
Ken
From: Herald ten Dam [mailto:Herald.ten.Dam@xxxxxxxxxxxxxxx]
Sent: Tuesday, March 23, 2010 2:56 AM
To: kennethnaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Plan Instability
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:
- » RE: Plan Instability - Solution - Kenneth Naim
