Thanks for your replies. A few observations in response to the points raised. optimizer_env_hash_value is the same for both--does this not mean that optimizer environments are identical? Underlying objects are the same (it's the same query issued by the same user) Query in pipelined function has one variable, which is passed in as parameter. Both sessions pass the same value. Plans displayed by dbms_xplan do not show use of cardinality feedback. No outlines, profiles, or baselines exist. No PX in either case. No histograms. Paul Baumgartel UBS AG Group Finance Accounting Solutions 400 Atlantic Street Stamford, CT 06904 203.719.4368 paul.baumgartel@xxxxxxx www.ubs.com -----Original Message----- From: Kerry Osborne [mailto:kerry.osborne@xxxxxxxxxxx] Sent: Thursday, December 08, 2011 3:21 PM To: cshapi@xxxxxxxxx Cc: Baumgartel, Paul; oracle-l@xxxxxxxxxxxxx Subject: Re: What else can cause different optimizer plan? It's a long list. Here are a few more to come readily to mind. Different optimizer environment (i.e. any of a whole bunch of parameters is different) Different underlying objects (your MYDATA table is not the same as Joe's MYDATA table) Data changes (range of value significantly exceeds what stats says it is) Bind Variable Peeking kicks in Cardinality Feedback feature kicks in Adaptive Cursor Sharing kicks in Someone creates an Outline, Profile, or Baseline on the statement Size of memory structures (can determine whether a statement gets run with PX plan or not) The most common cause of plan instability is probably the combination of histograms and bind variables (i.e. bind variable peeking). Kerry Osborne Enkitec blog: kerryosborne.oracle-guy.com On Dec 8, 2011, at 2:04 PM, Gwen Shapira wrote: > different statistics on objects, different system statistics or > different bind variables. > > Gwen > > On Thu, Dec 8, 2011 at 10:39 AM, <paul.baumgartel@xxxxxxx> wrote: >> >> -- >> //www.freelists.org/webpage/oracle-l >> >> > -- > //www.freelists.org/webpage/oracle-l > > Visit our website at http://www.ubs.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mails are not encrypted and cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments. UBS reserves the right to retain all messages. Messages are protected and accessed only in legally justified cases. -- //www.freelists.org/webpage/oracle-l