RE: Plan Instability

As you're on 10gR2, have you thought about using DBMS_ADVANCED_REWRITE?
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_advr
wr.htm
 
Have you pulled the execution plan via dbms_xplan.display_cursor? The
bad estimates should be more obvious with that output.
 
 
 
________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kenneth Naim
Sent: 23 March 2010 06:42
To: oracle-l@xxxxxxxxxxxxx
Subject: 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


**********************************************************************
 Please consider the environment before printing this email or its attachments.
The contents of this email are for the named addressees only.  It contains 
information which may be confidential and privileged.  If you are not the 
intended recipient, please notify the sender immediately, destroy this email 
and any attachments and do not otherwise disclose or use them. Email 
transmission is not a secure method of communication and Man Investments cannot 
accept responsibility for the completeness or accuracy of this email or any 
attachments. Whilst Man Investments makes every effort to keep its network free 
from viruses, it does not accept responsibility for any computer virus which 
might be transferred by way of this email or any attachments. This email does 
not constitute a request, offer, recommendation or solicitation of any kind to 
buy, subscribe, sell or redeem any investment instruments or to perform other 
such transactions of any kind. Man Investments reserves the right to monitor, 
record and retain all electronic communications through its network to ensure 
the integrity of its systems, for record keeping and regulatory purposes. 
Visit us at: www.maninvestments.com 
TG0908
**********************************************************************

Other related posts: