RE: What else can cause different optimizer plan?
- From: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
- To: "paul.baumgartel@xxxxxxx" <paul.baumgartel@xxxxxxx>, "kerry.osborne@xxxxxxxxxxx" <kerry.osborne@xxxxxxxxxxx>, "cshapi@xxxxxxxxx" <cshapi@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Dec 2011 16:58:54 -0500
You can run event 10053 trace for each situation
(http://jonathanlewis.wordpress.com/2010/04/30/10053-viewer/) - it shows the
calculations the CBO performs to get the execution plan. Look at the two trace
files and see where they start to diverge. That may give you the clue about why
the CBO generates different execution plan.
Iordan Iotzov
http://www.indeed.com/
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of paul.baumgartel@xxxxxxx
Sent: Thursday, December 08, 2011 4:13 PM
To: kerry.osborne@xxxxxxxxxxx; cshapi@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: What else can cause different optimizer plan?
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
This message and its attachments may contain legally privileged or confidential
information. It is intended solely for the named addressee. If you are not the
addressee indicated in this message (or responsible for delivery of the message
to the addressee), you may not copy or deliver this message or its attachments
to anyone. Rather, you should permanently delete this message and its
attachments and kindly notify the sender by reply e-mail. Any content of this
message and its attachments that does not relate to the official business of
News America Incorporated or its subsidiaries must be taken not to have been
sent or endorsed by any of them. No warranty is made that the e-mail or
attachment(s) are free from computer virus or other defect.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
