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

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

Hint is a bad idea, hard to maintain. Use the OPT settings set at session level to get better plan. Alter your session right before running the query. The values are for you to determine based on the join types/plan you want to see.

alter session set.....

optimizer_index_caching
optimizer_index_cost_adj
db_file_multiblock_read_count Received on Thu Dec 28 2006 - 12:22:51 CST