Thanks for the suggestion.
I agree that a Hint would be hard to maintain. However since there are
multiple selects in the package, a change in optimizer_index_cost_adj,
db_file_multiblock_read_count will cause a change in the explain plan
of other selects within the same package.
EscVector wrote:
> 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 Tue Jan 02 2007 - 11:02:34 CST
