Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Different explain plans for a query on SQL Plus and PL/SQL

Re: Different explain plans for a query on SQL Plus and PL/SQL

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 2 Jan 2007 12:52:16 -0800
Message-ID: <1167771136.040721.92130@a3g2000cwd.googlegroups.com>

barraboombarrabin wrote:
> Thanks for your suggestions. I am going through the tuning manual to
> get a better understanding of the Optimizer and tuning.
> For the time being I plan to use a hint to get a more efficient
> explaing plan
>
> On Nov 3, 7:06 pm, "joel garry" <joel-ga..._at_home.com> wrote:
> > barraboombarrabin wrote:
> > > Hi,
> > > I have a couple of queries where there explain plan that I get from
> > > TOAD or SQL Plus is different from the explain plan that is being used
> > > when the same select statement is executed from PL/SQL package (which I
> > > determined by looking at V$SQL_PLAN table). The table is truncated and
> > > loaded with data every day and during the data load process the indexes
> > > are dropped and recreated after the data load. After the index creation
> > > is done, the table in question is also analyzed using dbms_stats.
> > > I would like to use a hint only as a last resort.
> > > I would appreciate it if someone can help me with some steps that can
> > > be taken to ensure that the explain plan within the PL/SQL is the
> > > desired one without using the hint.Explaining a plan is just what might happen ("With bind variables in
> > general, the EXPLAIN PLAN output might not represent the real execution
> > plan." - Performance Tuning Manual). V$SQL_PLAN is what actually used
> > by the optimizer. See metalink Note:186548.1.
> >
> > See the Performance Tuning Manual about Plan Stability (which is hints,
> > of course).
> >
> > There may be an argument for just letting the CBO do its thing if you
> > are getting fresh data and stats every day. Do you have any evidence
> > that the plan might change with an expected different data
> > distribution?
> >
> > jg
> > --
> > @home.com is bogus.http://www.networkworld.com/graphics/2006/subnetmask.jpg

Hi,

One option, if the plan differs between two different clients, is to look at the gv$ses_optimizer_env view. It will show you the optimizer settings for each session, as they may differ for any number of reasons (triggers, manually setting, etc.).

Regards,

Steve Received on Tue Jan 02 2007 - 14:52:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US